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

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  其中用到update 也会遇到类似的问题，相同的数据的时候会遇到update_time 不会更新的情况