sqlupdatedate类型_MySQL中“诡异”的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是否跟时区相关是否有默认⾏为
int4-21474836482147483647否否
timestamp41970-01-01 00:00:01(UTC)2038-01-19 03:14:07(UTC)是是
datetime81000-01-01 00:00:009999-12-31 23:59:59否否
注意：从5.6.4版本开始，TIME，TIMESTAMP，DATTIME这三种类型增加了对⼩数秒的⽀持，timestamp存储⼤⼩为4Bytes+⼩数部分；datetime存储⼤⼩为5Bytes+⼩数部分。
详见：
Date and Time Data Type Representation(sql/doc/internals/en/date-and-time-data-
type-representation.html)
#验证了TIMESTAMP类型采⽤INT存储，具体差8⼩时原因，请查看1：时区部分
mysql> SELECT FROM_UNIXTIME(0);
+---------------------+
sql中update什么意思
| 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  案例篇
默认⾏为规则+使⽤规则+特殊限制放在⼀起晕了没？我们少废话，上案例
案例⼀： 验证了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)
案例⼆： 验证了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)
案例三： 验证了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
案例四： 验证了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)

版权声明：本站内容均来自互联网，仅供演示用，请勿用于商业和其他非法用途。如果侵犯了您的权益请与我们联系QQ:729038198，我们将在24小时内删除。