mysql timestamp很奇怪_MySQL中“诡异”的TIMESTAMP数据类型

前言

目录

1： TIMESTAMP受影响因素之一——MySQL时区简介

2：TIMESTAMP受影响因素之二——SQL_MODE简介

3：TIMESTAMP类型和DATETIME类型的比较

4：TIMESTAMP类型的默认行为

5：TIMESTAMP类型常见的线上问题

6：线上推荐使用的有关TIMESTAMP类型建表方式

前言

涉及MySQL的日常开发与运维过程中少不了和TIMESTAMP数据类型打交道，有时候TIMESTAMP类型看起来莫名其妙，测试环境都测试OK了上了预发就直接报错了；有时候TIMESTAMP类型看起来又很诡异，表中的真实数据跟开发人员提交的表结构又南辕北辙并非所需要的；本篇文章将抽丝剥茧带你重新认识一下这个熟悉又陌生的TIMESTAMP类型。

1： TIMESTAMP影响因素之一—— MySQL时区简介

1.1 GMT、UTC、CST简介

GMT：格林尼治标准时间(Greenwich Mean Time)，简称GMT。是指位于英国伦敦郊区的皇家格林尼治天文台的标准时间，格林尼治的经线被定义为本初子午线，作为计算地理的起点和世界标准"时区"的起点，即：零时区的时间。

UTC：协调世界时(Universal Time Coordinated)，简称UTC，又称世界标准时间。取代GMT的新计量标准。

CST：北京时间(China Standard Time)，简称CST，又称中国标准时间，即：东八区的时间。由于在时区划分上，比协调世界时早了8个小时，固记为UTC+8

1.2 GMT和UTC的关系

GMT是之前的时间计量标准，是根据 地球自转和公转来计算时间，也就是太阳每天经过格林尼治天文台的时间就是中午12点，误差较大。

UTC是现用的时间计量标准，是根据 原子钟来计算时间，误差较小。

现在世界上最精确的原子钟50亿年会产生误差1秒，可以说非常精确。而GMT因为是根据地球的转动来计算时间的，而地球的自转正在缓速变慢，所以使用GMT的话，总会有一天(可能很久很久)，中午12点，并不是一天太阳当头照的时候，很可能就是早上或者晚上了。所以说UTC更加精确。当前看来两者是约等于关系。

1.3 CST和GMT、UTC之间的关系

根据不同时区可以看到几者之间的关系是：

CST=UTC+8小时

CST=GMT+8小时

UTC ≈GMT

1.4 举个栗子，说说清楚：

时区的存在是因为地球上不同地方的人看到太阳升起的时间是不一样的。假定北京人民在早上8:00看到了太阳刚刚升起，而此刻欧洲人民(假设东1区)还在夜里，他们还需要再过7个小时才能看到太阳升起，所以此刻欧洲人民的手表上显示的是凌晨1:00，如果你强迫他们用北京时间那他们每天看到日出的时间就是15点。

即：东8区的北京人民的手表显示的8:00和东1区欧洲人民手表显示的1:00是相同的时刻： "2017-06-29 08:00:00 +8:00" = "2017-06-29 01:00:00 +1:00"

2： TIMESTAMP影响因素之二—— SQL_MODE简介

SQL Mode定义了MySQL支持的SQL语法以及应该在数据上执行何种验证检查，常见的SQL_MODE归类如下：

2.1：SQL语法支持类

ANSI：等同于REAL_AS_FLOAT,PIPES_AS_CONCAT,ANSI_QUOTES,IGNORE_SPACE

IGNORE_SPACE：允许函数名和'(' 之间有空格

REAL_AS_FLOAT：将REAL视为FLOAT的同义词，而不是DOUBLE的同义词

PIPES_AS_CONCAT：将 '||' 视为字符串的连接操作符而非 '或' 运算符

ONLY_FULL_GROUP_BY：查询字段必须出现在聚合操作的字段中

NO_AUTO_CREATE_USER：不自动创建用户，即授权时候必须先创建用户之后才可以授权

