MySQL - if exists, get the primary key. Else, add a record

Question

My table has two columns: id (Auto Increment, Primary) and Number (Unique). Now I want the following:

• if the number already exists, return id;
• else, add a record to the table and return its identifier.

What is the most effective method to do this job?

Note:

• There is a high probability that the number is new;
• The table will contain hundreds of thousands of records.

Thanks!

Answer 1

INSERT IGNORE INTO table (number) VALUES (42); SELECT id FROM table WHERE number = 42; 

This is probably the most efficient in MySQL. You can use a stored procedure to break them down, which may or may not be a bit more efficient.

EDIT:

If you think that new numbers will rarely appear, it will be even faster:

 SELECT id FROM table WHERE number = 42; if (!id) { INSERT INTO table WHERE number = 42; id = SELECT @LAST_INSERT_ID; } 

There is a possible race condition if simultaneous simultaneous streams simultaneously insert the same number. In this case, later insertion will not be performed. You can restore this by reselecting this error condition.

Answer 2

Here is one such stored function that does what you describe:

 CREATE FUNCTION `spNextNumber`(pNumber int) RETURNS int(11) BEGIN DECLARE returnValue int; SET returnValue := (SELECT Number FROM Tbl WHERE Number = pNumber LIMIT 1); IF returnValue IS NULL THEN INSERT IGNORE INTO Tbl (Number) VALUES (pNumber); SET returnValue := pNumber; -- LAST_INSERT_ID() can give you the real, surrogate key END IF; RETURN returnValue; END 

Answer 3

I know this is old, but this is a common problem. So, for the sake of anyone looking for a solution, there are four different ways to accomplish this task using performance tests. http://mikefenwick.com/blog/insert-into-database-or-return-id-of-duplicate-row-in-mysql/ .