在时间类型中，用的较多的是TIMESTAMP和DATETIME。

看到很多开发规范，推荐使用TIMESTAMP而不是DATETIME，原因在于TIMESTAMP占4个字节，而DATETIME需要8个字节，前者比后者省空间。

这个说法没错，但也仅适用于MySQL 5.6之前的版本。而且，仅着眼于存储空间，实际在淡化这两者的差别。而知其然，知其所以然，才能找到更合适的表达方式。

下面，具体来看看TIMESTAMP和DATETIME的相同点及不同点。

相同点

两者都可用来表示YYYY-MM-DD HH:MM:SS[.fraction]类型的日期。

不同点

不同点主要体现在以下三方面：

时间范围

TIMESTAMP所能表示的时间范围为：'1970-01-01 00:00:01.000000' 到 '2038-01-19 03:14:07.999999'，

注意，该范围是UTC时间，如果换算为中国标准时间，其能表示的时间范围为：'1970-01-01 08:00:01.000000' 到 '2038-01-19 11:14:07.999999'。

DATETIME所能表示的时间范围为：'1000-01-01 00:00:00.000000' 到 '9999-12-31 23:59:59.999999'。

存储空间

在MySQL 5.6.4之前，TIMESTAMP和DATETIME是不支持小数秒的。MySQL 5.6.4对其进行了重构，相应的存储需求也发生了变化。不考虑小数秒的话，DATETIME只比TIMESTAMP多占用一个字节。

存储方式

TIMESTAMP，在插入时，它会将客户端输入的时间转化为UTC时间进行存储，在查询时，又会将存储的UTC时间转化为客户端当前时区时间进行返回。

而对于DATETIME，不做任何改变，基本上是原样输入和输出。

下面来验证下。


mysql> create table t (c1 timestamp,c2 datetime);
Query OK, 0 rows affected (0.08 sec)




mysql> insert into t values ('2018-08-13 14:43:12','2018-08-13 14:43:12');
Query OK, 1 row affected (0.06 sec)




mysql> select * from t;
+---------------------+---------------------+
| c1                  | c2                  |
+---------------------+---------------------+
| 2018-08-13 14:43:12 | 2018-08-13 14:43:12 |
+---------------------+---------------------+
1 row in set (0.00 sec)




mysql> show variables like '%time_zone%';
+------------------+--------+
| Variable_name    | Value  |
+------------------+--------+
| system_time_zone | CST    |
| time_zone        | SYSTEM |
+------------------+--------+
2 rows in set (0.00 sec)




mysql> set session time_zone='+0:00';
Query OK, 0 rows affected (0.00 sec)




mysql> select * from t;
+---------------------+---------------------+
| c1                  | c2                  |
+---------------------+---------------------+
| 2018-08-13 06:43:12 | 2018-08-13 14:43:12 |
+---------------------+---------------------+
1 row in set (0.00 sec)

在这里，涉及到两个参数，system_time_zone和time_zone。

其中，前者是在mysqld服务启动时决定的，没显式设置的话，会继承自主机时区。

后者指的是mysqld服务端时区，可全局修改，也可在会话级别修改，默认为SYSTEM，即使用system_time_zone的值。

可以看到，将time_zone会话级别的值调整后，c1的输出发生了变化，其滞后了8个小时，而c2的输出不变，这充分提现了两者的不同。

为什么是滞后8个小时呢？因为在插入的时候，time_zone是取自system_time_zone的值，即CST。CST同时代表了4个时区。

Central Standard Time (USA) UTC -06:00
Central Standard Time (Australia) UTC +09:30
China Standard Time UTC +08:00
Cuba Standard Time UTC -04:00

在这里，指的是China Standard Time（中国标准时间），中国标准时间在东八区，比UTC时间快8个小时。

对于time_zone，有以下几点需要注意：

1. 其不仅仅会影响TIMESTAMP字段的存储和显示，还会影响NOW()，CURTIME()，CURRENT_TIMESTAMP()，LOCALTIME()，LOCALTIMESTAMP()等函数的输出。

2. 不建议将其设置为SYSTEM，因为在处理上述函数及TIMESTAMP字段时，每调用一次或处理一行，都会调用系统底层的时区函数进行时区转换，在高并发场景下，可能会导致mutex争用。官方文档中也提到了这一点。

If set to SYSTEM, every MySQL function call that requires a timezone calculation makes a system library call to determine the current system timezone. This call may be protected by a global mutex, resulting in contention.

3. 如果要设置默认时区的话，需设置default-time-zone，而不是time_zone，如

default-time-zone='+08:00'

关于TIMESTAMP和DATETIME的自动初始化和自动更新

首先，看一个MySQL 5.7下的测试。