NO_TABLE_OPTIONS：SHOW CREATE TABLE 时不会输出MySQL特有的语法部分,如ENGINE信息

ANSI_QUOTES：双引号是识别符作用同反引号：SHOW CREATE TABLE时可以看到，UPDATE tab SET col1="a",此时的"a"会当做一个字段名，而不是某个值

2.2：验证检查类

NO_ZERO_DATE：认为日期 '0000-00-00' 非法，跟是否设置后面的严格模式有关。( 见案例七)

NO_ZERO_DATE且在严格模式下NO_ZERO_DATE自然满足，INSERT和UPDATE全0不再被允许;INSERT IGNORE和UPDATE IGNORE全0被允许但是报warning

NO_ZERO_DATE且在非严格模式下INSERT和UPDATE和INSERT IGNORE和UPDATE IGNORE全0被允许但是报warning

NO_ZERO_IN_DATE：同NO_ZERO_DATE类似， 在年份非0的前提下，月和日是否允许为0，即2010-01-00或者2010-00-01是否合法

NO_ENGINE_SUBSTITUTION：在ALTER TABLE或CREATE TABLE时候，指定的ENGINE如果被禁用或未编译直接抛错

STRICT_TRANS_TABLES：严格模式，严格检查在INSERT、UPDATE等操作出现少值或无效值该如何处理

线上常用的SQL_MODE有ANSI、STRICT_TRANS_TABLES，推荐使用严格模式

3：TIMESTAMP类型和DATETIME类型的比较

Type Storage(Bytes) Minimum Value Maximum Value 是否跟时区相关 是否有默认行为 int 4 -2147483648 2147483647 否 否 timestamp 4 1970-01-01 00:00:01(UTC) 2038-01-19 03:14:07(UTC) 是 是 datetime 8 1000-01-01 00:00:00 9999-12-31 23:59:59 否 否

注意：从5.6.4版本开始，TIME，TIMESTAMP，DATTIME这三种类型增加了对小数秒的支持，timestamp存储大小为4Bytes+小数部分；datetime存储大小为5Bytes+小数部分。详见：

#验证了TIMESTAMP类型采用INT存储，具体差8小时原因，请查看1：时区部分

mysql> SELECT FROM_UNIXTIME(0);

+---------------------+

|FROM_UNIXTIME(0) |

+---------------------+

| 1970-01-01 08:00:00 |

+---------------------+

1 row in set (0.00 sec)

mysql> SELECT FROM_UNIXTIME(2147483647);

+---------------------------+

| FROM_UNIXTIME(2147483647) |

+---------------------------+

| 2038-01-19 11:14:07 |

+---------------------------+

1 row in set (0.00 sec)

相同点：

a、两者都可以表示时间精确到秒且显示格式都为：YYYY-MM-DD HH:MM:SS

不同点：

a、TIMESTAMP长度4字节而DATETIME长度8字节，比较节省存储空间

b、TIMESTAMP类型采用INT存储，排序效率更高，查询速度更快，方便计算

c、TIMESTAMP有效范围是1970-2038，而DATETIME有效范围是1000-9999

d、TIMESTAMP类型有默认行为，而DATETIME则没有

e、TIMESTAMP类型会受时区的影响，而DATETIME则不会( 见案例六)

f、TIMESTAMP类型会受SQL_MODE的影响，而DATETIME则不会

4：TIMESTAMP类型的默认行为

4.1：默认行为规则如下：

a、如果TIMESTAMP列没有明确声明NULL属性，则默认是NOT NULL(如果是其他的数据类型，如果没有明确声明NULL属性则默认为NULL)

b、表中的第一个TIMESTAMP字段，如果没有明确声明NULL、DEFAULT、ON UPDATE会自动分配DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP属性( 见案例一)

c、表中的第二个及以后TIMESTAMP字段，如果没有明确声明NULL、DEFAULT会默认分配'0000-00-00 00:00:00'属性( 见案例二)

4.2：使用规则如下：

a、在INSERT或者UPDATE语句中设置了TIMESTAMP字段为NULL时，若该字段允许为NULL，则结果为NULL；若该字段不允许为NULL，则结果为当前的时间戳， 跟DEFAULT没有关系( 见案例四)

