InnoDB参数之explicit_defaults_for_timestamp

最新推荐文章于 2024-09-05 15:22:45 发布

原创最新推荐文章于 2024-09-05 15:22:45 发布 · 561 阅读

1 ·

CC 4.0 BY-SA版权

Mysql 专栏收录该内容

44 篇文章

订阅专栏

本文详细解析了MySQL中timestamp列的默认值行为，特别是在explicit_defaults_for_timestamp变量为ON和OFF时的不同表现。当该变量为OFF时，首个未指定属性的timestamp列将默认设置为CURRENT_TIMESTAMP，而非首个timestamp列则可能因sql_mode设置而报错或赋值'0000-00-00 00:00:00'。当变量为ON时，timestamp列默认为NULL，且在没有指定default value和not null的情况下，插入行为受sql_mode影响。

摘要生成于 C知道，由 DeepSeek-R1 满血版支持，前往体验 >

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 |
+------+------+---------------------+