mysql> create table t (c1 int,c2 timestamp,c3 datetime);
Query OK, 0 rows affected (0.02 sec)




mysql> insert into t(c1,c3) values(1,now());
Query OK, 1 row affected (0.00 sec)




mysql> select * from t;
+------+---------------------+---------------------+
| c1   | c2                  | c3                  |
+------+---------------------+---------------------+
|    1 | 2018-08-14 16:19:59 | 2018-08-14 16:19:59 |
+------+---------------------+---------------------+
1 row in set (0.00 sec)




mysql> update t set c1=2,c3=now() where c1=1;
Query OK, 1 row affected (0.01 sec)
Rows matched: 1  Changed: 1  Warnings: 0




mysql> select * from t;
+------+---------------------+---------------------+
| c1   | c2                  | c3                  |
+------+---------------------+---------------------+
|    2 | 2018-08-14 16:20:21 | 2018-08-14 16:20:21 |
+------+---------------------+---------------------+
1 row in set (0.00 sec)

在插入时，c2列并没有赋值，却有了值，内容同c3列一样。

在更新时，同样没有赋值，值却发生了变化，但依旧同c3列保持一致。而c3列存储的即是系统当前时间。

再来看看该表的表结构。


mysql> show create table t\G
*************************** 1. row ***************************
       Table: t
Create Table: CREATE TABLE `t` (
  `c1` int(11) DEFAULT NULL,
  `c2` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
  `c3` datetime DEFAULT NULL
) ENGINE=InnoDB DEFAULT CHARSET=latin1
1 row in set (0.01 sec)

可以看到，c2列的定义，除了timestamp外，还新增了其它属性。其中，

DEFAULT CURRENT_TIMESTAMP子句会开启自动初始化，在执行插入操作时，如果该列没有显式设置，则会自动设置为系统当前时间。

ON UPDATE CURRENT_TIMESTAMP子句会开启自动更新，在执行更新操作时，如果该列没有显式设置，其它列的值又发生了变化，则会自动更新为系统当前时间。

之所以会隐式开启自动初始化和自动更新，与“explicit_defaults_for_timestamp”的设置有关，在MySQL 8.0之前，该参数的默认值为OFF。设置为OFF，其行为如下：

1. 对于表中第一个TIMESTAMP列，如果没有显式设置NULL，或DEFAULT，或ON UPDATE属性的话，会自动设置为NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP。

2. 对于其它TIMESTAMP列，如果没有显示指定NULL和DEFAULT属性的话，会自动设置为NOT NULL DEFAULT '0000-00-00 00:00:00'。

当然，这个与SQL_MODE有关，如果SQL_MODE中包含'NO_ZERO_DATE'，实际上是不允许将其默认值设置为'0000-00-00 00:00:00'的。


mysql> set session sql_mode='NO_ZERO_DATE';
Query OK, 0 rows affected, 2 warnings (0.00 sec)




mysql> create table t (c1 timestamp,c2 timestamp);
ERROR 1067 (42000): Invalid default value for 'c2'




mysql> set session sql_mode='';
Query OK, 0 rows affected (0.00 sec)




mysql> create table t (c1 timestamp,c2 timestamp);
Query OK, 0 rows affected (0.03 sec)




mysql> show create table t\G
*************************** 1. row ***************************
       Table: t
Create Table: CREATE TABLE `t` (
  `c1` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
  `c2` timestamp NOT NULL DEFAULT '0000-00-00 00:00:00'
) ENGINE=InnoDB DEFAULT CHARSET=latin1
1 row in set (0.00 sec)

3. 没有显式设置NULL属性的话，会自动设置为NOT NULL。对这些列插入NULL值，会转成系统当前时间。如，


mysql> create table t (c1 timestamp,c2 timestamp,c3 timestamp default '2018-08-15');
Query OK, 0 rows affected (0.02 sec)




mysql> show create table t\G
*************************** 1. row ***************************
       Table: t
Create Table: CREATE TABLE `t` (
  `c1` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
  `c2` timestamp NOT NULL DEFAULT '0000-00-00 00:00:00',
  `c3` timestamp NOT NULL DEFAULT '2018-08-15 00:00:00'
) ENGINE=InnoDB DEFAULT CHARSET=latin1
1 row in set (0.00 sec)




mysql> insert into t values(null,null,null);
Query OK, 1 row affected (0.01 sec)




mysql> select * from t;
+---------------------+---------------------+---------------------+
| c1 | c2 | c3 |
+---------------------+---------------------+---------------------+
| 2018-08-15 08:36:14 | 2018-08-15 08:36:14 | 2018-08-15 08:36:14 |
+---------------------+---------------------+---------------------+
1 row in set (0.00 sec)