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

矫情吗；* 2022-06-06 04:27 150阅读 0赞

> 主要参考博客（一些内容照搬了）：  
> \- [http://zhaohe162.blog.163.com/blog/static/38216797201122411193745/][http_zhaohe162.blog.163.com_blog_static_38216797201122411193745]  
> \- [http://blog.csdn.net/slvher/article/details/42298355][http_blog.csdn.net_slvher_article_details_42298355]  
> \- [http://www.jquerycn.cn/a\_14138][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`的, 不可能被其它的客户端连接改变。

[http_zhaohe162.blog.163.com_blog_static_38216797201122411193745]: http://zhaohe162.blog.163.com/blog/static/38216797201122411193745/
[http_blog.csdn.net_slvher_article_details_42298355]: http://blog.csdn.net/slvher/article/details/42298355
[http_www.jquerycn.cn_a_14138]: http://www.jquerycn.cn/a_14138