explicit_defaults_for_timestamp 分析

explicit_defaults_for_timestamp = OFF是第一列的行为非常奇葩，而且会影响后面的表列。
这里分析记录下。

总结速查：

- explicit_defaults_for_timestamp = OFF时

  - 所有timestamp列的`not null / null`行为：
    - 如果没`null / not null`定义，自动加`not null`。
    - 如果有`null`定义，例如`c10 timestamp null`，会保留`timestamp NULL DEFAULT NULL`。
    - 如果有`not null`定义，保留。
  - 所有timestamp列的`默认值`行为：
    - 如果是第一列
      - 如果第一列已经有默认值了，就用已经有的。`c1 timestamp default '2010-01-01 00:00:00' `建表后 `c1 timestamp NOT NULL DEFAULT '2010-01-01 00:00:00'`
      - **（复杂）**如果第一列没有默认值，`c1 timestamp`建表后`c1 timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP`。增加了默认值CURRENT_TIMESTAMP、增加了更新默认值CURRENT_TIMESTAMP
        - DEFAULT CURRENT_TIMESTAMP 插入式不指定当前列直接给CURRENT_TIMESTAMP
        - ON UPDATE CURRENT_TIMESTAMP更新时不指定当前列，直接更新为CURRENT_TIMESTAMP
    - 如果不是第一列
      - 如果已经有默认值了，就用已经有的。
      - 如果没有默认值，会加上`DEFAULT '0000-00-00 00:00:00'`

explicit_defaults_for_timestamp=OFF

set explicit_defaults_for_timestamp = off;
show variables like 'explicit_defaults_for_timestamp';
+---------------------------------+-------+
| Variable_name                   | Value |
+---------------------------------+-------+
| explicit_defaults_for_timestamp | OFF   |
+---------------------------------+-------+


create table test5 (
  c1 timestamp,
  c2 timestamp,
  c3 timestamp,
  c4 timestamp default '2010-01-01 00:00:00',
  c5 timestamp not null,
  c6 timestamp DEFAULT CURRENT_TIMESTAMP,
  c7 timestamp ON UPDATE CURRENT_TIMESTAMP,
  c8 timestamp ON UPDATE now()
);

8.0创建出来的表

mysql> show create table test\G
*************************** 1. row ***************************
       Table: test
Create Table: CREATE TABLE `test` (
  `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 '0000-00-00 00:00:00',
  `c4` timestamp NOT NULL DEFAULT '2010-01-01 00:00:00',
  `c5` timestamp NOT NULL DEFAULT '0000-00-00 00:00:00',
  `c6` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP,
  `c7` timestamp NOT NULL DEFAULT '0000-00-00 00:00:00' ON UPDATE CURRENT_TIMESTAMP,
  `c8` timestamp NOT NULL DEFAULT '0000-00-00 00:00:00' ON UPDATE CURRENT_TIMESTAMP
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_general_ci

5.7创建出来的表

CREATE TABLE `test` (
  `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 '0000-00-00 00:00:00',
  `c4` timestamp NOT NULL DEFAULT '2010-01-01 00:00:00',
  `c5` timestamp NOT NULL DEFAULT '0000-00-00 00:00:00',
  `c6` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP,
  `c7` timestamp NOT NULL DEFAULT '0000-00-00 00:00:00' ON UPDATE CURRENT_TIMESTAMP,
  `c8` timestamp NOT NULL DEFAULT '0000-00-00 00:00:00' ON UPDATE CURRENT_TIMESTAMP
) ENGINE=InnoDB DEFAULT CHARSET=utf8

explicit_defaults_for_timestamp=ON

set explicit_defaults_for_timestamp = on;
show variables like 'explicit_defaults_for_timestamp';
+---------------------------------+-------+
| Variable_name                   | Value |
+---------------------------------+-------+
| explicit_defaults_for_timestamp | ON    |
+---------------------------------+-------+


create table test_on (
  c1 timestamp,
  c2 timestamp,
  c3 timestamp,
  c4 timestamp default '2010-01-01 00:00:00',
  c5 timestamp not null,
  c6 timestamp DEFAULT CURRENT_TIMESTAMP,
  c7 timestamp ON UPDATE CURRENT_TIMESTAMP,
  c8 timestamp ON UPDATE now()
);

8.0创建出来的表：

show create table test_on\G
*************************** 1. row ***************************
       Table: test_on
Create Table: CREATE TABLE `test_on` (
  `c1` timestamp NULL DEFAULT NULL,
  `c2` timestamp NULL DEFAULT NULL,
  `c3` timestamp NULL DEFAULT NULL,
  `c4` timestamp NULL DEFAULT '2010-01-01 00:00:00',
  `c5` timestamp NOT NULL,
  `c6` timestamp NULL DEFAULT CURRENT_TIMESTAMP,
  `c7` timestamp NULL DEFAULT NULL ON UPDATE CURRENT_TIMESTAMP,
  `c8` timestamp NULL DEFAULT NULL ON UPDATE CURRENT_TIMESTAMP
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_general_ci

5.7创建出来的表

CREATE TABLE `test_on` (
  `c1` timestamp NULL DEFAULT NULL,
  `c2` timestamp NULL DEFAULT NULL,
  `c3` timestamp NULL DEFAULT NULL,
  `c4` timestamp NULL DEFAULT '2010-01-01 00:00:00',
  `c5` timestamp NOT NULL,
  `c6` timestamp NULL DEFAULT CURRENT_TIMESTAMP,
  `c7` timestamp NULL DEFAULT NULL ON UPDATE CURRENT_TIMESTAMP,
  `c8` timestamp NULL DEFAULT NULL ON UPDATE CURRENT_TIMESTAMP
) ENGINE=InnoDB DEFAULT CHARSET=utf8

总结

• 参数在5.7和8.0的行为一致。

  • explicit_defaults_for_timestamp在5.7是OFF，表示兼容旧行为，有隐式行为。

  • explicit_defaults_for_timestamp在8.0是ON，表示不兼容旧行为，不在增加隐式行为。

• explicit_defaults_for_timestamp = OFF时

  • 所有timestamp列的not null / null行为：
    • 如果没null / not null定义，自动加not null。
    • 如果有null定义，例如c10 timestamp null，会保留timestamp NULL DEFAULT NULL。
    • 如果有not null定义，保留。
  • 所有timestamp列的默认值行为：
    • 如果是第一列
      • 如果第一列已经有默认值了，就用已经有的。c1 timestamp default '2010-01-01 00:00:00' 建表后 c1 timestamp NOT NULL DEFAULT '2010-01-01 00:00:00'
      • **（复杂）**如果第一列没有默认值，c1 timestamp建表后c1 timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP。增加了默认值CURRENT_TIMESTAMP、增加了更新默认值CURRENT_TIMESTAMP
        • DEFAULT CURRENT_TIMESTAMP 插入式不指定当前列直接给CURRENT_TIMESTAMP
        • ON UPDATE CURRENT_TIMESTAMP更新时不指定当前列，直接更新为CURRENT_TIMESTAMP
    • 如果不是第一列
      • 如果已经有默认值了，就用已经有的。
      • 如果没有默认值，会加上DEFAULT '0000-00-00 00:00:00'