Search code examples
mysqlsqllast-insert-id

INSERT doesn't work when followed by SELECT LAST_INSERT_ID

There are a lot of questions about LAST_INSERT_ID()

In my case, issue is:

When INSERT is followed by SELECT LAST_INSERT_ID() there are no records being inserted

INSERT INTO sequences (state)  VALUES (0);
select LAST_INSERT_ID();
>>> 80  // nothing is added to DB

INSERT on it's own works OK

INSERT INTO sequences (state)  VALUES (0);
>>>
select LAST_INSERT_ID();
>>> 81 // row is inserted

For testing I am using SequelPro, DB is Amazon's RDS MySQL. Same issue happens when I use Python's MySQLdb module.

Ideally I want to insert row, get back ID of it for future identification and use.

Solution

  • You should run one query at a time. Most SQL interfaces don't allow multiple queries.

    MySQL allows a single query to be terminated by ; but if there's any words following the ; (except for a comment), it's be a syntax error, which will make the whole request fail. So the INSERT won't run either.

    MySQL does have a connection option to allow multi-query, but it's not active by default. See https://dev.mysql.com/doc/refman/8.0/en/c-api-multiple-queries.html

    There's really no reason to use multi-query. Just run the SELECT LAST_INSERT_ID() as a separate query following the INSERT. As long as you use the same connection, it'll give you the right answer.