MySQL与Java交互时毫秒处理

2023年3月25日 MySQL Joe.Ye

问题分析

MySQL字段类型datetime，实际存储的数据是：2020-09-08 17:01:16

Java的Date支持毫秒，比如：2020-09-08 17:01:15.618，通过SimpleDateFormat等格式化后的时间格式为：2020-09-08 17:01:15

导致MySQL读取数据与Java格式化数据不一致问题

原因：Mysql dategtime类型的四舍五入问题

查看MySQL 5.6 manual发现，5.6.4及以上版本的MySQL Server端确实支持fractional second part(fsp)，但如果Client提交过来的小数位数超过Server端建表时指定的小数位数，Mysql Server会自动进行四舍五入的截断，没有任何警告或异常。

解决方案

1、入库前舍弃毫秒部分。
2、设置Mysql dategtime类型精度

MySQL datetime值毫秒四舍五入

问题

MySQL 5.6 版本，datetime字段类型支持6位毫秒级别，插入的值可能会被四舍五入

实例1

CREATE TABLE appblog (
`id` int(11) NOT NULL AUTO_INCREMENT,
`gmt_create` datetime DEFAULT NULL,    #5.6 版本定义格式为 datetime(6) ，保留6位毫秒数值。不加表示不保留毫秒
PRIMARY KEY (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

> insert into appblog select 1,'2018-08-28 23:59:59.999';

> select * from appblog;
+----+---------------------+
| id | gmt_create |
+----+---------------------+
| 1  | 2018-08-29 00:00:00 |    # 字段值变了！！！
+----+---------------------+
1 row in set (0.00 sec)

实例2

CREATE TABLE appblog (
`id` int(11) NOT NULL AUTO_INCREMENT,
`gmt_create` datetime(3) DEFAULT NULL,
PRIMARY KEY (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

> insert into appblog select 1,'2018-08-28 23:59:59.999';

> insert into appblog select 2,'2018-08-28 23:59:59.999999';

> insert into appblog select 3,'2018-08-28 23:59:59';

> select * from appblog;

+----+-------------------------+
| id | gmt_create |
+----+-------------------------+
| 1  | 2018-08-28 23:59:59.999 |
| 2  | 2018-08-29 00:00:00.000 |    # 又变了！！！
| 3  | 2018-08-28 23:59:59.000 |
+----+-------------------------+
3 rows in set (0.00 sec)

> select * from appblog where gmt_create='2018-08-28 23:59:59';    #查询还有精确度要求
+----+-------------------------+
| id | gmt_create |
+----+-------------------------+
| 3 | 2018-08-28 23:59:59.000 |
+----+-------------------------+