CODEWITHSUNDEEP
mysqlsql
Agamemnus
Agamemnus

Reputation: 1426

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

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.

Upvotes: 0

Views: 84

Answers (1)

byrnedo
byrnedo

Reputation: 1465

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 ;

Upvotes: 1

Related Questions