Mysql LAST_INSERT_ID函数

参考:官网说明

什么是LAST_INSERT_ID?

顾名思义，LAST_INSERT_ID，最近插入ID，用于得到表中自增列的值。

With no argument, LAST_INSERT_ID() returns a BIGINT UNSIGNED (64-bit) value representing the first automatically generated value successfully inserted for an AUTO_INCREMENT column as a result of the most recently executed INSERT statement. The value of LAST_INSERT_ID() remains unchanged if no rows are successfully inserted.
LAST_INSERT_ID()没有参数，返回值是无符号的64位长整型整数，该值来自最新添加数据中的自增列(如果一次添加多条数据取第一条数据自增id)；LAST_INSERT_ID()返回值不变直到下一条数据添加成功。
需要注意的点:
1. 会话之间LAST_INSERT_ID()值互不影响,默认值是0。

A,B:
mysql> select last_insert_id();
+------------------+
| last_insert_id() |
+------------------+
|                0 |
+------------------+
1 row in set (0.00 sec)
A:
mysql> insert into demo(value) values('1');
Query OK, 1 row affected (0.00 sec)
mysql> select last_insert_id();
+------------------+
| last_insert_id() |
+------------------+
|                1 |
+------------------+
1 row in set (0.00 sec)
B:
mysql> select last_insert_id();
+------------------+
| last_insert_id() |
+------------------+
|                0 |
+------------------+
1 row in set (0.00 sec)

A中last_insert_id值与B中last_insert_id值互不影响。
2. 一次添加多条数据，返回值是首条id。

mysql> insert into demo(value) values('1'),('2');
Query OK, 2 rows affected (0.00 sec)
Records: 2  Duplicates: 0  Warnings: 0
mysql> select last_insert_id();
+------------------+
| last_insert_id() |
+------------------+
|                2 |
+------------------+
1 row in set (0.00 sec)

1. LAST_INSERT_ID()值可以设置(貌似没什么用)。

mysql> select last_insert_id(10);
+--------------------+
| last_insert_id(10) |
+--------------------+
|                 10 |
+--------------------+
1 row in set (0.00 sec)

当一张表中有AUTO_INCREMENT字段时，那么表中会有一个number来保存自增列，该number值最好使用乐观锁来保护，一个连接添加数据后把number值传给连接中的LAST_INSERT_ID值，该值可变且对表中number值无影响。