MySQL保存时间时居然四舍五入了

最近遇到一个数据库导致的时间倒流问题，把时间插入数据库后，其他流程再读取出来，发现该时间落在了当前时间的后面，看起来就是时间倒流。经过排查后发现原来是因为MySQL支持小数秒（一般叫做分秒），但是数据库字段长度不够，导致了四舍五入

问题展示

插入不同大小的小数秒可以看到四舍五入的效果

CREATE TABLE `user_tenmao` (
  `id` int NOT NULL AUTO_INCREMENT,
  `name` varchar(32) NOT NULL,
  `birth_time` timestamp NULL DEFAULT NULL,
  `create_time` timestamp(2) NOT NULL DEFAULT CURRENT_TIMESTAMP,
  `update_time` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
  PRIMARY KEY (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

mysql> insert into user_tenmao (name, birth_time) values('tim2', '2021-06-03T20:26:42.715');
Query OK, 1 row affected (0.01 sec)

mysql> select * from user_tenmao;
+----+------+---------------------+---------------------+---------------------+
| id | name | birth_time          | create_time         | update_time         |
+----+------+---------------------+---------------------+---------------------+
|  1 | tenmao  | 2021-06-03 20:26:43 | 2021-06-03 20:45:58 | 2021-06-03 20:45:58 |
+----+------+---------------------+---------------------+---------------------+
1 row in set (0.01 sec)

插入时间是2021-06-03T20:26:42.715，到数据库后进位得到2021-06-03 20:26:43

mysql> insert into user_tenmao (name, birth_time) values('tim2', '2021-06-03T20:26:42.215');
Query OK, 1 row affected (0.01 sec)

mysql> select * from user_tenmao;
+----+------+---------------------+---------------------+---------------------+
| id | name | birth_time          | create_time         | update_time         |
+----+------+---------------------+---------------------+---------------------+
|  1 | tenmao  | 2021-06-03 20:26:43 | 2021-06-03 20:45:58 | 2021-06-03 20:45:58 |
|  2 | tim2 | 2021-06-03 20:26:42 | 2021-06-03 20:49:39 | 2021-06-03 20:49:39 |
+----+------+---------------------+---------------------+---------------------+
2 rows in set (0.00 sec)

插入时间是2021-06-03T20:26:42.215，到数据库后进位得到2021-06-03 20:26:42

保存小数秒

timestamp(2)，后面的数字表示小数秒的位数

CREATE TABLE `user_tim2` (
  `id` int NOT NULL AUTO_INCREMENT,
  `name` varchar(32) NOT NULL,
  `birth_time` timestamp(2) NULL DEFAULT NULL, # 2位小数
  `create_time` timestamp(4) NOT NULL DEFAULT CURRENT_TIMESTAMP(4), # 4位小数
  `update_time` timestamp(6) NOT NULL DEFAULT CURRENT_TIMESTAMP(6) ON UPDATE CURRENT_TIMESTAMP(6), # 6位小数
  PRIMARY KEY (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

## 注意CURRENT_TIMESTAMP也需要对应的位数，否则会有异常`ERROR 1067 (42000): Invalid default value for 'create_time'`

mysql> insert into user_tim_2 (name, birth_time) values('tim2', '2021-06-03T20:26:42.715');
Query OK, 1 row affected (0.01 sec)

mysql> select * from user_tim2;
+----+------+------------------------+--------------------------+----------------------------+
| id | name | birth_time             | create_time              | update_time                |
+----+------+------------------------+--------------------------+----------------------------+
|  1 | tim2 | 2021-06-03 20:26:42.72 | 2021-06-03 20:55:21.2476 | 2021-06-03 20:55:21.247616 |
+----+------+------------------------+--------------------------+----------------------------+

参考

• Fractional Seconds in Time Values