InnoDB参数之explicit_defaults_for_timestamp

explicit_defaults_for_timestamp

    1)全局\会话级、动态变量，默认值 OFF，取值范围：OFF/ON
    2)当explicit_defaults_for_timestamp为OFF时：
        1)表里的第一个timestamp列，如果没有明确指定null、DEFAULT 、ON UPDATE属性的话，Mysql默认会赋予该列DEFAULT CURRENT_TIMESTAMP和ON UPDATE CURRENT_TIMESTAMP属性
        2)不是表里的第一个timestamp列，如果没有明确指定null、DEFAULT 属性的话，Mysql默认会赋予该列 DEFAULT '0000-00-00 00:00:00'(如果sql_mode里有STRICT_ALL_TABLES或者STRICT_TRANS_TABLES或者NO_ZERO_DATE的话，该列在创建时会报错)
        3)The nonstandard behaviors just described are deprecated and will be removed in a future MySQL release.

mysql> show variables like '%explicit%';
+---------------------------------+-------+
| Variable_name                   | Value |
+---------------------------------+-------+
| explicit_defaults_for_timestamp | OFF   |
+---------------------------------+-------+
1 row in set (0.01 sec)

mysql> show variables like '%sql_mode%';
+---------------+------------------------------------------------------------------------------------------------------------------------+
| Variable_name | Value                                                                                                                  |
+---------------+------------------------------------------------------------------------------------------------------------------------+
| sql_mode      | STRICT_TRANS_TABLES,NO_ZERO_IN_DATE,NO_ZERO_DATE,ERROR_FOR_DIVISION_BY_ZERO,NO_AUTO_CREATE_USER,NO_ENGINE_SUBSTITUTION |
+---------------+------------------------------------------------------------------------------------------------------------------------+
1 row in set (0.00 sec)

mysql> create table test0705(a timestamp);
Query OK, 0 rows affected (0.01 sec)

mysql> show create table test0705\G
*************************** 1. row ***************************
       Table: test0705
Create Table: CREATE TABLE `test0705` (
  `a` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP
) ENGINE=InnoDB DEFAULT CHARSET=utf8
1 row in set (0.00 sec)

mysql> alter table test0705 add b timestamp;
ERROR 1067 (42000): Invalid default value for 'b'
mysql> alter table test0705 add b timestamp null;
Query OK, 0 rows affected (0.02 sec)
Records: 0  Duplicates: 0  Warnings: 0

     3)当explicit_defaults_for_timestamp为ON时：
        1)timestamp列，如果不明确指定not null的话，默认是null，也允许null，不会有null转换成CURRENT_TIMESTAMP的情况
        2)timestamp列指定为not null但是没有指定default value的话，
如果sql_mode里有STRICT_ALL_TABLES或者STRICT_TRANS_TABLES或者NO_ZERO_DATE的话，插入和更新会报错，
如果sql_mode里没有STRICT_ALL_TABLES或者STRICT_TRANS_TABLES或者NO_ZERO_DATE的话，not null但是没有指定default value的列会插入'0000-00-00 00:00:00'

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

mysql> create table test0706(a timestamp,b timestamp);
Query OK, 0 rows affected (0.02 sec)

mysql> show create table test0706\G
*************************** 1. row ***************************
       Table: test0706
Create Table: CREATE TABLE `test0706` (
  `a` timestamp NULL DEFAULT NULL,
  `b` timestamp NULL DEFAULT NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8
1 row in set (0.00 sec)

mysql> alter table test0706 add c timestamp not null;
Query OK, 0 rows affected (0.02 sec)
Records: 0  Duplicates: 0  Warnings: 0

mysql> show create table test0706\G
*************************** 1. row ***************************
       Table: test0706
Create Table: CREATE TABLE `test0706` (
  `a` timestamp NULL DEFAULT NULL,
  `b` timestamp NULL DEFAULT NULL,
  `c` timestamp NOT NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8
1 row in set (0.00 sec)

mysql> insert into test0706(a,b) values (null,null);
ERROR 1364 (HY000): Field 'c' doesn't have a default value

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

mysql> insert into test0706(a,b) values (null,null);
Query OK, 1 row affected, 1 warning (0.00 sec)

mysql> select * from test0706;
+------+------+---------------------+
| a    | b    | c                   |
+------+------+---------------------+
| NULL | NULL | 0000-00-00 00:00:00 |
+------+------+---------------------+