MySQL last_insert_id的值

user_id设置为primary key，auto_increment

查询现有user_id

mysql> select * from test;
+---------+----------+
| user_id | name     |
+---------+----------+
|       1 | aaaaaa   |
|       2 | bbbbbbbb |
+---------+----------+
2 rows in set (0.03 sec)

插入一条记录

mysql> insert into test (name) values ('ccccc');
Query OK, 1 row affected (0.01 sec)

查询last_insert_id

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

一次插入多条记录

mysql> insert into test (name) values ('ccccc'),('dddddd'),('ee');
Query OK, 3 rows affected (0.04 sec)
Records: 3  Duplicates: 0  Warnings: 0

再次查询last_insert_id

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

对比整个表

mysql> select * from test;
+---------+----------+
| user_id | name     |
+---------+----------+
|       1 | aaaaaa   |
|       2 | bbbbbbbb |
|       3 | ccccc    |
|       4 | ccccc    |
|       5 | dddddd   |
|       6 | ee       |
+---------+----------+
6 rows in set (0.00 sec)

发现last_insert_id只是返回最后一次插入的第一条的primary key，并不是返回最后一次插入的最大的primary key

 

如果insert 避免重复使用了on duplicate key update,这个时候使用select last_insert_id()；不会返回更新的id，需要在update中加点小动作

mysql> insert into test (user_id,name) values (2,'aaa') on duplicate key update name='duplicate name', user_id=last_insert_id(user_id);
Query OK, 2 rows affected (0.02 sec)

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

mysql> insert into test (name) values ('aaa');
Query OK, 1 row affected (0.00 sec)

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

注意primary key一定要有auto_increment属性，不然last_insert_id()会返回0，

如果primary key没有auto_increment属性，就先drop primary key，然后再change column加上primary key和auto_increment属性，添加primary key的时候注意id不能为0，也不能有重复的