技术学习生活分享 -【由last_insert

由last_insert_id()引发的错误

今天碰到一个错误，如下：

// 伪代码，只表述实际工作流程
$sql_1 = "insert into t1('name') values ('wuzehui')"; 
$db->query($sql_1);
if($db->getLastInsertId() < 1){
    $db->rollback();
    return false;
}

$sql_2 = "insert into t2('name') values ('wuzehui')";
$db->query($sql_2);
if($db->getLastInsertId() > 1){
    $db->commit();
    return true;
} else {
    $db->rollback();
    return false;
}

代码的执行流程如上所述，先执行一个插入语句，如果最后插入的id是大于1的说明执行成功，提交事物，否则回滚。

如果所有的sql都正常插入了，那么没有问题；但是如果第一个sql成功了，第二个sql插入失败了，我们预想的可能是第二个getLastInsertId()返回0，从而回滚事物，但是实际上getLastInsertId()返回的是第一个成功插入的sql返回的id，最终导致问题。

之所以这样写通常是下面的逻辑会用到上面插入返回的自增id，为了方便就直接用这个id判断了。

所以比较靠谱的办法是通过query()的返回值来进行判断。

各种MVC框架中获取插入id的方法到MySQL层面执行的都是last_insert_id()函数，所以看看文档上怎么写的这个函数：

LAST_INSERT_ID() && LAST_INSERT_ID(expr)

对于没有参数的调用，LAST_INSERT_ID()返回一个64位BIGINT UNSIGNED的值，这个值代表了上一个sql成功执行后，AUTO_INCREMENT列自动生成的值，这些sql通常是insert语句。如果数据没有插入成功，这个函数的返回值不变。

例如，插入生成AUTO_INCREMENT值之后，你可以这样使用：

mysql> SELECT LAST_INSERT_ID();
-> 195

当前正在执行的语句不会影响该函数的返回值。假设你通过一个语句生成了一个AUTO_INCREMENT值，然后在有AUTO_INCREMENT列的表上执行多行插入语句，这个LAST_INSERT_ID()的值始终保持为第二个语句生成的值；第二个和后面的行不会被早期的插入影响。（然而，如果混用LAST_INSERT_ID()和LAST_INSERT_ID(expr)就不确定了）（注：这块不是很理解，难道会在sql里面使用LAST_INSERT_ID()？感觉没有必要呢）

如果前面的语句返回错误，LAST_INSERT_ID()的值不确定，对于事务中的表，如果事务由于错误而回滚，该函数的值也是未定义的。对于手动回滚，该函数的值不会重新设置为事务前的值，该值始终为回滚时间点上的值。

在MySQL5.7.3之前，如果使用了复制过滤规则，该函数未能正确过滤（Bug #17234370, Bug #69861），（没理解到底是什么规则）。

在存储过程，函数与触发器内部，LAST_INSERT_ID()的行为和外面没有什么区别。存储过程或触发器对该函数的影响是否被之后的语句可见需要看执行的种类：

如果存储过程对LAST_INSERT_ID()的值进行了修改，那么存储过程调用之后的执行的语句都可以看到更改后的值。
如果函数和触发器修改了LAST_INSERT_ID()的值，那么该函数的值会在函数结束后重置，所以后续的执行不会看到修改的值。

生成的ID是以每个连接为基础保留在服务端的。这意味着该函数返回给当前客户端的值是该这客户端最近执行语句生成的AUTO_INCREMENT值。这个值不能受其他客户端的影响，即使他们生成自己的AUTO_INCREMENT值。这种行为确保了每个客户端能获取到他们自己的ID而不用担心其他客户端，并且也不需要锁和事务。

如果为AUTO_INCREMENT列设置了一个不是NULL也不是0的值，LAST_INSERT_ID()的值保持不变。

注：如果使用一个INSERT语句插入多行数据，那么LAST_INSERT_ID()的值为第一个插入的行。这么做的原因可能是在其他服务器上更容易复制这个INSERT语句。（可能和主从同步有关，未验证）

例如：

mysql> USE test;

mysql> CREATE TABLE t (
id INT AUTO_INCREMENT NOT NULL PRIMARY KEY,
name VARCHAR(10) NOT NULL
);

mysql> INSERT INTO t VALUES (NULL, 'Bob');
mysql> SELECT * FROM t;
+----+------+
| id | name |
+----+------+
| 1 | Bob |
+----+------+

mysql> SELECT LAST_INSERT_ID();
+------------------+
| LAST_INSERT_ID() |
+------------------+
|
1 |
+------------------+

mysql> INSERT INTO t VALUES
(NULL, 'Mary'), (NULL, 'Jane'), (NULL, 'Lisa');