b、在INSERT时若缺省(即INSERT语句不指定该列的值)，则结果为默认值，具体值由DEFAULT决定( 见案例五)

c、若有一个字段属性是ON UPDATE CURRENT_TIMESTAMP，则修改该行的任何字段都会更新此TIMESTAMP字段为当前时间戳。

4.3：默认行为的特殊限制

a、5.1和5.5版本每张表只能有一个DEFAULT CURRENT_TIMESTAMP类型的TIMESTAMP字段，如果多了会报错：there can be only one TIMESTAMP column with CURRENT_TIMESTAMP in DEFAULT or ON UPDATE clause( 见案例三)

b、5.6和5.7则没有这个限制：每张表可以由多个DEFAULT CURRENT_TIMESTAMP类型的TIMESTAMP字段

c、参数explicit_defaults_for_timestamp(释义:显示指定TIMESTAMP类型)可以控制TIMESTAMP类型的默认行为，默认是OFF，即使用默认行为，不手动显示指定

4.4：案例篇

默认行为规则+使用规则+特殊限制放在一起晕了没？我们少废话，上案例

a、案例一： 验证了4.1-b的默认行为

mysql> #执行SQL

mysql> CREATE TABLE tab(

-> id BIGINT unsigned NOT NULL PRIMARY KEY AUTO_INCREMENT,

-> db_create_time TIMESTAMP

-> )ENGINE=INNODB DEFAULT CHARSET=UTF8;

Query OK, 0 rows affected (0.01 sec)

mysql> #SHOW CREATE TABLE查看

mysql> #5.5版本 & 5.6版本 & 5.7版本

mysql> SHOW CREATE TABLE tab\G

*************************** 1. row ***************************

Table: tab

Create Table: CREATE TABLE`tab` (

`id` bigint(20) unsigned NOT NULL AUTO_INCREMENT,

`db_create_time` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,

`db_update_time` timestamp NOT NULL DEFAULT '0000-00-00 00:00:00',

PRIMARY KEY (`id`)

) ENGINE=InnoDB DEFAULT CHARSET=utf8

1 row in set (0.00 sec)

b、案例二： 验证了4.1-bc的默认行为

mysql> #执行SQL

mysql> CREATE TABLE tab1(

-> id BIGINT unsigned NOT NULL PRIMARY KEY AUTO_INCREMENT,

-> db_create_time TIMESTAMP,

-> db_update_time TIMESTAMP

-> )ENGINE=INNODB DEFAULT CHARSET=UTF8;

Query OK, 0 rows affected (0.01 sec)

mysql> #SHOW CREATE TABLE查看

mysql> #5.5版本 & 5.6版本 & 5.7版本

mysql> SHOW CREATE TABLE tab1 \G

*************************** 1. row ***************************

Table: tab1 Create

Table: CREATE TABLE `tab1` (

`id` bigint(20) unsigned NOT NULL AUTO_INCREMENT,

`db_create_time` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,

`db_update_time` timestamp NOT NULL DEFAULT '0000-00-00 00:00:00',

PRIMARY KEY (`id`)

) ENGINE=InnoDB DEFAULT CHARSET=utf8

1 row in set (0.00 sec)

c、案例三： 验证了4.3-a的特殊限制

mysql> #执行SQL

mysql> CREATE TABLE tab2(

-> id BIGINT unsigned NOT NULL PRIMARY KEY AUTO_INCREMENT,

-> db_create_time TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,

-> db_update_time TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP

-> )ENGINE=INNODB DEFAULT CHARSET=UTF8;

Query OK, 0 rows affected (0.01 sec)

mysql> #SHOW CREATE TABLE查看

mysql> #5.6版本 & 5.7版本

mysql> SHOW CREATE TABLE tab2 \G

*************************** 1. row ***************************

Table: tab2 Create Table: CREATE TABLE `tab2` (

`id` bigint(20) unsigned NOT NULL AUTO_INCREMENT,

`db_create_time` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,

`db_update_time` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,

PRIMARY KEY (`id`)

) ENGINE=InnoDB DEFAULT CHARSET=utf8

