How to find out if a certain value exists as a primary key in mySql?

Question

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();

Answer 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.

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.

Answer 2

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

Answer 3

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.

Answer 4

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

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

Answer 5

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.