mysql> SELECT * FROM t;
+----+------+
| id | name |
+----+------+
| 1 | Bob |
| 2 | Mary |
| 3 | Jane |
| 4 | Lisa |
+----+------+

mysql> SELECT LAST_INSERT_ID();
+------------------+
| LAST_INSERT_ID() |
+------------------+
|
2 |
+------------------+

尽管上面第二个INSERT语句插入三条数据到表t中，生成的ID是这个INSERT的2，所以后面的select语句返回的LAST_INSERT_ID的值是2。

如果使用了INSERT IGNORE，这个行会被忽略。LAST_INSERT_ID()会保持当前的值不变（或者0，代表当前连接没有成功执行过INSERT语句），并且对于非事务性的表，AUTO_INCREMENT计数器不会增加。对于InnoDB表，如果innodb_autoinc_lock_mode设置为1或2，AUTO_INCREMENT计数器会增加。如下例子：

mysql> USE test;
mysql> SELECT @@innodb_autoinc_lock_mode;
+----------------------------+
| @@innodb_autoinc_lock_mode |
+----------------------------+
|
1 |
+----------------------------+

mysql> CREATE TABLE `t` (
`id` INT(11) NOT NULL AUTO_INCREMENT,
`val` INT(11) DEFAULT NULL,
PRIMARY KEY (`id`),
UNIQUE KEY `i1` (`val`)
) ENGINE=InnoDB DEFAULT CHARSET=latin1;

# Insert two rows
mysql> INSERT INTO t (val) VALUES (1),(2);

# AUTO_INCREMENT计数器变为3
mysql> SHOW CREATE TABLE t\G
*************************** 1. row ***************************
Table: t
Create Table: CREATE TABLE `t` (
`id` int(11) NOT NULL AUTO_INCREMENT,
`val` int(11) DEFAULT NULL,
PRIMARY KEY (`id`),
UNIQUE KEY `i1` (`val`)
) ENGINE=InnoDB AUTO_INCREMENT=3 DEFAULT CHARSET=latin1

# LAST_INSERT_ID() 值为1
mysql> SELECT LAST_INSERT_ID();
+------------------+
| LAST_INSERT_ID() |
+------------------+
|
1 |
+------------------+

# 尝试插入两条数据，错误被忽略
mysql> INSERT IGNORE INTO t (val) VALUES (1),(2);
Query OK, 0 rows affected (0.00 sec)
Records: 2 Duplicates: 2 Warnings: 0

# AUTO_INCREMENT计数器变为5
mysql> SHOW CREATE TABLE t\G
*************************** 1. row ***************************
Table: t
Create Table: CREATE TABLE `t` (
`id` int(11) NOT NULL AUTO_INCREMENT,
`val` int(11) DEFAULT NULL,
PRIMARY KEY (`id`),
UNIQUE KEY `i1` (`val`)
) ENGINE=InnoDB AUTO_INCREMENT=5 DEFAULT CHARSET=latin1

# 因为上面两条数据插入失败，LAST_INSERT_ID()的值不变
mysql> SELECT LAST_INSERT_ID();
+------------------+
| LAST_INSERT_ID() |
+------------------+
|
1 |
+------------------+

如果为LAST_INSERT_ID()提供了参数expr，函数会把参数值返回，并且这个值会被该函数记住，下次调用会返回该值。这能被用来模拟序列：

创建一个表来保存序列计数器，并且初始化它：

mysql> CREATE TABLE sequence (id INT NOT NULL);
mysql> INSERT INTO sequence VALUES (0);

使用这个表生成序列数：

mysql> UPDATE sequence SET id=LAST_INSERT_ID(id+1);
mysql> SELECT LAST_INSERT_ID();

UPDATE语句增加了序列计数器，下次调用LAST_INSERT_ID()返回被更新的值。SELECT语句也会获取这个值。

你也可以不用LAST_INSERT_ID()生成序列，但是使用这个函数的好处是ID值保存在服务端，为上一次自动生成的值。它是多用户安全的，因为多个客户端能访问UPDATE语句并且通过SELECT获取他们自己的序列值，而不会影响其他客户端，也不会被其他客户端影响。

上面这段的解释有点像程序语言中的会话，但是没有理解到底有什么用处呢？

（完）

本文作者：吴泽辉
本文链接：https://mutex.top/posts/ed38800a/
发表日期：2018年4月1日
版权声明：本文章为原创，采用《知识共享署名-非商业性使用-禁止演绎 4.0 国际许可协议》进行许可

由last_insert_id()引发的错误

LAST_INSERT_ID() && LAST_INSERT_ID(expr)

目录