MySQL学习笔记（四）：MySQL中LAST_INSERT_ID()使用时的注意事项

主要参考博客（一些内容照搬了）：
- http://zhaohe162.blog.163.com/blog/static/38216797201122411193745/
- http://blog.csdn.net/slvher/article/details/42298355
- http://www.jquerycn.cn/a_14138

---

关于LAST_INSERT_ID()

LAST_INSERT_ID()是MySQL中的一个函数，自动返回最后一个INSERT或UPDATE查询中AUTO_INCREMENT列设置的第一个表发生的值。

---

注意事项

---

① 查询和插入所使用的Connection对象必须是同一个才可以，否则返回值是不可预料的；

使用这函数向一个给定Connection对象返回的值是该Connection对象产生对影响AUTO_INCREMENT列的最新语句第一个AUTO_INCREMENT值的。这个值不能被其它Connection对象的影响，即它们产生它们自己的AUTO_INCREMENT值。

---

② LAST_INSERT_ID() 是与table无关的，如果向表a插入数据后，再向表b插入数据，LAST_INSERT_ID()返回表b中的ID值；

即我们使用LAST_INSERT_ID()时返回的ID值时当前对象操作的最后一张表时所返回的ID值。

mysql> INSERT user_info(username,password,age,sex) VALUES('BBB',md5('B'),25,0);

mysql> SELECT * FROM user_info;
+----+----------+----------------------------------+------+-----+
| id | username | password                         | age  | sex |
+----+----------+----------------------------------+------+-----+
|  1 | AAA      | 7fc56270e7a70fa81a5935b72eacbe29 |   20 |   1 |
|  2 | BBB      | 9d5ed678fe57bcca610140957afab571 |   25 |   0 |
+----+----------+----------------------------------+------+-----+
2 rows in set (0.00 sec)

mysql> SELECT LAST_INSERT_ID();
+------------------+
| LAST_INSERT_ID() |
+------------------+
|                2 |
+------------------+
1 row in set (0.00 sec)

mysql> CREATE TABLE grade(
    -> id SMALLINT UNSIGNED PRIMARY KEY AUTO_INCREMENT,
    -> subjectname VARCHAR(30) NOT NULL,
    -> grade TINYINT UNSIGNED NOT NULL
    -> );
Query OK, 0 rows affected (0.03 sec)

mysql> INSERT grade(subjectname,grade) VALUES('Math',95);
Query OK, 1 row affected (0.01 sec)

mysql> SELECT LAST_INSERT_ID();
+------------------+
| LAST_INSERT_ID() |
+------------------+
|                1 |
+------------------+
1 row in set (0.00 sec)

---

③ 使用一条INSERT语句同时插入多个行的数据时， LAST_INSERT_ID() 只返回插入的第一行数据时产生的值。

这个应该算是一个易忽视的地方，我在刚开始使用LAST_INSERT_ID()这个函数时就犯了错。返回的是多行数据中第一行数据的ID值。
如此设定的原因是，这使依靠其它服务器复制同样的 INSERT语句变得简单。

mysql> SELECT * FROM user_info;
+----+----------+----------------------------------+------+-----+
| id | username | password                         | age  | sex |
+----+----------+----------------------------------+------+-----+
|  1 | AAA      | 7fc56270e7a70fa81a5935b72eacbe29 |   20 |   1 |
|  2 | BBB      | 9d5ed678fe57bcca610140957afab571 |   25 |   0 |
+----+----------+----------------------------------+------+-----+
2 rows in set (0.00 sec)

mysql> INSERT user_info(username,password,age,sex) VALUES('CCC',md5('C'),24,1),('DDD',md5('D'),22,0);
Query OK, 2 rows affected (0.00 sec)
Records: 2  Duplicates: 0  Warnings: 0

mysql> SELECT * FROM user_info;
+----+----------+----------------------------------+------+-----+
| id | username | password                         | age  | sex |
+----+----------+----------------------------------+------+-----+
|  1 | AAA      | 7fc56270e7a70fa81a5935b72eacbe29 |   20 |   1 |
|  2 | BBB      | 9d5ed678fe57bcca610140957afab571 |   25 |   0 |
|  3 | CCC      | 0d61f8370cad1d412f80b84d143e1257 |   24 |   1 |
|  4 | DDD      | f623e75af30e62bbd73d6df5b50bb7b5 |   22 |   0 |
+----+----------+----------------------------------+------+-----+
4 rows in set (0.00 sec)

mysql> SELECT LAST_INSERT_ID();
+------------------+
| LAST_INSERT_ID() |
+------------------+
|                3 |
+------------------+
1 row in set (0.00 sec)

---

④ LAST_INSERT_ID()如何保证其正确性

LAST_INSERT_ID()的值是由MySQL server来维护的，而且是为每条连接维护独立的值，也即，某条连接调用LAST_INSERT_ID()获取到的值是这条连接最近一次INSERT操作执行后的自增值，该值不会被其它连接的SQL语句所影响。这个行为保证了不同的连接能正确地获取到它最近一次INSERT SQL执行所插入的行的自增值，也就是说，LAST_INSERT_ID()的值不需要通过加锁或事务机制来保证其在多连接场景下的正确性。

---

⑤ 假如你使用 INSERT IGNORE而记录被忽略，则AUTO_INCREMENT 计数器不会增量，而 LAST_INSERT_ID() 返回0, 这反映出没有插入任何记录。

一般情况下获取刚插入的数据的ID，使用SELECT max(id) FROM table 是可以的。但在多线程情况下，就不行了。在多用户交替插入数据的情况下max(id)显然不能用。这就该使用LAST_INSERT_ID了，因为LAST_INSERT_ID是基于Connection的，只要每个线程都使用独立的Connection对象，LAST_INSERT_ID函数将返回该Connection对AUTO_INCREMENT列最新的INSERT or UPDATE操作生成的第一个record的ID。LAST_INSERT_ID是基于单个connection的, 不可能被其它的客户端连接改变。