Guide reading
Usually, we call last? INSERT? Id() to get the latest auto increment ID after executing an INSERT, but this is not very reliable.
When the last INSERT id() function has no additional parameters or expressions, it returns an unsigned BIGINT. By default, it returns the value after the last INSERT of the autoincrement ID column.
Note that after the end of INSERT for the non auto increment ID column, the call to last? INSERT? Id() has no effect, for example:
[root@yejr.me]> create table tt ( `id` int(11) NOT NULL primary key, `c1` int(10) unsigned NOT NULL )engine=innodb; [root@yejr.me]> insert into tt values(0,0); Query OK, 1 row affected (0.01 sec) [root@yejr.me]> select last_insert_id(); +------------------+ | last_insert_id() | +------------------+ | 0 | +------------------+
In the application, many developers are used to calling the last insert id() function to obtain the last inserted self increment, but in fact, this is not reliable. Let's take a few examples:
Example 1: when the insertion fails
[root@yejr.me]> CREATE TABLE `t` ( `id` int(11) NOT NULL AUTO_INCREMENT, `c1` int(10) unsigned NOT NULL, PRIMARY KEY (`id`) ) ENGINE=InnoDB; # First insert, no problem [root@yejr.me]> insert into t select 0,rand()*1024; [root@yejr.me]> select last_insert_id(); +------------------+ | last_insert_id() | +------------------+ | 1 | +------------------+ # The second insertion, no problem [root@yejr.me]> insert into t select 0,rand()*1024; [root@yejr.me]> select last_insert_id(); +------------------+ | last_insert_id() | +------------------+ | 2 | +------------------+ # The third time you insert it, you deliberately create a duplicate primary key. This time it's not right [root@yejr.me]> insert into t values(0,rand()*1024), (3, rand()*1024); ERROR 1062 (23000): Duplicate entry '3' for key 'PRIMARY' [root@yejr.me]> select last_insert_id(); +------------------+ | last_insert_id() | +------------------+ | 3 | +------------------+ # There are only two records in the table [root@yejr.me]> select * from t; +----+-----+ | id | c1 | +----+-----+ | 1 | 784 | | 2 | 574 | +----+-----+ 2 rows in set (0.00 sec)
Example 2, when inserting multiple times at the same time
When there are multiple inserts, the second insert value is returned, for example:
# Now there are three records in the table [root@yejr.me]> select * from t; +----+-----+ | id | c1 | +----+-----+ | 1 | 784 | | 2 | 574 | | 5 | 681 | +----+-----+ 3 rows in set (0.00 sec) # Insert 3 more records at a time [root@yejr.me]> insert into t values (0,rand()*1024), (0, rand()*1024), (0,rand()*1024); # The value of last_insert_id() obtained is 6, obviously "not quite as expected" [root@yejr.me]> select last_insert_id(); +------------------+ | last_insert_id() | +------------------+ | 6 | +------------------+ [root@yejr.me]> select * from t; +----+-----+ | id | c1 | +----+-----+ | 1 | 784 | | 2 | 574 | | 5 | 681 | | 6 | 841 | | 7 | 112 | | 8 | 87 | +----+-----+ 6 rows in set (0.00 sec)
For example 3, when last insert id() has parameters
# Empty again [root@yejr.me]> truncate table t; # Insert 1 new record [root@yejr.me]> insert into t select 0,rand()*1024; # See last? Insert? Id(), as expected [root@yejr.me]> select last_insert_id(); +------------------+ | last_insert_id() | +------------------+ | 1 | +------------------+ [root@yejr.me]> select * from t; +----+-----+ | id | c1 | +----+-----+ | 1 | 730 | +----+-----+ # Add expression when last? Insert? Id() is called [root@yejr.me]> select last_insert_id(id+2) from t; +----------------------+ | last_insert_id(id+2) | +----------------------+ | 3 | +----------------------+ # Looking at the value of last_insert_id(), it seems that "it's not as expected again" [root@yejr.me]> select last_insert_id(); +------------------+ | last_insert_id() | +------------------+ | 3 | +------------------+ # Insert 1 new record [root@yejr.me]> insert into t select 0,rand()*1024; # Look at the value of last_insert_id(), it's like "back on track" [root@yejr.me]> select last_insert_id(); +------------------+ | last_insert_id() | +------------------+ | 2 | +------------------+ [root@yejr.me]> select * from t; +----+-----+ | id | c1 | +----+-----+ | 1 | 730 | | 2 | 600 | +----+-----+ 2 rows in set (0.00 sec)
Through several examples, we can see that it's not reliable to call the last ﹣ insert ﹣ id() function to get the maximum value of the auto increasing column in the table. If you need to build a sequence table, it's better to call the max() function every time to get the maximum value.
With MySQL version information:
[root@yejr.me]> \s ... Server version: 8.0.15 MySQL Community Server - GPL ...
Reference material
MySQL manual https://dev.mysql.com/doc/refman/8.0/en/information-functions.html#function_last-insert-id
Original link
https://mp.weixin.qq.com/s/exZ_Aq0HKjWdi6ybNerwwg