Mysql: most efficient way to get the ID from an attempted insertion?

Question

I'm inserting some_data (a unique key column), and then using the resulting user_id (the primary key auto-increment column) in a separate statement (not shown)

INSERT IGNORE INTO users (some_data) VALUES ('test');
SELECT LAST_INSERT_ID(); <--- I do stuff with this.

But, of course, if some_data already exists (happens very frequently), LAST_INSERT_ID() returns 0. What is the best way to get the user_id based on the unique key some_data, in this case? Of course I can do a separate WHERE query, but not sure that is the most efficient.

byrnedo · Accepted Answer · 2014-10-17T19:16:43.623

1

From http://dev.mysql.com/doc/refman/5.0/en/insert-on-duplicate.html

INSERT INTO users ( id, some_col ) VALUES (n,some_val)
   ON DUPLICATE KEY UPDATE id=LAST_INSERT_ID(id), some_col=some_val;

Not an ignore but might do the job?

Edit:

To be clear, this will update some_col with some_val and then set the LAST_INSERT_ID to return the id of the duplicate row.

It could just as well be this if you didn't want to update any data on the duplicate but just set the LAST_INSERT_ID() call to give you what you want:

INSERT INTO users ( user_name ) VALUES ( 'bobloblaw' )
   ON DUPLICATE KEY UPDATE id=LAST_INSERT_ID( id );

Edit 2:

Use a proc to do the work and get back the id

DELIMITER $$
CREATE PROCEDURE insert_test( val1 varchar(10), val2 varchar(10) )
BEGIN

    INSERT INTO test.test_table ( col1, col2 ) SELECT val1, val2 FROM ( select 1 ) as a
    WHERE  NOT EXISTS (
            select 1 from test.test_table t where t.col1 = val1
    );

    SELECT id FROM test.test_table where col1 = val1;

END $$
DELIMITER ;

edited Oct 17 '14 at 19:16

answered Oct 12 '14 at 20:09

byrnedo

1,415
10
12

Er.... (1) I don't know the id. (2) wouldn't that set id to 0 if the key is duplicate? – Agamemnus Oct 12 '14 at 20:46
1

Check this post out:http://stackoverflow.com/questions/14383503/on-duplicate-key-update-same-as-insert Seems to be your problem too. – byrnedo Oct 17 '14 at 10:46
byrnedo, thanks for the link, but the critical part is getting the ID after the insert/update of an already existing item. No solution was provided. – Agamemnus Oct 17 '14 at 16:40
The original answer I gave will set the LAST_INSERT_ID to the id of the original row. What is happening is based on the difference between a call to `LAST_INSERT_ID()` and `LAST_INSERT_ID( )` is that the latter *sets* the last id value. See this doodle: http://sqlfiddle.com/#!2/75af5/1 – byrnedo Oct 17 '14 at 17:32
Let me try to clarify... I need to get the ID of the row if it was inserted, *AND* the ID of the row if it was skipped because the row already existed. – Agamemnus Oct 17 '14 at 17:43
1

Look at the doodle/fiddle please. Doing a `SELECT LAST_INSERT_ID()` after the above syntax will give you the id in both cases. – byrnedo Oct 17 '14 at 17:44
Oh, all right.. great. A few little problems though: (1) you're re-inserting the ID... (2) since you're re-inserting the ID, you're auto-incrementing on ignored inserts. Can you think of a way of getting the ID without re-inserting it? If not, is there a way of resetting the auto-increment value back down by 1 within the same INSERT statement somehow? – Agamemnus Oct 17 '14 at 17:54
1

See Edit 2 in answer above. 1) I know, this is due to the syntax. 2) The auto_increment is bumped since it attempted the insert, then caught the duplicate internally. The same thing happens when using `DECLARE ERROR HANDLER...` like he uses here: http://www.mysqltutorial.org/mysql-error-handling-in-stored-procedures/ – byrnedo Oct 17 '14 at 19:27
1

Ah, thanks. Didn't realize that. I'll try the procedure. Thanks a lot. – Agamemnus Oct 17 '14 at 19:34

Mysql: most efficient way to get the ID from an attempted insertion?

1 Answers1