在 MySQL 中，由于 TIMESTAMP 类型占用的空间为 4 个字节，理论上其能够存储最大的日期为 “2038-01-19 03:14:07”，而在 MySQL 5.6 之后占用的内存空间为 7 个字节，可以精确到毫秒、微秒，但是这个最大日期并没有被改变。所以，当我们存储时间大于 “2038-01-19 03:14:07” 时系统就会报错。
这个限制在 MySQL 官方 11.2.2 The DATE, DATETIME, and TIMESTAMP Types[1] 也有描述：
|
1 |
The TIMESTAMP data type is used for values that contain both date and time parts. TIMESTAMP has a range of '1970-01-01 00:00:01' UTC to '2038-01-19 03:14:07' UTC. |
explicit_defaults_for_timestamp
官方文档中关于 explicit_defaults_for_timestamp 参数说明如下：explicit_defaults_for_timestamp
| Deprecated | 5.6.6 | ||
| Command-Line Format | --explicit_defaults_for_timestamp=# |
||
| System Variable | Name | explicit_defaults_for_timestamp | |
| Variable Scope | Global, Session | ||
| Dynamic Variable | Yes | ||
| Permitted Values | Type | boolean | |
| Default | FALSE | ||
在 MySQL 5.7 版本之前，且在 MySQL 5.6.6 版本之后（explicit_defaults_for_timestamp 参数在 MySQL 5.6.6 开始加入）的版本中，如果没有开启 explicit_defaults_for_timestamp 参数的情况下：
- 在默认情况下，如果 TIMESTAMP 列没有显示的指明 null 属性，那么该列会被自动加上 not null 属性（而其他类型的列如果没有被显示的指定 not null，那么是允许 null 值的），如果往这个列中插入 null 值，会自动的设置该列的值为 current_timestamp 值。
- 表中的第一个 TIMESTAMP 列，如果没有指定 null 属性或者没有指定默认值，也没有指定 ON UPDATE 语句。那么该列会自动被加上DEFAULT CURRENT_TIMESTAMP 和 ON UPDATE CURRENT_TIMESTAMP 属性。
- 第一个 TIMESTAMP 列之后的其他的 TIMESTAMP 类型的列，如果没有指定 null 属性，也没有指定默认值，那么该列会被自动加上 DEFAULT ‘0000-00-00 00:00:00’ 属性。如果 insert 语句中没有为该列指定值，那么该列中插入 ‘0000-00-00 00:00:00’，并且没有 warning。
在 MySQL 5.6.6 及以后的版本和 MySQL 5.7 之前的版本中，如果在配置文件中没有指定 explicit_defaults_for_timestamp 参数，启动时 error 日志中会报如下警告：
|
1 2 3 |
[Warning] TIMESTAMP with implicit DEFAULT value is deprecated. Please use --explicit_defaults_for_timestamp server option (see documentation for more details). |
如果我们在启动的时候在配置文件中指定了 explicit_defaults_for_timestamp=1，MySQL 会按照如下的方式处理 TIMESTAMP 列：
- 此时如果 TIMESTAMP 列没有显示的指定 not null 属性，那么默认的该列可以为 null，此时向该列中插入 null 值时，会直接记录 null，而不是 current_timestamp。
- 不会自动的为表中的第一个 TIMESTAMP 列加上 DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP 属性，除非你在建表的时候显示的指明。
- 如果 TIMESTAMP 列被加上了 not null 属性，并且没有指定默认值。这时如果向表中插入记录，但是没有给该 TIMESTAMP 列指定值的时候，如果 strict sql_mode 被指定了，那么会直接报错。如果 strict sql_mode 没有被指定，那么会向该列中插入 ‘0000-00-00 00:00:00’ 并且产生一个 warning。
这里为什么一直强调版本呢？主要还是因为这个参数 explicit_defaults_for_timestamp 在 MySQL 5.6.6 开始加入，并且 MySQL 5.6 跟 MySQL 5.7 的默认 SQL 模式不同了。MySQL 5.7 的 SQL 模式更加严格了，限制了不合法的日期输入，比如 “0000-00-00 00:00:00″。详情可以看：MySQL 5.7默认SQL模式带来的问题总结。
设置 explicit_defaults_for_timestamp=0
1）创建测试表test_time
|
1 2 3 4 5 |
mysql> set session sql_mode=''; Query OK, 0 rows affected, 1 warning (0.00 sec) mysql> create table test_time(time1 timestamp,time2 timestamp,id int); Query OK, 0 rows affected (0.01 sec) |
这里关闭了 SQL 模式，是因为 MySQL 5.7 默认 SQL 模式加入了 NO_ZERO_DATE 和 NO_ZERO_IN_DATE 模式，这两个模式的意思如下：
- NO_ZERO_DATE
在严格模式，不要将 ‘0000-00-00’ 做为合法日期。你仍然可以用 IGNORE 选项插入零日期。在非严格模式，可以接受该日期，但会生成警告。
- NO_ZERO_IN_DATE
在严格模式，不接受月或日部分为 0 的日期（也就是说比 NO_ZERO_DATE），对年不限制。如果使用 IGNORE 选项，我们为类似的日期插入 ‘0000-00-00’。在非严格模式，可以接受该日期，但会生成警告。
所以如果不去掉这两个 SQL 模式，那么根据我们上面所说的表中的第一个 TIMESTAMP 列，如果没有指定 null 属性或者没有指定默认值，也没有指定 ON UPDATE 语句。那么该列会自动被加上 DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP 属性。第一个 TIMESTAMP 列之后的其他的 TIMESTAMP 类型的列，如果没有指定 null 属性，也没有指定默认值，那么该列会被自动加上 DEFAULT ‘0000-00-00 00:00:00’ 属性。所以第二个 timestamp 添加默认值时就会报错的，错误如下：
|
1 2 |
mysql> create table test_time1(time1 timestamp,time2 timestamp,id int); ERROR 1067 (42000): Invalid default value for 'time2' |
表创建好了之后，下面来查看表结构信息，如下：
|
1 2 3 4 5 6 7 8 9 |
mysql> show create table test_time\G *************************** 1. row *************************** Table: test_time Create Table: CREATE TABLE `test_time` ( `time1` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP, `time2` timestamp NOT NULL DEFAULT '0000-00-00 00:00:00', `id` int(11) DEFAULT NULL ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 1 row in set (0.00 sec) |
从表结构中可以看到表中 timestamp 列被自动设置为 not null，并且表中第一个 timestamp 列被设置了 DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP 默认值，第二个字段默认值为 “0000-00-00 00:00:00″。
2）插入测试
|
1 2 3 4 5 6 7 8 9 10 11 |
mysql> insert into test_time select null,null,1; Query OK, 1 row affected (0.00 sec) Records: 1 Duplicates: 0 Warnings: 0 mysql> select * from test_time; +---------------------+---------------------+------+ | time1 | time2 | id | +---------------------+---------------------+------+ | 2017-02-22 15:42:11 | 2017-02-22 15:42:11 | 1 | +---------------------+---------------------+------+ 1 row in set (0.00 sec) |
往 timestamp 列插入 null 值时，会自动为该列设置为 current time。
插入时未指定值的 timestamp 列中被插入了 0000-00-00 00:00:00（非表中第一个 timestamp 列）。
|
1 2 3 4 5 6 7 8 |
mysql> select * from test_time; +---------------------+---------------------+------+ | time1 | time2 | id | +---------------------+---------------------+------+ | 2017-02-22 15:42:11 | 2017-02-22 15:42:11 | 1 | | 2017-02-22 15:45:09 | 0000-00-00 00:00:00 | 1 | +---------------------+---------------------+------+ 2 rows in set (0.00 sec) |
设置 explicit_defaults_for_timestamp=1
1）创建表（不需要改变 sql_mode）
|
1 2 3 4 5 6 7 8 9 10 11 12 |
mysql> create table test_time(time1 timestamp,time2 timestamp,id int); Query OK, 0 rows affected (0.00 sec) mysql> show create table test_time\G *************************** 1. row *************************** Table: test_time Create Table: CREATE TABLE `test_time1` ( `time1` timestamp NULL DEFAULT NULL, `time2` timestamp NULL DEFAULT NULL, `id` int(11) DEFAULT NULL ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 1 row in set (0.00 sec) |
通过表结构我们看到，2 个 timestamp 列都被设置为 null，并且设置了默认值为 null。
2）插入测试
|
1 2 3 4 5 6 7 8 9 10 11 |
mysql> insert into test_time select null,1; Query OK, 1 row affected (0.00 sec) Records: 1 Duplicates: 0 Warnings: 0 mysql> select * from test_time; +-------+-------+------+ | time1 | time2 | id | +-------+-------+------+ | NULL | NULL | 1 | +-------+-------+------+ 1 row in set (0.00 sec) |
为 timestamp 列指定了 not null 属性，在 strict SQL mode 时，如果插入时该列没有指定值，会直接报错。
|
1 2 3 4 5 |
mysql> create table test_time(time1 timestamp,time2 timestamp not null,id int); Query OK, 0 rows affected (0.01 sec) mysql> insert into test_time select null,null,1; ERROR 1048 (23000): Column 'time2' cannot be null |
如果为 timestamp 列指定 not null 属性，在非 stric sql_mode 模式下，如果插入的时候该列没有指定值，那么会向该列中插入 0000-00-00 00:00:00，并且产生告警。
|
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 |
mysql> set session sql_mode=''; Query OK, 0 rows affected, 1 warning (0.00 sec) mysql> insert into test_time select null,null,1; Query OK, 1 row affected, 1 warning (0.00 sec) Records: 1 Duplicates: 0 Warnings: 1 mysql> show warnings; +---------+------+-------------------------------+ | Level | Code | Message | +---------+------+-------------------------------+ | Warning | 1048 | Column 'time2' cannot be null | +---------+------+-------------------------------+ 1 row in set (0.00 sec) mysql> select * from test_time; +-------+---------------------+------+ | time1 | time2 | id | +-------+---------------------+------+ | NULL | 0000-00-00 00:00:00 | 1 | +-------+---------------------+------+ 1 row in set (0.00 sec) |
上面说的都是关于 timestamp 类型的，如果是 datetime 呢？
|
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 |
mysql> create table time1(id int,time1 datetime,time2 datetime); Query OK, 0 rows affected (0.03 sec) mysql> show create table time1\G *************************** 1. row *************************** Table: time1 Create Table: CREATE TABLE `time1` ( `id` int(11) DEFAULT NULL, `time1` datetime DEFAULT NULL, `time2` datetime DEFAULT NULL ) ENGINE=InnoDB DEFAULT CHARSET=utf8 1 row in set (0.00 sec) mysql> insert into time1(id) values(1); Query OK, 1 row affected (0.00 sec) mysql> select * from time1; +------+-------+-------+ | id | time1 | time2 | +------+-------+-------+ | 1 | NULL | NULL | +------+-------+-------+ 1 row in set (0.00 sec) |
可以看到跟普通类型一样。
