CODEWITHSUNDEEP
mysql
lajos
lajos

Reputation: 25705

How to find out if a certain value exists as a primary key in 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();

Upvotes: 4

Views: 8137

Answers (6)

o17t H1H' S'k
o17t H1H' S'k

Reputation: 2725

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

Upvotes: 0

Dan
Dan

Reputation: 63390

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.

Upvotes: 1

Brian Matthews
Brian Matthews

Reputation: 8596

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!'

Upvotes: -1

Brian
Brian

Reputation: 13571

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.

Upvotes: 0

Mike Ivanov
Mike Ivanov

Reputation: 153

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

Upvotes: 1

Jarod Elliott
Jarod Elliott

Reputation: 15670

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.

Upvotes: 3

Related Questions