细讲MySQL的日期戳(Timestamp)类别转载

在生产环境中部署了各种版本的。MySQL，包括MySQL 5.5/5.6/5.7三个主要版本和N一个小版本，因为MySQL向上兼容性差，导致相同SQL不同版本的性能有所不同。以下从几个方面详细描述了时间戳数据类型。

时间戳数据访问

在MySQL在以上三个主要版本中，默认时间戳(Timestamp)类型的值范围为1970-01-01 00:00:01 UTC 至2038-01-19 03:14:07 UTC，数据精确到第二级，值范围包括约。22价值数十亿，所以MySQL内部使用4个字节INT存储时间戳数据的类型：

1存储时间戳数据时，首先转换本地时区时间UTC时区时间，然后UTC时区时间已转换INT格式为毫秒值(使用UNIX_TIMESTAMP函数)然后存储在数据库中。

2读取时间戳数据时，首先。INT格式为毫秒值转换为UTC时区时间(使用FROM_UNIXTIME函数)，然后转换为本地时区时间，最后返回给客户端。

在MySQL 5.6.4在以后的版本中，时间戳类型的数据可以精确到最高的微秒。(百万分之一秒)并且定义了数据类型timestamp(N),N值范围为0-6，默认为0如果需要精确到毫秒，请设置Timestamp(3)如果需要精确到微秒，请设置timestamp(6)，提高数据准确性的代价是其内部存储空间变大，但时间戳类型的最小值和最大值范围没有改变。

时间戳字段定义

时间戳字段定义主要影响两种类型的操作：

1插入记录时，时间戳字段包含DEFAULT CURRENT_TIMESTAMP如果插入记录时未指定特定的时间数据，则时间戳字段值将设置为当前时间。

2更新记录时，时间戳字段包含ON UPDATE CURRENT_TIMESTAMP如果在更新记录时未指定特定的时间数据，则时间戳字段值将设置为当前时间。

PS1：CURRENT_TIMESTAMP表示使用CURRENT_TIMESTAMP()获取当前时间的函数，类似NOW()函数

根据以上两种类型的操作，时间戳列可以有四个组合定义，其含义如下：

1，定义字段时timestamp，表示插入和更新时字段不会自动设置为当前时间。

2，定义字段时timestamp DEFAULT CURRENT_TIMESTAMP，表示仅在插入字段且未指定值时将字段分配给当前时间，而在更新字段且未未指定值的情况下不会对其进行修改。

3，定义字段时timestamp ON UPDATE CURRENT_TIMESTAMP，表示插入时将字段分配给，但未指定值"0000-00-00 00:00:00",未指定值时更新为当前时间。

4，定义字段时timestamp DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP，表示插入或更新字段时不指定值，而是指定当前时间。

PS1：在MySQL表创建语句和在SHOW CREATE TABLE TB_XXX获取所创建表的表创建语句。

时间戳字段位于MySQL每个版本的使用差异

1、在MySQL 5.5和以前的版本一样，只能定义一个时间戳字段DEFUALT CURRENT_TIMESTAMP或ON UPDATE CURRENT_TIMESTAMP，但在MySQL 5.6和MySQL 5.7此限制已在版本中删除;

2、在MySQL 5.6版本中的参数explicit_defaults_for_timestamp默认值为1，在MySQL 5.7版本中的参数explicit_defaults_for_timestamp默认值为0;

3、在MySQL 5.5和MySQL 5.7版本中timestamp类型默认值NOT NULL,在在MySQL 5.6版本中timestamp类型默认值NULL;

4，当在语句中构建表时。c1 timestamp 时，

在MySQL 5.5中等价于c1 timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP;

在MySQL 5.6中等价于c1 timestamp NULL DEFAULT NULL;

在MySQL 5.7中等价于c1 timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP;

5，当在语句中构建表时c1 timestamp default 0时，

在MySQL 5.5中等价于c1 timestamp NOT NULL DEFAULT 0000-00-00 00:00:00;

在MySQL 5.6中等价于c1 timestamp NULL DEFAULT 0000-00-00 00:00:00;

在MySQL 5.7中等价于c1 timestamp NOT NULL DEFAULT 0000-00-00 00:00:00;

PS1: MySQL 5.6版本和MySQL 5.7版本中的主要差异受参数影响explicit_defaults_for_timestamp影响的默认值。

PS2：当时间戳列的默认值为0000-00-00 00:00:00使用此默认值“不在时间戳范围内”不会生成警告。

时间戳类型引发的异常

当MySQL参数time_zone=system时，查询timestampField将调用系统时区进行时区转换，由于系统时区中的全局锁定问题，当多个并发大数据量访问时，线程上下文将频繁切换，CPU使用率飙升，系统响应变慢，导致假死亡。

时间戳类型和时间类型选择

在部分"数据库指南"在文档中，建议使用timestamp类型代替datetime字段，这是正确的timestamp类型使用4字节，而datetime字段使用8字节，但随着磁盘性能的提高和内存成本的降低，在实际生产环境中，使用timestamp类型不会带来太多性能改进，但可能会导致timestamp类型限制和影响业务用途的定义和值范围。

在MySQL 5.6.4以及更高版本，您可以设置时间戳类型(timestamp)数据是最高精度的微秒，时间类型也可以是(datetime)数据最高精度微秒，时间类型(datetime)也可以获得相同的结果timestamp相同类型的效果，例如定义字段 dt1 DATETIME(3) NOT NULL DEFAULT NOW(3) ON UPDATE NOW(3); 时间类型(datetime)访问范围1000-01-01 00:00:00.000000 至 9999-12-31 23:59:59.999999，可以更好地存储每个时间段的数据。

时间戳类型使用建议

1当只涉及数据的最后更新时间时，建议定义时间戳列TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP；

2如果您关心创建时间和更新时间，建议将更新时间设置为时间戳字段并定义创建时间DAETIME 或 TIMESTAMP DEFAULT 0000-00-00 00:00:00并明确指定插入记录时的创建时间；

3建议在表中只定义一个时间戳列，并显式定义DEFAULT 和 ON UPDATE属性；

4、虽然在MySQL可以在中分配或更新时间戳字段，但建议仅在必要时显式插入和更新时间戳列；

5、建议将time_zone参数已设置system如果设置了中国地区服务器+8:00；

6、建议将MySQL离线测试版本与在线生产版本一致。