MySQL中的日期时间类型与格式化方式总结

深入理解MySQL中时间范围的处理方式

说实话，当我第一次接手这个项目时，我对 MySQL 的处理时间感到头疼。
那时，系统中的时间字段一片混乱。
有些存储 DATETIME 字符串，有些直接存储字符串。
这是一件可怕的事情。
现在想起来，要掌握的重点是存储格式、比较方法、性能测试。

我们先来说一下保存格式。
我以前有一个电商项目，orders表直接使用VARCHAR来存储时间。
因此，在导入数据时经常会出现问题。
后来我改用了DATETIME，这样查询效率更高，效率提高了一倍。
当谈到选择哪种类型时，我建议您记住以下几点：
如果日期和时间指定的精度为秒，例如创建订单时，没有选择，应该是DATETIME
如果您只需要一个日期，例如用户的生日，那么使用 DATE 是最简单的。

对于计划任务等纯时间信息，TIME是最合适的
我记得有一次我从旧系统升级，将TIME类型更改为DATETIME。
结果，users表中突然出现了大量的“00:00:00”条目。
原来有人传递了NULL作为时间。
教训是，在节省时间之前，您必须将您的需求与业务方面保持一致，并且不要认为这是理所当然的。

与BETWEEN操作相比，它是最简单粗暴的，但是有一个陷阱需要注意。
有一天，我检查了特定日期的订单，并在“2 02 3 -03 -1 0”和“2 02 3 -03 -1 1 ”之间写道。
原来，前天晚上2 3 :5 9 :5 9 找到的订单居然不见了。
后来发现MySQL是有边界的，应该写成>=和<。
每次我教给新人这个，我都要解释很长时间，而他们却从来不记得。

对于高级功能，我更喜欢使用 DATE_FORMAT。
有一个旅游网站，显示的预订时间应该是“2 02 3 年3 月1 0日下午2 点3 0分”。
您可以直接使用此功能来实现此目的。
但注重表现。
我测试了一张大桌子。
使用DATE_FORMAT(event_time)会直接导致索引失败。
如果你使用 >= 和 < 那就没问题了。
我个人没有在这方面运行过PostgreSQL，所以我不知道其他数据库是否也有同样的工作方式。

至于索引，我建议对所有时间字段建立索引。
有一天，当我检查特定月份的销售数据时，表中的 event_time 没有建立索引。
结果，全表扫描期间CPU使用率飙升至1 00%。
但指标也是具体的。
例如分区表就特别实用。
我们有一个按月划分的日志表。
查看去年3 月的数据时，直接找到p2 02 3 03 段，查询速度与查询本地文件相差无几。

我遇到了与时区相关的陷阱。
有一天项目部署在国外的服务器上，时区没有改变。
结果，所有 UTC 时间均被保留。
当用户查看报告时，国内用户认为这是前一天的订单。
后来在应用层面统一处理时区，问题就解决了。
我目前的习惯是存储UTC并在前端显示时根据用户的时区传递它。

最后说一下业务场景。
在统计每日活跃用户时，我经常使用 WHERE action_time BETWEEN '2 02 3 -03 -1 0 00:00:00' AND '2 02 3 -03 -1 0 2 3 :5 9 :5 9 '，这样简单明了。
然而，有一个大平台，由于流量大，这种记录方法仍然很慢。
后来改成了使用一系列事件标记+索引，这样效率就高多了。
因此，我建议你多尝试EXPLAIN，不要盲目添加索引。

其实时间处理的本质就这么几个字：保存、比较、快速查看。
说太多会使局势变得混乱。
关键是灵活定制，适合自己的业务场景。
我见过有些人使用字符串来存储时间，有些人使用字符串来存储时间使用整数来存储时间戳。
他们都有他们的理由。
最重要的是要理解为什么它是这样安排的，以至于以后你甚至无法向自己解释它。