MySQL 中的 upsert 操作与时间戳自动更新

MySQL

MySQL 中的 upsert

在 MySQL 中,是没有直接的 upsert 功能的,但是我们可以利用唯一约束进行重复时更新操作。

示例:

INSERT INTO test(id) VALUES(1) ON DUPLICATE KEY UPDATE id=2

上述的 sql 语句在遇到重复的主键时,将会中断插入操作改为更新操作。

时间戳自动更新

有的时候,我们在表中会维护一个类似于 updateTime 的字段,每当对记录进行 update 操作时,这个字段都应该被更新到最新的时间。但是每次都要手动更新的话,就太麻烦了。MySQL 中对需要这样操作的字段提供了特殊的属性,在更新记录时它会自动更新。

示例:

CREATE TABLE `profile` (
  `id` int unsigned NOT NULL AUTO_INCREMENT,
  `userId` varchar(32) CHARACTER SET utf8mb4 COLLATE utf8mb4_0900_ai_ci NOT NULL,
  `name` varchar(8) DEFAULT NULL,
  `birth` date DEFAULT NULL,
  `updateTime` datetime NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
  PRIMARY KEY (`id`),
  UNIQUE KEY `userId` (`userId`)
)

先创建一个简单的表,在 updateTime 字段上,我们设置了默认值且自动更新。
插入一条数据:

INSERT INTO `profile`(userId,name) VALUES('testuid','john001')

然后看一下数据库中内容:

mysql> SELECT * FROM `profile`;
+----+---------+---------+-------+---------------------+
| id | userId  | name    | birth | updateTime          |
+----+---------+---------+-------+---------------------+
|  1 | testuid | john001 | NULL  | 2020-12-23 08:25:39 |
+----+---------+---------+-------+---------------------+
1 row in set (0.04 sec)

接着,测试一下自动更新功能:

mysql> UPDATE `profile` SET name='john002' WHERE userId='testuid';
Query OK, 1 row affected (0.01 sec)
Rows matched: 1  Changed: 1  Warnings: 0
mysql> SELECT * FROM `profile`;
+----+---------+---------+-------+---------------------+
| id | userId  | name    | birth | updateTime          |
+----+---------+---------+-------+---------------------+
|  1 | testuid | john002 | NULL  | 2020-12-23 08:30:24 |
+----+---------+---------+-------+---------------------+
1 row in set (0.05 sec)

很明显,我们只对 name 字段进行了更新,但是 updateTime 也被自动更新了。

奇怪的问题

观察下面的操作:

mysql> INSERT INTO `profile`(userId,name) VALUES('testuid','john003') ON DUPLICATE KEY UPDATE name='john003';
Query OK, 2 rows affected (0.01 sec)

语句执行成功了,猜测一下结果是什么。
我们这里会先触发 userId 重复,进而中断插入操作,转而进行更新操作。由于我们设置了 updateTime 自动更新,所以按理应该更新了 name 与 updateTime,那么到底是不是这样,看看吧:

mysql> SELECT * FROM `profile`;
+----+---------+---------+-------+---------------------+
| id | userId  | name    | birth | updateTime          |
+----+---------+---------+-------+---------------------+
|  1 | testuid | john003 | NULL  | 2020-12-23 08:36:15 |
+----+---------+---------+-------+---------------------+
1 row in set (0.06 sec)

的确,时间依然被更新了,到现在为止,我们都没有遇到一点问题。
但是下面的语句就不一样了:

mysql> UPDATE `profile` SET name='john003' WHERE userid='testuid';
Query OK, 0 rows affected (0.00 sec)
Rows matched: 1  Changed: 0  Warnings: 0

mysql> SELECT * FROM `profile`;
+----+---------+---------+-------+---------------------+
| id | userId  | name    | birth | updateTime          |
+----+---------+---------+-------+---------------------+
|  1 | testuid | john003 | NULL  | 2020-12-23 08:36:15 |
+----+---------+---------+-------+---------------------+
1 row in set (0.09 sec)

这一次,时间没有被更新。
第一次,我遇到这个情况时怎么也想不通,明明进行了更新操作,为什么没有作用呢。因为当时是在 mybatis 中进行的操作,所以看不到变更的行数。
因为我们的操作并没有对数据库产生实质的修改,我们的语句被跳过了(并没有被执行)。所以,更新时间戳的操作也就没有发生。通过在 MySQL 命令行中,我们可以直观地看到 sql 语句的执行情况,于是很快能定位到问题。
根本原因是因为数据库对于冗余的操作进行了优化,但是如果不了解这个优化的人,在某些特定场合下会产生错误的解读。

总结

时间戳 autoupdate 会在记录更新时自动更新为当前时间,但是有时会出现“失效”的情况,因为并不是所有的 update 语句最后都会被执行,当 update 更新后的数据与原来一致时,数据库会自动优化从而跳过语句执行,自动更新只会发生在语句真正执行的情况下,所以当我们遇到奇怪的数据库现象时,首先应该考虑每一步的执行结果是否都如预料的那般,往往就是我们想当然的地方出了错。

duplicate