Mysql日期默认格式_【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 字段。四 推荐阅读

1datatime和timstmap初始化

2datetime官方文档

3说说time_zone 带来的性能问题