MySQL精度问题-hekui

一、MySQL中datetime类型怎么处理毫秒

MySQL中datetime日期类型，默认是精确到秒的。从MySQL5.6.4版本开始，可以设置精度，如datetime(3)表示精确到毫秒

类型	说明	插入时赋值	DB实际存储
datetime(0)或datetime	精确到秒	2024-05-19 00:00:00:499 2024-05-19 00:00:00:500	2024-05-19 00:00:00 2024-05-19 00:00:01
datetime(3)	精确到毫秒	2024-05-19 00:00:00:499 2024-05-19 00:00:00:500	2024-05-19 00:00:00:499 2024-05-19 00:00:00:500

实验：

数据库版本： MySQL5.7.34

表定义：

create table date_test
(
    id              int auto_increment primary key,
    date_field      date                                null,
    datetime_field  datetime                            null,
    datetime3_field datetime(3)                         null,
    timestamp_field timestamp default CURRENT_TIMESTAMP not null on update CURRENT_TIMESTAMP,
    remark          int                                 null
);

插入2024-05-18 23:59:59:000 ~ 2024-05-19 00:00:01:000，每1毫秒插入一条数据

　　@Test
	public void testInsert(){
		List<DateTestDO> dateTestDOList= new ArrayList<>();
		//2024-05-19 00:00:00 减去一秒
		long timeStamp = 1716048000000L - 1000L;
		for (int i = 0; i <=2000 ; i++) {
			long time = timeStamp+i;
			Date date = new Date(time);
			DateTestDO dateTestDO = new DateTestDO();
			dateTestDO.setDateField(date);
			dateTestDO.setDatetimeField(date);
			dateTestDO.setDatetime3Field(date);
			dateTestDO.setTimestampField(date);
			dateTestDO.setRemark(i+1);
			dateTestDOList.add(dateTestDO);
		}
		dateTestDao.batchInsert(dateTestDOList);
	}

观察DB数据：

datetime(0)类型，毫秒数小于500时直接舍弃；毫秒数大于等于500时+1秒

datetime(3)类型，能保存毫秒数，不会加1秒

 

二、按datetime(0)类型字段检索时有精度问题吗？

例如需要查询2024-05-19这一天中的所有订货单，订单日期查询条件为 2024-05-19 00:00:00:000 ~ 2024-05-19 23:59:59:999，订单日期使用 datetime进行存储，时间部分固定全为0，如2024-05-18 00:00:00

构造实验数据：2024-05-10~2024-05-20每天保存一张订货单，时间部分都是0，例如2024-05-10 00:00:00:000

查询代码：

　　@Test
	public void testQuery(){
		//2024-05-18 00:00:00:000
		long startTime = 1715961600000L;
		//2024-05-18 23:59:59:999
		long endTime = 1716047999999L;
		List<DateTestDO> dateTestDOS = dateTestDao.queryByDate(new Date(startTime), new Date(endTime));
		System.out.println(dateTestDOS.size());
		System.out.println(dateTestDOS.get(0));
		System.out.println(dateTestDOS.get(dateTestDOS.size()-1));
	}

    <select id="queryByDate" resultType="cn.hekui.model.DateTestDO">
        select * from date_test
        where datetime_field >= #{start,jdbcType=TIMESTAMP} and datetime_field <= #{end,jdbcType=TIMESTAMP};
    </select>

在上面的插入实验中，毫秒数大于500时会加一秒，那查询条件中的 end时间为2024-05-18 23:59:59:999会进位为 2024-05-19 00:00:00吗？会查出05-19的记录吗？

查询结果如下：

可以看到查询结果只有1条，只查询到了05-18的记录，并没有查询到05-19的记录。

结论：在进行日期查询时，MySQL不会自动将毫秒部分加到秒上。