MySQL 5.7中timestamp数据类型和explicit_defaults_for_timestamp参数

方文档中关于explicit_defaults_for_timestamp参数说明如下:explicit_defaults_for_timestamp

Deprecated5.6.6
Command-Line Format  –explicit_defaults_for_timestamp=#
System VariableNameexplicit_defaults_for_timestamp
Variable ScopeGlobal, Session
Dynamic VariableYes
Permitted ValuesTypeboolean
DefaultFALSE

在MySQL 5.7版本之前,且在MySQL 5.6.6版本之后(explicit_defaults_for_timestamp参数在MySQL 5.6.6开始加入)的版本中,如果没有设置explicit_defaults_for_timestamp=1的情况下:

1)在默认情况下,如果TIMESTAMP列没有显示的指明null属性,那么该列会被自动加上not null属性(而其他类型的列如果没有被显示的指定not null,那么是允许null值的),如果往这个列中插入null值,会自动的设置该列的值为current timestamp值。

2)表中的第一个TIMESTAMP列,如果没有指定null属性或者没有指定默认值,也没有指定ON UPDATE语句。那么该列会自动被加上DEFAULT CURRENT_TIMESTAMP和ON UPDATE CURRENT_TIMESTAMP属性。

3)第一个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列:

1)此时如果TIMESTAMP列没有显示的指定not null属性,那么默认的该列可以为null,此时向该列中插入null值时,会直接记录null,而不是current timestamp。

2)不会自动的为表中的第一个TIMESTAMP列加上DEFAULT CURRENT_TIMESTAMP和ON UPDATE CURRENT_TIMESTAMP属性,除非你在建表的时候显示的指明。

3)如果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模式带来的问题总结

一、启动mysql时未设置explicit_defaults_for_timestamp=1

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)

二、启动mysql时设置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)

可以看到跟普通类型一样。

 

转载至http://www.ywnds.com/?p=8309

### MySQL `explicit_defaults_for_timestamp` 参数的作用 `explicit_defaults_for_timestamp` 是一个 MySQL 全局变量,用于控制服务器对 `TIMESTAMP` 列的行为方式。具体来说,它决定了 `TIMESTAMP` 列的默认值以及是否允许存储特殊值 `'0000-00-00 00:00:00'` 或者 `NULL` 值。 #### 当 `explicit_defaults_for_timestamp=OFF` 在这种情况下,MySQL 使用一种向后兼容的方式处理 `TIMESTAMP` 列: - 如果未显式定义 `DEFAULT` `NULL` 属性,则 `TIMESTAMP` 列会被设置为 `NOT NULL DEFAULT '0000-00-00 00:00:00'`[^5]。 - 此外,在某些 SQL 模式下(如包含 `NO_ZERO_DATE`),这种行为可能会受到限制,从而阻止将非法日期作为默认值[^4]。 #### 当 `explicit_defaults_for_timestamp=ON` 启用此选项时,MySQL 不再使用上述隐式的默认行为,而是完全依赖用户的声明来定义 `TIMESTAMP` 列的特性: - 用户必须明确指定每一列是否支持 `NULL` 值及其默认值。 - Airflow 等工具通常需要这样的配置才能正常工作,因为它期望 `TIMESTAMP` 列具有更严格的语义,即默认值应为 `NULL` 而不是 `'0000-00-00 00:00:00'`[^2]。 --- ### 设置方法 可以通过修改 MySQL 配置文件或者通过命令行动态调整该参数的值。 #### 方法一:编辑 my.cnf 文件 找到并编辑 MySQL 的配置文件 (`my.cnf`) ,在 `[mysqld]` 下添加或更改如下内容: ```ini [mysqld] explicit_defaults_for_timestamp=ON ``` 保存文件后重启 MySQL 服务使改动生效。 #### 方法二:运行 SET GLOBAL 命令 也可以直接执行 SQL 查询以临时改变这一设定(仅限当前会话有效): ```sql SET GLOBAL explicit_defaults_for_timestamp = ON; ``` 注意这种方式不会持久化到下次启动之后;如果希望永久应用新设值,请采用第一种办法[^3]。 --- ### 示例代码展示 假设我们创建一张表 test_table 来观察不同状态下 timestamp 行为差异: ```sql CREATE TABLE test_table ( id INT PRIMARY KEY, created_at TIMESTAMP -- No attributes specified here. ); ``` 当 `explicit_defaults_for_timestamp=OFF` 时,created_at 将自动获得 NOT NULL 并且其初始值将是 ‘0000-00-00 00:00:00’ (除非受 sql_mode 影响)[^1]。 而一旦切换至 ON 状态,尝试插入记录而不提供 created_at 数据将会引发错误,直到开发者自行赋予合适的属性为止。 ---
评论
添加红包

请填写红包祝福语或标题

红包个数最小为10个

红包金额最低5元

当前余额3.43前往充值 >
需支付:10.00
成就一亿技术人!
领取后你会自动成为博主和红包主的粉丝 规则
hope_wisdom
发出的红包
实付
使用余额支付
点击重新获取
扫码支付
钱包余额 0

抵扣说明:

1.余额是钱包充值的虚拟货币,按照1:1的比例进行支付金额的抵扣。
2.余额无法直接购买下载,可以购买VIP、付费专栏及课程。

余额充值