转载

【MySQL】时间类型存储格式选择

一前言
昨天在给开发同学做数据库设计规范分享的时候，讲到时间字段常用的有三个选择datetime、timestamp、int，应该使用什么类型的合适？本文通过三种类型的各个维度来分析，声明:本文没有具体的结论，但是会给一个推荐使用方式，需要使用者结合自己的业务场景来具体选择。

二分析
int型：
存储长度: 4字节
表示范围: date('Y-m-d H:i:s', 4294967295) 最大到 2106-02-07 14:28:15 ，如果一个企业活过这么久，就需要数据库考虑 bigint 或者datetime类型了。
是否为空: 可以为空,但是业务逻辑设计建议设置非空
存储格式: 数值类型存储，节省空间
时区相关: 与时区无关
默认值 : 可以根据业务逻辑设置默认值为某个时间。
优点
1 类型简单,cpu处理该字段的运算会比较快，占用字节小，节省空间。
2 查询速度快。

datetime：
存储长度: 8字节
表示范围:'1000-01-01 00:00:00'-'9999-12-31 23:59:59'
是否为空: 允许为空值，可以自定义值，且insert和update操作不会自动修改其值。
储存格式: 以实际格式存储(Just stores what you have stored and retrieves the same thing which you have stored.)
时区相关: 与时区无关
默认值 : 不指定默认值的时候 MySQL会初始化为'0000-00-00 00:00:00'
mysql> CREATE TABLE `tm` (
-> `d1` int(10) unsigned NOT NULL default '0',
-> `d2` timestamp NOT NULL default CURRENT_TIMESTAMP,
-> `d3` datetime NOT NULL,
-> `d4` timestamp NOT NULL default CURRENT_TIMESTAMP on update current_timestamp
-> );
Query OK, 0 rows affected (0.02 sec)
mysql> insert into tm(d1,d4) values(1458612980,now());
Query OK, 1 row affected, 1 warning (0.00 sec)
mysql> select * from tm;
+------------+---------------------+---------------------+---------------------+
| d1 | d2 | d3 | d4 |
+------------+---------------------+---------------------+---------------------+
| 1458612980 | 2016-03-22 10:16:20 | 2016-03-22 15:21:21 | 2016-03-22 10:16:20 |
| 1458612980 | 2016-03-22 15:22:17 | 0000-00-00 00:00:00 | 2016-03-22 15:22:17 |
+------------+---------------------+---------------------+---------------------+
2 rows in set (0.00 sec)
优点 显示直观，不需使用函数做转换

timestamp：
存储长度: 4字节
是否为空: 允许为空值，但是不可以自定义值，所以为空值时没有任何意义。
表示范围:'1970-01-01 00:00:01'-'2038-01-19 03:14:07
是否为空: 允许为空值，可以自定义值，且insert和update操作不会自动修改其值。
存储格式: 值以UTC格式保存,即以毫秒为单位的数字存储（ it stores the number of milliseconds）
时区相关: 和时间相关，时区转化，存储时对当前的时区进行转换，检索时再转换回当前的时区。
默认值 : 可以设置为CURRENT_TIMESTAMP()，当前的系统时间。
gmt_modified timestamp not null default '0000-00-00 00:00:00' on update current_timestamp
字段属性加上 "on update current_timestamp",
1 在更新记录时不指定update timestamp字段的值，数据库会自动修改gmt_modified的值为当前系统的时间，
2 在插入记录时不指定timestamp字段和timestamp字段的值，插入后该字段的值会自动变为当前系统时间。
相比于 init 类型的可以自动更新为系统当前时间，其他并无优势。
三总结
对于如何选型，有如下三种层面性能，存储空间，时间范围的考虑。这里我从时间范围和存储空间层面推荐使用 int 或者bigint ，datetime 类型。bigint和datetime 占用的空间一样，唯一的差异是在性能上可能存在差异。当然如果你服务的企业有存在 102年的梦想，那我建议直接使用 datetime类型。2038年的时候，虽然我们(80后)估计已经在家养老或者身居高层，为了避免给后来的运维人员留坑，建议不要使用 timestamp 字段。
四推荐阅读
1 datatime和timstmap初始化
2 datetime官方文档
3 说说time_zone 带来的性能问题

正文到此结束