1 row in set (0.00 sec)

#5.5版本报错了(MySQL5.5版本不支持多个DEFAULT CURRENT_TIMESTAMP属性的字段)

ERROR 1293 (HY000): Incorrect table definition;

there can be only one TIMESTAMP column with CURRENT_TIMESTAMP in DEFAULT or ON UPDATE clause

d、案例四： 验证了4.2-a的使用规则

#5.5版本 & 5.6版本 & 5.7版本

mysql> #执行SQL

mysql> CREATE TABLE tab3(

-> id BIGINT unsigned NOT NULL PRIMARY KEY AUTO_INCREMENT,

-> db_create_time TIMESTAMP NOT NULL DEFAULT '2000-01-01 00:00:00'

-> )ENGINE=INNODB DEFAULT CHARSET=UTF8;

Query OK, 0 rows affected (0.00 sec)

mysql> SHOW CREATE TABLE tab3 \G

*************************** 1. row ***************************

Table: tab3 Create Table: CREATE TABLE `tab3` (

`id` bigint(20) unsigned NOT NULL AUTO_INCREMENT,

`db_create_time` timestamp NOT NULL DEFAULT '2000-01-01 00:00:00',

PRIMARY KEY (`id`)

) ENGINE=InnoDB DEFAULT CHARSET=utf8

1 row in set (0.01 sec)

mysql> INSERT INTO tab3 VALUES(1,NULL);

Query OK, 1 row affected (0.00 sec)

mysql> SELECT * FROM tab3;

+----+---------------------+

| id | db_create_time |

+----+---------------------+

| 1 | 2017-06-29 22:46:53 |

+----+---------------------+

1 row in set (0.01 sec)

e、案例五： 验证了4.2-b的使用规则

#5.5版本 & 5.6版本 & 5.7版本

mysql> #执行SQL mysql> CREATE TABLE tab4(

-> id BIGINT unsigned NOT NULL PRIMARY KEY AUTO_INCREMENT,

-> db_create_time TIMESTAMP NOT NULL DEFAULT '2000-01-01 00:00:00'

-> )ENGINE=INNODB DEFAULT CHARSET=UTF8;

Query OK, 0 rows affected (0.01 sec)

mysql> SHOW CREATE TABLE tab4 \G

*************************** 1. row ***************************

Table: tab4

Create Table: CREATE TABLE `tab4` (

`id` bigint(20) unsigned NOT NULL AUTO_INCREMENT,

`db_create_time` timestamp NOT NULL DEFAULT '2000-01-01 00:00:00',

PRIMARY KEY (`id`)

) ENGINE=InnoDB DEFAULT CHARSET=utf8

1 row in set (0.00 sec)

mysql> INSERT INTO tab4(id) VALUES(1);

Query OK, 1 row affected (0.01 sec)

mysql> SELECT * FROM tab4;

+----+---------------------+

| id | db_create_time |

+----+---------------------+

| 1 | 2000-01-01 00:00:00 |

+----+---------------------+

1 row in set (0.00 sec)

f、案例六：(见TIMESTAMP类型和DATETIME类型)

#MySQL所有版本

mysql> #执行SQL

mysql> SHOW CREATE TABLE tab5 \G

*************************** 1. row ***************************

Table: tab5 Create Table: CREATE TABLE `tab5` (

`id` bigint(20) unsigned NOT NULL AUTO_INCREMENT,

`start` datetime NOT NULL,

`db_create_time` timestamp NOT NULL DEFAULT '2000-01-01 00:00:00',

PRIMARY KEY (`id`)

) ENGINE=InnoDB DEFAULT CHARSET=utf8

1 row in set (0.00 sec)

mysql> INSERT INTO tab5 VALUES(1,'2017-06-30 00:00:00','2017-06-30 00:00:00');

Query OK, 1 row affected (0.00 sec)

mysql> SELECT * FROM tab5;

+----+---------------------+---------------------+

| id | start | db_create_time |

+----+---------------------+---------------------+

