MySQL 写入数据库的时间自动被加一秒

抓挖攻城狮

程序猿/攻城狮/架狗狮/码畜/咖啡控

MySQL 写入数据库的时间自动被加一秒

时间自动被加一秒

背景，Spring Cloud + MyBatis-Plus 插入数据库的时间被无辜加了一秒中，MyBatis-Plus 日志 2022-12-05 11:40:49，MySQL binlog 2022-12-05 11:40:50。

经过排查，发现MyBatis-Plus传递给MySQL的时间携带了毫秒，2022-12-05 11:40:49.500。

CREATE TABLE `test_data` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `date` datetime DEFAULT NULL,
  PRIMARY KEY (`id`)
) ENGINE=InnoDB AUTO_INCREMENT=1 DEFAULT CHARSET=utf8mb4
mysql> INSERT INTO `test`.`test_data` (`date`) VALUES ('2022-12-05 11:40:49');
Query OK, 1 row affected (0.04 sec)
mysql> INSERT INTO `test`.`test_data` (`date`) VALUES ('2022-12-05 11:40:49.449');
Query OK, 1 row affected (0.04 sec)
mysql> INSERT INTO `test`.`test_data` (`date`) VALUES ('2022-12-05 11:40:49.500');
Query OK, 1 row affected (0.09 sec)
mysql> SELECT * FROM test.test_data;
+----+---------------------+
| id | date                |
+----+---------------------+
|  1 | 2022-12-05 11:40:49 |
|  2 | 2022-12-05 11:40:49 |
|  3 | 2022-12-05 11:40:50 |
+----+---------------------+
3 rows in set (0.00 sec)

第三条数据无故被增加了一秒，这是因为 500毫秒会做四舍五入，最终成了 2022-12-05 11:40:49.500

解决方案一，不要给 mysql 传递毫秒，将 2022-12-05 11:40:49.500 改为 2022-12-05 11:40:49

解决方案二，对于时间敏感的场合不允许差一秒，可以设置时间精度解决这个问题

CREATE TABLE `test_data` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `date` datetime(3) DEFAULT NULL,
  PRIMARY KEY (`id`)
) ENGINE=InnoDB AUTO_INCREMENT=4 DEFAULT CHARSET=utf8mb4
mysql> INSERT INTO `test`.`test_data` (`date`) VALUES ('2022-12-05 11:40:49');
Query OK, 1 row affected (0.02 sec)
mysql> INSERT INTO `test`.`test_data` (`date`) VALUES ('2022-12-05 11:40:49.449');
Query OK, 1 row affected (0.04 sec)
mysql> INSERT INTO `test`.`test_data` (`date`) VALUES ('2022-12-05 11:40:49.500');
Query OK, 1 row affected (0.09 sec)
mysql> SELECT * FROM test.test_data;
+----+-------------------------+
| id | date                    |
+----+-------------------------+
|  1 | 2022-12-05 11:40:49.000 |
|  2 | 2022-12-05 11:40:49.449 |
|  3 | 2022-12-05 11:40:49.500 |
+----+-------------------------+
3 rows in set (0.00 sec)

影响范围还有 timestamp, time

CREATE TABLE `test_data` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `date` timestamp NULL DEFAULT NULL,
  PRIMARY KEY (`id`)
) ENGINE=InnoDB AUTO_INCREMENT=4 DEFAULT CHARSET=utf8mb4
TRUNCATE `test`.`test_data`;
INSERT INTO `test`.`test_data` (`date`) VALUES ('2022-12-05 11:40:49');
INSERT INTO `test`.`test_data` (`date`) VALUES ('2022-12-05 11:40:49.449');
INSERT INTO `test`.`test_data` (`date`) VALUES ('2022-12-05 11:40:49.500');
SELECT * FROM test.test_data;

time 演示

DROP TABLE `test`.`test_data`;
CREATE TABLE `test_data` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `date` time NULL DEFAULT NULL,
  PRIMARY KEY (`id`)
) ENGINE=InnoDB AUTO_INCREMENT=4 DEFAULT CHARSET=utf8mb4;
INSERT INTO `test`.`test_data` (`date`) VALUES ('11:40:49');
INSERT INTO `test`.`test_data` (`date`) VALUES ('11:40:49.449');
INSERT INTO `test`.`test_data` (`date`) VALUES ('11:40:49.500');
SELECT * FROM test.test_data;

time 操作演示

mysql> DROP TABLE `test`.`test_data`;
Query OK, 0 rows affected (0.16 sec)
mysql> CREATE TABLE `test_data` (
    ->   `id` int(11) NOT NULL AUTO_INCREMENT,
    ->   `date` time NULL DEFAULT NULL,
    ->   PRIMARY KEY (`id`)
    -> ) ENGINE=InnoDB AUTO_INCREMENT=4 DEFAULT CHARSET=utf8mb4;
Query OK, 0 rows affected (0.22 sec)
mysql> 
mysql> INSERT INTO `test`.`test_data` (`date`) VALUES ('11:40:49');
Query OK, 1 row affected (0.04 sec)
mysql> INSERT INTO `test`.`test_data` (`date`) VALUES ('11:40:49.449');
Query OK, 1 row affected (0.04 sec)
mysql> INSERT INTO `test`.`test_data` (`date`) VALUES ('11:40:49.500');
Query OK, 1 row affected (0.09 sec)
mysql> SELECT * FROM test.test_data;
+----+----------+
| id | date     |