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

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

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

使用 varchar

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

使用 int 与 timestamp

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

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

将 Laravel 项目设置为 CST 中国标准时间，MySQL 时区设置为 UTC，使用 now() 获取当前日期时间，比如：2018-5-25 11:00:00 存入 timestamp 类型的字段中，使用数据库工具查看字段结果为仍然为 2018-5-25 11:00:00。
继续上面的操作，项目中使用查询语句查询刚才的记录，结果显示为 2018-5-25 11:00:00，将项目时区从 CST 改为 UTC 后再次查询的结果仍然为 2018-5-25 11:00:00 没有变化。
继续上面的操作，将数据库的时区改为 +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:00 到 9999-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_at 和 updated_at 字段，考虑 Laravel 框架的特点使用 timestamp

References

mysql 修改时区的几种方法

– EOF –