| 1 | 2017-06-30 00:00:00 | 2017-06-30 00:00:00 |

+----+---------------------+---------------------+

1 row in set (0.00 sec)mysql> SHOW GLOBAL VARIABLES LIKE '%sql_mode%';

+---------------+------------------------+

| Variable_name | Value |

+---------------+------------------------+

| sql_mode | NO_ENGINE_SUBSTITUTION |

+---------------+------------------------+

1 row in set (0.00 sec)

#MySQL此时的时区默认是系统时区CST，即：东8区时间(有关时区详情见第一部分：时区简介)

mysql> SHOW GLOBAL VARIABLES LIKE '%time_zone%';

+------------------+--------+

| Variable_name | Value |

+------------------+--------+

| system_time_zone | CST |

| time_zone | SYSTEM |

+------------------+--------+

2 rows in set (0.01 sec)

#将MySQL时区修改为东1区

mysql> SET GLOBAL time_zone='+1:00';

Query OK, 0 rows affected (0.00 sec)

#新开SESSION，查看有关datetime类型、timestamp类型受时区的影响情况

mysql> \q

Bye

#新SESSION

mysql> USE t1

Reading table information for completion of table and column names

You can turn off this feature to get a quicker startup with -A

#由于"东8区本地时间" = "东1区本地时间+7:00"，所以timestamp类型的数据整体减少了7个小时

Database changed

mysql> SELECT * FROM tab5;

+----+---------------------+---------------------+

| id | start | db_create_time |

+----+---------------------+---------------------+

| 1 | 2017-06-30 00:00:00 | 2017-06-29 17:00:00 |

+----+---------------------+---------------------+

1 row in set (0.00 sec)

#引入SQL_MODE='NO_ZERO_DATE, NO_ZERO_IN_DATE'

g、案例七：

#MySQL5.5版本

mysql> SET SESSION SQL_MODE='NO_ZERO_DATE,NO_ZERO_IN_DATE';

Query OK, 0 rows affected (0.00 sec)

#MySQL5.6版本

mysql> SET SESSION SQL_MODE='NO_ZERO_DATE,NO_ZERO_IN_DATE';

Query OK, 0 rows affected, 2 warnings (0.00 sec)

mysql> show warnings;

+---------+------+--------------------------------------------------------------------------+

| Level | Code | Message |

+---------+------+--------------------------------------------------------------------------+

| Warning | 1681 | 'NO_ZERO_DATE' is deprecated and will be removed in a future release. |

| Warning | 1681 | 'NO_ZERO_IN_DATE' is deprecated and will be removed in a future release. |

+---------+------+--------------------------------------------------------------------------+

2 rows in set (0.00 sec)

#MySQL5.7版本

mysql> SET SESSION SQL_MODE='NO_ZERO_DATE,NO_ZERO_IN_DATE';

Query OK, 0 rows affected, 1 warning (0.00 sec)

mysql> show warnings;

+---------+------+-------------------------------------------------------------------------------------------------------------------------------------------------------------------------+

| Level | Code | Message |

+---------+------+-------------------------------------------------------------------------------------------------------------------------------------------------------------------------+

| Warning | 3135 | 'NO_ZERO_DATE', 'NO_ZERO_IN_DATE' and 'ERROR_FOR_DIVISION_BY_ZERO' sql modes should be used with strict mode. They will be merged with strict mode in a future release. |

+---------+------+-------------------------------------------------------------------------------------------------------------------------------------------------------------------------+

1 row in set (0.00 sec)

#MySQL5.5 & 5.6 & 5.7 版本

mysql> CREATE TABLE tab6(

-> id BIGINT unsigned NOT NULL PRIMARY KEY AUTO_INCREMENT,

-> `db_create_time` timestamp NOT NULL ,

-> `db_update_time` timestamp NOT NULL

-> )ENGINE=INNODB DEFAULT CHARSET=UTF8;

ERROR 1067 (42000): Invalid default value for 'db_update_time'

本文来自网易云社区，经作者葛崇授权发布。

了解 网易云 ：

更多网易研发、产品、运营经验分享请访问网易云社区。