Home | 简体中文 | 繁体中文 | 杂文 | Github | 知乎专栏 | 51CTO学院 | CSDN程序员研修院 | OSChina 博客 | 腾讯云社区 | 阿里云栖社区 | Facebook | Linkedin | Youtube | 打赏(Donations) | About
13.32. 时间自动被加一秒
上一页 第 13 章 FAQ 
知乎专栏 ｜ 多维度架构

13.32. 时间自动被加一秒

背景，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_date` (
  `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_date` (`date`) VALUES ('2022-12-05 11:40:49');
Query OK, 1 row affected (0.04 sec)

mysql> INSERT INTO `test`.`test_date` (`date`) VALUES ('2022-12-05 11:40:49.449');
Query OK, 1 row affected (0.04 sec)

mysql> INSERT INTO `test`.`test_date` (`date`) VALUES ('2022-12-05 11:40:49.500');
Query OK, 1 row affected (0.09 sec)

mysql> SELECT * FROM test.test_date;
+----+---------------------+
| 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

解决方案二，对于时间敏感的场合不允许差一秒，可以设置时间精度解决这个问题，例如 datetime(1)，最长设置 datetime(6)

		
CREATE TABLE `test_date` (
  `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_date` (`date`) VALUES ('2022-12-05 11:40:49');
Query OK, 1 row affected (0.02 sec)

mysql> INSERT INTO `test`.`test_date` (`date`) VALUES ('2022-12-05 11:40:49.449');
Query OK, 1 row affected (0.04 sec)

mysql> INSERT INTO `test`.`test_date` (`date`) VALUES ('2022-12-05 11:40:49.500');
Query OK, 1 row affected (0.09 sec)

mysql> SELECT * FROM test.test_date;
+----+-------------------------+
| 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_date` (
  `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_date`;
INSERT INTO `test`.`test_date` (`date`) VALUES ('2022-12-05 11:40:49');
INSERT INTO `test`.`test_date` (`date`) VALUES ('2022-12-05 11:40:49.449');
INSERT INTO `test`.`test_date` (`date`) VALUES ('2022-12-05 11:40:49.500');
SELECT * FROM test.test_date;

time 演示

		
DROP TABLE `test`.`test_date`;
CREATE TABLE `test_date` (
  `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_date` (`date`) VALUES ('11:40:49');
INSERT INTO `test`.`test_date` (`date`) VALUES ('11:40:49.449');
INSERT INTO `test`.`test_date` (`date`) VALUES ('11:40:49.500');
SELECT * FROM test.test_date;

time 操作演示

		
mysql> DROP TABLE `test`.`test_date`;
Query OK, 0 rows affected (0.16 sec)

mysql> CREATE TABLE `test_date` (
    ->   `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_date` (`date`) VALUES ('11:40:49');
Query OK, 1 row affected (0.04 sec)

mysql> INSERT INTO `test`.`test_date` (`date`) VALUES ('11:40:49.449');
Query OK, 1 row affected (0.04 sec)

mysql> INSERT INTO `test`.`test_date` (`date`) VALUES ('11:40:49.500');
Query OK, 1 row affected (0.09 sec)

mysql> SELECT * FROM test.test_date;
+----+----------+
| id | date     |
+----+----------+
|  4 | 11:40:49 |
|  5 | 11:40:49 |
|  6 | 11:40:50 |
+----+----------+
3 rows in set (0.00 sec)

增加时间精度设置

		 
mysql> DROP TABLE `test`.`test_date`;
Query OK, 0 rows affected (0.02 sec)

mysql> 
mysql> CREATE TABLE `test_date` (
	->   `id` int NOT NULL AUTO_INCREMENT,
	->   `date` datetime(1) DEFAULT NULL,
	->   PRIMARY KEY (`id`)
	-> ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_general_ci;
Query OK, 0 rows affected (0.00 sec)

mysql> 
mysql> INSERT INTO `test`.`test_date` (`date`) VALUES ('2022-12-05 11:40:49');
Query OK, 1 row affected (0.01 sec)

mysql> INSERT INTO `test`.`test_date` (`date`) VALUES ('2022-12-05 11:40:49.4');
Query OK, 1 row affected (0.00 sec)

mysql> INSERT INTO `test`.`test_date` (`date`) VALUES ('2022-12-05 11:40:49.5');
Query OK, 1 row affected (0.00 sec)

mysql> INSERT INTO `test`.`test_date` (`date`) VALUES ('2022-12-05 11:40:49.449');
Query OK, 1 row affected (0.00 sec)

mysql> INSERT INTO `test`.`test_date` (`date`) VALUES ('2022-12-05 11:40:49.500');
Query OK, 1 row affected (0.00 sec)

mysql> select * from `test`.`test_date`;
+----+-----------------------+
| id | date                  |
+----+-----------------------+
|  1 | 2022-12-05 11:40:49.0 |
|  2 | 2022-12-05 11:40:49.4 |
|  3 | 2022-12-05 11:40:49.5 |
|  4 | 2022-12-05 11:40:49.4 |
|  5 | 2022-12-05 11:40:49.5 |
+----+-----------------------+
5 rows in set (0.00 sec)

时间精度最长可以设置为 time(6)

		 
DROP TABLE `test`.`test_time`; 

CREATE TABLE `test`.`test_time` (
	`id` int NOT NULL AUTO_INCREMENT,
	`time` time(6) DEFAULT NULL,
	PRIMARY KEY (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_general_ci;

INSERT INTO `test`.`test_time` (`time`) VALUES ('11:40:49');
INSERT INTO `test`.`test_time` (`time`) VALUES ('11:40:49.4');
INSERT INTO `test`.`test_time` (`time`) VALUES ('11:40:49.5');
INSERT INTO `test`.`test_time` (`time`) VALUES ('11:40:49.449');
INSERT INTO `test`.`test_time` (`time`) VALUES ('11:40:49.500');

select * from `test`.`test_time`;

timestamp 演示

		 
DROP TABLE `test`.`test_timestamp`; 
CREATE TABLE `test`.`test_timestamp` (
	`id` int NOT NULL AUTO_INCREMENT,
	`d1` timestamp DEFAULT NULL,
	`d2` timestamp(3) DEFAULT NULL,
	PRIMARY KEY (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_general_ci;
INSERT INTO `test`.`test_timestamp` (`d1`,`d2`) VALUES ('2022-12-05 11:40:49','2022-12-05 11:40:49');
INSERT INTO `test`.`test_timestamp` (`d1`,`d2`) VALUES ('2022-12-05 11:40:49.4','2022-12-05 11:40:49.4');
INSERT INTO `test`.`test_timestamp` (`d1`,`d2`) VALUES ('2022-12-05 11:40:49.5','2022-12-05 11:40:49.5');
INSERT INTO `test`.`test_timestamp` (`d1`,`d2`) VALUES ('2022-12-05 11:40:49.449','2022-12-05 11:40:49.449');
INSERT INTO `test`.`test_timestamp` (`d1`,`d2`) VALUES ('2022-12-05 11:40:49.500','2022-12-05 11:40:49.500');
select * from `test`.`test_timestamp`;
上一页 上一级 
13.31. mysql: [Warning] Using a password on the command line interface can be insecure. 起始页