mysql 5.7 timestamp default_MySQL 5.7中timestamp数据类型和explicit_defaults_for_timestamp参数...

最新推荐文章于 2022-10-09 13:57:02 发布

xingming Zhenshi

最新推荐文章于 2022-10-09 13:57:02 发布

阅读量1k

点赞数

文章标签： mysql 5.7 timestamp default

本文链接：https://blog.csdn.net/weixin_30636449/article/details/113588597

版权

在默认情况下，如果 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 日志中会报如下警告：

[Warning] TIMESTAMP with implicit DEFAULT value is deprecated.

Please use --explicit_defaults_for_timestamp server option (see

documentation for more details).

[Warning]TIMESTAMPwithimplicitDEFAULTvalueisdeprecated.

Pleaseuse--explicit_defaults_for_timestampserveroption(see

documentationformoredetails).

如果我们在启动的时候在配置文件中指定了 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模式带来的问题总结。

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

1)创建测试表test_time

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)

mysql>setsessionsql_mode='';

QueryOK,0rowsaffected,1warning(0.00sec)

mysql>createtabletest_time(time1timestamp,time2timestamp,idint);

QueryOK,0rowsaffected(0.01sec)

这里关闭了 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 添加默认值时就会报错的，错误如下：

mysql> create table test_time1(time1 timestamp,time2 timestamp,id int);

ERROR 1067 (42000): Invalid default value for 'time2'

mysql>createtabletest_time1(time1timestamp,time2timestamp,idint);

ERROR1067(42000):Invaliddefaultvaluefor'time2'

表创建好了之后，下面来查看表结构信息，如下：

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)

mysql>showcreatetabletest_time\G

***************************1.row***************************

Table:test_time

CreateTable:CREATETABLE`test_time`(

`time1`timestampNOTNULLDEFAULTCURRENT_TIMESTAMPONUPDATECURRENT_TIMESTAMP,

`time2`timestampNOTNULLDEFAULT'0000-00-00 00:00:00',

`id`int(11)DEFAULTNULL

)ENGINE=InnoDBDEFAULTCHARSET=utf8mb4

1rowinset(0.00sec)

从表结构中可以看到表中 timestamp 列被自动设置为 not null，并且表中第一个 timestamp 列被设置了 DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP 默认值，第二个字段默认值为 “0000-00-00 00:00:00″。

2)插入测试

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)

mysql>insertintotest_timeselectnull,null,1;

QueryOK,1rowaffected(0.00sec)

Records:1Duplicates:0Warnings:0

mysql>select*fromtest_time;

+---------------------+---------------------+------+

|time1|time2|id|

+---------------------+---------------------+------+

|2017-02-2215:42:11|2017-02-2215:42:11|1|

+---------------------+---------------------+------+

1rowinset(0.00sec)

往 timestamp 列插入 null 值时，会自动为该列设置为 current time。

插入时未指定值的 timestamp 列中被插入了 0000-00-00 00:00:00(非表中第一个 timestamp 列)。

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>select*fromtest_time;

+---------------------+---------------------+------+

|time1|time2|id|

+---------------------+---------------------+------+

|2017-02-2215:42:11|2017-02-2215:42:11|1|

|2017-02-2215:45:09|0000-00-0000:00:00|1|

+---------------------+---------------------+------+

2rowsinset(0.00sec)

二、启动mysql时设置explicit_defaults_for_timestamp=1

1)创建表(不需要改变 sql_mode)

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)

mysql>createtabletest_time(time1timestamp,time2timestamp,idint);

QueryOK,0rowsaffected(0.00sec)

mysql>showcreatetabletest_time\G

***************************1.row***************************

Table:test_time

CreateTable:CREATETABLE`test_time1`(

`time1`timestampNULLDEFAULTNULL,

`time2`timestampNULLDEFAULTNULL,

`id`int(11)DEFAULTNULL

)ENGINE=InnoDBDEFAULTCHARSET=utf8mb4

1rowinset(0.00sec)

通过表结构我们看到，2 个 timestamp 列都被设置为 null，并且设置了默认值为 null。

2)插入测试

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)

mysql>insertintotest_timeselectnull,1;

QueryOK,1rowaffected(0.00sec)

Records:1Duplicates:0Warnings:0

mysql>select*fromtest_time;

+-------+-------+------+

|time1|time2|id|

+-------+-------+------+

|NULL|NULL|1|

+-------+-------+------+

1rowinset(0.00sec)

为 timestamp 列指定了 not null 属性，在 strict SQL mode 时，如果插入时该列没有指定值，会直接报错。

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

mysql>createtabletest_time(time1timestamp,time2timestampnotnull,idint);

QueryOK,0rowsaffected(0.01sec)

mysql>insertintotest_timeselectnull,null,1;

ERROR1048(23000):Column'time2'cannotbenull

如果为 timestamp 列指定 not null 属性，在非 stric sql_mode 模式下，如果插入的时候该列没有指定值，那么会向该列中插入 0000-00-00 00:00:00，并且产生告警。

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)

mysql>setsessionsql_mode='';

QueryOK,0rowsaffected,1warning(0.00sec)

mysql>insertintotest_timeselectnull,null,1;

QueryOK,1rowaffected,1warning(0.00sec)

Records:1Duplicates:0Warnings:1

mysql>showwarnings;

+---------+------+-------------------------------+

|Level|Code|Message|

+---------+------+-------------------------------+

|Warning|1048|Column'time2'cannotbenull|

+---------+------+-------------------------------+

1rowinset(0.00sec)

mysql>select*fromtest_time;

+-------+---------------------+------+

|time1|time2|id|

+-------+---------------------+------+

|NULL|0000-00-0000:00:00|1|

+-------+---------------------+------+

1rowinset(0.00sec)

上面说的都是关于 timestamp 类型的，如果是 datetime 呢？

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)

mysql>createtabletime1(idint,time1datetime,time2datetime);

QueryOK,0rowsaffected(0.03sec)

mysql>showcreatetabletime1\G

***************************1.row***************************

Table:time1

CreateTable:CREATETABLE`time1`(

`id`int(11)DEFAULTNULL,

`time1`datetimeDEFAULTNULL,

`time2`datetimeDEFAULTNULL

)ENGINE=InnoDBDEFAULTCHARSET=utf8

1rowinset(0.00sec)

mysql>insertintotime1(id)values(1);

QueryOK,1rowaffected(0.00sec)

mysql>select*fromtime1;

+------+-------+-------+

|id|time1|time2|

+------+-------+-------+

|1|NULL|NULL|

+------+-------+-------+

1rowinset(0.00sec)

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

xingming Zhenshi

关注

0
点赞
踩
0

收藏

觉得还不错? 一键收藏
0
评论
mysql 5.7 timestamp default_MySQL 5.7中timestamp数据类型和explicit_defaults_for_timestamp参数...

在默认情况下，如果 TIMESTAMP 列没有显示的指明 null 属性，那么该列会被自动加上 not null 属性(而其他类型的列如果没有被显示的指定 not null，那么是允许 null 值的)，如果往这个列中插入 null 值，会自动的设置该列的值为 current_timestamp 值。表中的第一个 TIMESTAMP 列，如果没有指定 null 属性或者没有指定默认值，也没有指定 ...
复制链接

扫一扫