Retrieving an autoincrement value after INSERT duplicate key

Question

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

• if there is already a row where A=x, give me back B
• otherwise, insert a row where A=x and C=y, and give me back B

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?

Answer 1

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;