如何确定某个值是否作为mySql中的主键存在？

What is the best way to find out if a primary key with a certain value already exists in a table?

找出表中是否已存在具有特定值的主键的最佳方法是什么?

I can think of:

我能想到:

SELECT key FROM table WHERE key = 'value';

and count the results, or:

并计算结果,或:

SELECT SQL_CALC_FOUND_ROWS key FROM table WHERE key = 'value' LIMIT 1;
SELECT FOUND_ROWS();

6 个解决方案

#1

I think either of your suggestions in the question are suitable.

我认为你在问题中的任何一个建议都是合适的。

Depending on how you are using this though, you can potentially save time by doing an INSERT IGNORE, which allows you to insert a new row if the primary key doesn't exist. If it does exist, the error is ignored so you can continue as normal.

根据您使用它的方式,您可以通过执行INSERT IGNORE来节省时间,如果主键不存在,则允许您插入新行。如果确实存在,则忽略该错误,以便您可以正常继续。

Other similar options depending on your usage include using the REPLACE or the INSERT ON DUPLICATE KEY UPDATE types of inserts. This allows you to update the existing entry if the primary key already exists, otherwise it just inserts your new entry.

其他类似选项取决于您的用法,包括使用REPLACE或INSERT ON DUPLICATE KEY UPDATE类型的插入。如果主键已存在,则允许您更新现有条目,否则只需插入新条目。

#2

Do the first and count the results (always 0 or 1). Easy and fast.

先完成并计算结果(总是0或1)。简单快捷。

#3

I'd do:

SELECT 1 FROM table WHERE id key = 'value'

Anything else is likely to interfere with query optimisation a little, so I'd stick with that.

其他任何东西都可能会稍微干扰查询优化,所以我坚持这一点。

Edit: Although I just realised I don't think I've ever done that in MySQL, although I can't see why it wouldn't work.

编辑:虽然我刚刚意识到我不认为我曾经在MySQL中做过这些,虽然我不明白为什么它不起作用。

#4

Example

Select count(key) into :result from table where key = :theValue

If your trying to decide between an insert or update, use a MERGE statement in Oracle. I believe MS-SQL is something like an UPSERT statement.

如果您尝试在插入或更新之间做出决定,请在Oracle中使用MERGE语句。我相信MS-SQL就像UPSERT语句。

#5

SELECT 1 FROM mytable WHERE mykey = 'value' LIMIT 1

SELECT 1 FROM mytable WHERE mykey ='value'LIMIT 1

#6

-1

I think it would be more intuitive and simplier to use IF EXISTS.

我认为使用IF EXISTS会更直观,更简单。


IF EXISTS (SELECT key FROM table WHERE key = 'value')
    PRINT 'Found it!'
ELSE
    PRINT 'Cannot find it!'

#1