/

在 MySQL 中选择合适的日期类型

如何在 MySQL 中选择合适的日期类型困扰了很久,varcharinttimestampdatetime 都有尝试过,近来有所感悟,做此总结。

注:此总结考虑了 PHP 和 Laravel 框架的特点。

使用 varchar

varchar 存储日期时间的格式完全可以自己控制,月/日/年 还是 年-月-日 需求怎么说就怎么存,读取后展示是也不用在格式化。同时伏笔也就此埋下:日期时间格式没强制约束,总有一天字段里出现了与众不同的格式;要是日期时间会 _变化_ 或作为 查询条件 或要进行 _排序_ 时就又是一坑,还是要格式化标准格式再处理。可以说 varchar 应该是最差的选择了。

使用 int 与 timestamp

PHP time() 可以直接获取当前时间戳秒数,数据库字段要也是 int 一存就完事了,不会有格式问题,谁用什么样转什么样。但是在数据库工具中查看此字段时显示不够直观,范围时会不方便,这些在使用 timestamp 是会得到解决。

timestamp 是我一直迷惑的一个类型。我写了几个例子做测试:

  1. 将 Laravel 项目设置为 CST 中国标准时间,MySQL 时区设置为 UTC,使用 now() 获取当前日期时间,比如:2018-5-25 11:00:00 存入 timestamp 类型的字段中,使用数据库工具查看字段结果为仍然为 2018-5-25 11:00:00
  2. 继续上面的操作,项目中使用查询语句查询刚才的记录,结果显示为 2018-5-25 11:00:00,将项目时区从 CST 改为 UTC 后再次查询的结果仍然为 2018-5-25 11:00:00 没有变化。
  3. 继续上面的操作,将数据库的时区改为 +8:00,数据库工具、项目查询后的结果为 2018-5-25 19:00:00 发生了变化,修改项目为 CST 查询结果是 2018-5-25 19:00:00 和刚才一样也变化了。

这个测试说明了:

  • 项目的时区影响的是 PHP 的时区,影响的是 now() 产生的日期时间。
  • 一个日期时间从项目存入数据库时,这个日期时间的时区是数据库设置的时区,和项目无关了。传入什么样子数据库存什么样子,更换了参照系,但是没有转换日期时间。
  • 从数据库中读一个时间戳日期时间,这个是时间戳日期时间受到数据库的时区影响,和项目的时区无关。

我现在认识的结论:

  • 因为数据库时区不可能轻易改变,所以依靠数据库转换时区不可能。
  • 没看出来时间戳对于处理时区对 datatime 有什么过人之处,若有就是时间戳将时间标准设置在了 UTC,占 4 字节更小些。
  • 数据库时区和项目时区不一致可能会坑,如果一直不一致同时各个项目也一直错下去,没不会有察觉。
  • 对应国际化项目,是不是应该将数据库和项目还有服务器都设置为 UTC?谁使用谁根据用户的时区进行处理。

使用 timestamp 与 datetime

datetime 字段类型存储的时间不会随时间库时区发生变化,占 8 个字节,可存储 1000-01-01 00:00:009999-12-31 23:59:59。而时间戳只能存储 1970 年到 2038 年多,2038 还有 20 年怎么感觉马上就到了。

datetime 个人推荐的存储时间的格式。

时区处理常见问题

北京用户在北京时间 2018-05-27 10:00:00 发布一个文章。

如果在前端发布日期时间为 2018-05-27 10:00:00,在北京用户看来是没问题的,但是在美国的用户看来是奇怪的,因为美国没有到 2018-05-27 10:00:00 他们甚至还在 26 日。

这时 1 min ago 这种展示形式就解决了这个问题,ago 是一种绝对的方式,在一个月之后再显示完整的日期时间,此时世界各地肯定已经度过了这个要展示日期时间,无论在哪国用户显示 2018-05-27 10:00:00 都不会奇怪了。

同时也要注意,我们将各个时区都设置为 UTC 后,ago 的时间很好计算,但是在一个月之后日前时间完全显示,美国用户看北京用户的发布日期时间也应该是在中国看来的 2018-05-27 10:00:00,此发布日期不应该以美国用户为标准。这个问题的解决方法还在考虑,日期时间是存储多个字段来区分用于显示或比较,还是自行转换处理,需要实践测试。

MySQL 查看、修改时区

查看时区

> select now();   # 或 select curtime();

> show variables like "%time_zone%"; # SELECT @@global.time_zone, @@session.time_zone;
+------------------+--------+
| Variable_name | Value |
+------------------+--------+
| system_time_zone | CST |
| time_zone | SYSTEM |
+------------------+--------+

# time_zone 说明 mysql 使用 system 的时区,system_time_zone 说明 system 使用 CST 时区

修改时区

> set global time_zone = '+8:00';  # 修改 mysql 全局时区为北京时间,即我们所在的东8区
> set time_zone = '+8:00'; # 修改当前会话时区 SET time_zone = 'Asia/Shanghai'
> flush privileges; # 立即生效

通过修改 my.cnf 配置文件来修改时区

# vim /etc/my.cnf
# 在[mysqld]区域中加上
default-time-zone='+8:00'

# /etc/init.d/mysqld restart # 重启 mysql 使新时区生效

最后的总结

全球化的项目应当考虑:

  • 项目、数据库、服务器都建议设置为 UTC
  • 要考虑时分秒的字段用 datetime 类型,不用考虑时分秒的字段用 date
  • created_atupdated_at 字段,考虑 Laravel 框架的特点使用 timestamp

References:

– EOF –