事情是这样的。
项目中有个逻辑,需要获取某个日期最后一天的最后时刻,如 2021-08-31 23:59:59。
于是,我封装了一个方法
public static LocalDateTime getLastDayTimeOfMonth(LocalDateTime localDateTime) {
return LocalDateTime.of(
localDateTime.toLocalDate().with(TemporalAdjusters.lastDayOfMonth()), LocalTime.MAX);
}
// 输出效果如下
// 2021-08-31T23:59:59.999999999
但是,在插入数据库后,数据变成了 2021-09-01 00:00:00
从表现上推测,应该是分数秒部分引起了进位。
进行实验后,验证了这个想法
-- 查询得到结果为 '2021-08-31 23:59:59'
insert into test values(1, '2021-08-31 23:59:59.499');
-- 查询得到结果为 '2021-09-01 00:00:00'
insert into test values(2, '2021-08-31 23:59:59.500');
我模糊的记得 DATETIME 类型是支持毫秒的!难道是我的 MySQL 版本问题?
于是,我从现有的环境中找到了不同版本的 MySQL( 5.6.26、5.7.17、5.7.26、8.0.24)进行验证,结果都存在四舍五入的“问题”。
正好最近在重学英语,就硬着头皮去啃官方文档,得出了如下结论:
-
DATETIME 类型支持到微秒。
With the fractional part included, the format for these values is
YYYY-MM-DD hh:mm:ss[fraction]
, the range forDATETIME
values is'1000-01-01 00:00:00.000000'
to'9999-12-31 23:59:59.999999'
如果包含分数秒部分,日期格式是
YYYY-MM-DD hh:mm:ss[fraction]
,DATETIME 类型可以表示的范围为'1000-01-01 00:00:00.000000'
到'9999-12-31 23:59:59.999999'
。 -
可以指定 DATETIME 分数秒部分的位数。
To define a column that includes a fractional seconds part, use the syntax
type_name(fsp)
, wheretype_name
isTIME
,DATETIME
, orTIMESTAMP
, andfsp
is the fractional seconds precision. For example:CREATE TABLE t1 (t TIME(3), dt DATETIME(6));
The
fsp
value, if given, must be in the range 0 to 6. A value of 0 signifies that there is no fractional part. If omitted, the default precision is 0. (This differs from the standard SQL default of 6, for compatibility with previous MySQL versions.)大意是可以使用形如
type_name(fsp)
的语法去定义 TIME、DATEITME 、TIMESTAMP 类型,fsp 表示分数秒的精度。fsp 必须在 0 - 6 之间。如果值为 0(默认值),表示没有分数秒部分。 -
如果插入数据超过指定位数,将会进行四舍五入。
Inserting a
TIME
,DATE
, orTIMESTAMP
value with a fractional seconds part into a column of the same type but having fewer fractional digits results in rounding. Consider a table created and populated as follows:CREATE TABLE fractest( c1 TIME(2), c2 DATETIME(2), c3 TIMESTAMP(2) ); INSERT INTO fractest VALUES ('17:51:04.777', '2018-09-08 17:51:04.777', '2018-09-08 17:51:04.777');
The temporal values are inserted into the table with rounding:
mysql> SELECT * FROM fractest; +-------------+------------------------+------------------------+ | c1 | c2 | c3 | +-------------+------------------------+------------------------+ | 17:51:04.78 | 2018-09-08 17:51:04.78 | 2018-09-08 17:51:04.78 | +-------------+------------------------+------------------------+
No warning or error is given when such rounding occurs. This behavior follows the SQL standard.
如果插入的数据比字段指定的分数秒部分多,将产生四舍五入的操作。官方也给出了上面的示例。这种情况下不会产生任何警告或错误,并且这样符合 SQL 标准。
所以,我的问题是插入数据时分数秒部分为 999(插入前转成了 date),但数据库在创建时没有指定 DATETIME 的分数秒位数(默认为0),故而产生了进位操作。
题外话:
- 结合上面几点,还可以推断出,对于类型为 DATETIME(6) 的字段,如果更新时分数秒部分超过6位,还是会产生四舍五入的操作。
-- 进位 2021-09-01 00:00:00.000000
insert into test values(2, '2021-08-31 23:59:59.9999999');
-- 舍去 2021-08-31 23:59:59.999999
insert into test values(3, '2021-08-31 23:59:59.9999994');
- 在使用内置函数获取时间时,默认没有分数秒部分,如果确实需要,必须在函数中指定分数秒的位数。
select now(6),now();
-- 输出分别为
-- 2021-08-14 15:30:01.014485
-- 2021-08-14 15:30:01
- Java 中 date 类型支持到毫秒,而 LocalTime 支持到纳秒。这里,因为项目中的 MyBatis 版本较低,我将 LocalDateTime 转换成了 date 类型才进行插入。其实 MyBatis-3.4.5 后已经支持 LocalDateTime 类型了。
参考: