前沿
最近工作中遇到两例 MySQL 时间戳相关的问题, 一个是 MySQL-connector-java 和 msyql 的精度不一致导致数据查不到; 另一例是应用服务器时区错误导致数据查询不到. 通过这篇文章, 希望能够解答关于 MySQL 中时间戳的几个问题:
MySQL 中的 DATETIME 精度为什么只支持到秒?
MySQL 中的 DATETIME 类型跟时区有关吗?
MySQL 设计表的时候, 表示时间的字段改如何选择?
案例分析 DATETIME 的精度问题
在我负责的应用中, 有个功能需要用到类似下面这种 SQL, 即使用时间戳作为查询的条件, 查询在某个时间戳之后的所有数据.
select * from adu_user where gmt_modified>= #{date}
前段时间, 将负责的应用的 MySQL-connector-java 的版本从 5.1.16 升级到 5.1.30, 在做功能回归的时候发现, 使用了类似上面的 SQL 的用例的运行时数据会有遗漏, 导致功能有问题.
经过排查发现: MySQL-connector-java 在 5.1.23 之前会将秒后面的精度丢弃再传给 MySQL 服务端, 正好我们使用的 MySQL 版本中 DATETIME 的精度是秒; 在我将 MySQL-connector-java 升级到 5.1.30 后, 从 java 应用通过 MySQL-connector-java 将时间戳传到 MySQL 服务端的时候, 就不会将毫秒数丢弃了, 从 MySQL-connector-java 的角度看是修复了一个 BUG, 但是对于我的应用来说却是触发了一个 BUG.
如果你面对这个问题, 你会怎么修复呢?
我们当时想了三种方案:
将 mybatis 的 Mapper 接口中的时间戳参数的类型, 从 java.util.Date 改成 java.sql.Date;
在传入 Mapper 接口之前, 将传入的时间戳按秒取正, 代码如下
在查询之前, 将传入的时间戳减 1 秒;
经过验证, 方案 1 会, java.util.Date 转过去的 java.sql.Date 对象会将日期之后的精度全部丢掉, 从而导致查询出更多不必要的数据; 方案 3 是可以的, 就是可能会查出多一两条数据; 方案 2 也是可以的, 相当于从代码上对 MySQL-connector-java 的特性做了补偿. 最终我选择的是方案 2.
案例复现
利用 homebrew 安装 MySQL, 版本是 8.0.15, 装好后建一个表, 用来存放用户信息, SQL 如下:
使用 spirngboot + mybatis 作为开发框架, 定义一个用户实体, 代码如下所示:
定义该实体对应的 Mapper, 代码如下:
设置连接 MySQL 相关的配置, 代码如下:
编写测试代码, 先插入一条数据, 然后用时间戳作为查询条件去查询, 代码如下:
运行单测, 如我们的设想, 确实是没有查询出数据来, 结果如下:
然后修改代码, 利用上面的代码将查询的时间戳按秒取正, 代码如下:
再次运行单测, 如我们的设想, 这次可以查询出数据来了.
不过, 这里有个小插曲, 我在最开始设计表的时候, 使用的 SQL 语句是下面这样的,
聪明如你一定发现了, 这里的 datetime 已经支持小数点后更小的时间精度了, 最多支持 6 位即最多可以支持到微妙级别. 这个特性是什么时候引入的呢, 我去查阅了 MySQL 的官方文档 , 发现这个特性是在 MySQL 5.6.4 之后开始支持的.
知识点总结
经过了前面的实际案例分析和案例复现, 想必读者已经对 MySQL 中 DATETIME 这个类型有了一定的认识, 接下来跟我一起看下, 我们从这个案例中可以总结出哪些经验.
MySQL-connector-java 的版本和 MySQL 的版本需要配套使用, 例如 5.6.4 之前的版本, 就最好不要使用 MySQL-connector-java 的 5.1.23 之前的版本, 否则就可能会遇到我们这次遇到的问题.
MySQL 中用来表示时间的字段类型有: DATE,DATETIME,TIMESTAMP, 它们之间有相同点, 各自也有自己的特性, 我总结了一个表格, 如下所示:
DATETIME 类型在 MySQL 中是以 "YYYYMMDDHHMMSS" 格式的整数存放的, 与时区无关, 使用 8 个字节的空间;
TIMESTAMP 类型可以保存的时间范围要小很多, 显示的值依赖时区, MySQL 的服务器, 操作系统以及客户端连接都有时区的设置.
一般情况下推荐使用 DATETIME 作为时间戳字段, 不推荐使用 bigint 类型来存储时间.
在开发中, 应该尽量避免使用时间戳作为查询条件, 如果必须要用, 则需要充分考虑 MySQL 的精度和查询参数的精度等问题.
参考资料
《高性能 MySQL》
来源: http://www.tuicool.com/articles/qMrMj2F