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: 0mysql> 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值无影响。