CODEWITHSUNDEEP
mysqlsql-insertauto-incrementmyisamlast-insert-id
Chap
Chap

Reputation: 3839

Retrieving an autoincrement value after INSERT duplicate key

I have a mySQL 5.7 myISAM table T:

Col  Key    Other
A    PRI   
B           autoincrement
C

I want to perform the following operation: Given the values x and y

The simplest way of doing this seems to be

SELECT B FROM T WHERE A=x;
if the row doesn't exist,
INSERT INTO T SET A=x, C=y;
SELECT LAST_INSERT_ID();

However, this is non-atomic, meaning I would need to implement some sort of locking.

I was hoping I could use

INSERT IGNORE INTO T SET A=x, C=y;
SELECT LAST_INSERT_ID();

but LAST_INSERT_ID() doesn't return B when the INSERT is ignored on a duplicate key.

Is there a simple way to accomplish this atomically, without locking?

Upvotes: 0

Views: 405

Answers (2)

Rick James
Rick James

Reputation: 142560

See the example of how to use id=LAST_INSERT_ID(id) in the UPDATE part of IODKU. https://dev.mysql.com/doc/refman/5.6/en/insert-on-duplicate.html

Upvotes: 1

Barmar
Barmar

Reputation: 782785

I don't think there's a way to do it with LAST_INSERT_ID(), since that's only set when something is actually inserted and a new ID is assigned. You'll have to select the key you just inserted:

INSERT IGNORE INTO T SET a=x, C=y;
SELECT B FROM T WHERE a = x;

Upvotes: 0

Related Questions