If I have a table with an id column (Autoincrement). How to get the value of the next insert id before actually inserting a new record in the same transaction?

CodePudding user response：

You can do this query:

SELECT AUTO_INCREMENT FROM INFORMATION_SCHEMA.TABLES
WHERE (TABLE_SCHEMA, TABLE_NAME) = ('mydatabase', 'mytable');

(Of course you would name your database and table, I just used examples.)

This would give you the next auto-increment value at the moment you run that query. But in the next instant, some other client could insert a row, causing that id to be used. So the result you value can be wrong almost as soon as you query it.

You could prevent concurrent inserts by locking your table, but most people prefer not to do that, because it blocks concurrent operations.

Other than that, the only way to guarantee you know the next auto-increment value in advance is to execute the INSERT, then query LAST_INSERT_ID() or a similar function provided by your connector. For example, PDO::lastInsertId().