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

Question

What is the best way to find out if a table has a primary key with a specific value?

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 any of your suggestions in the question is appropriate.

Depending on how you use this, you can save time by running INSERT IGNORE, which allows you to insert a new row if the primary key does not exist. If it exists, the error is ignored, so you can continue as usual.

Other similar options, depending on your use, include using the REPLACE or INSERT ON DUPLICATE KEY UPDATE insert types. This allows you to update an existing record if the primary key already exists, otherwise it simply inserts a new record.

Answer 2

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

Answer 3

I would do:

 SELECT 1 FROM table WHERE id key = 'value' 

Anything else may slightly affect query optimization, so I will stick with this.

Edit: Although I only realized that I did not think I had ever done this in MySQL, although I did not understand why this would not work.

Answer 4

Example

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

If you are trying to solve a problem between an insert or update, use the MERGE statement in Oracle. I believe MS-SQL is something like a UPSERT statement.

Answer 5

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

Answer 6

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

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