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

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

Date and Time Data Type Representation

#验证了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,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)

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