explicit_defaults_for_timestamp 参数详解
简介:
explicit_defaults_for_timestamp
系统变量决定MySQL服务端对timestamp列中的默认值和NULL
值的不同处理方法。此变量自MySQL 5.6.6 版本引入,分为全局级别和会话级别,可动态更新,默认值为OFF。本文主要介绍该参数打开和关闭情况下对timestamp的影响 。
1.explicit_defaults_for_timestamp = OFF
当该参数默认设置为OFF时,其行为如下:
在默认情况下,如果timestamp列没有显式的指明null属性,那么该列会被自动加上not null属性(而其他类型的列如果没有被显式的指定not null,那么是允许null值的),如果往这个列中插入null值,会自动的设置该列的值为current timestamp值。
表中的第一个timestamp列,如果没有指定null属性或者没有指定默认值,也没有指定ON UPDATE语句。那么该列会自动被加上DEFAULT CURRENT_TIMESTAMP和ON UPDATE CURRENT_TIMESTAMP属性。
对于其它TIMESTAMP列,如果没有显示指定NULL和DEFAULT属性的话,会自动设置为NOT NULL DEFAULT '0000-00-00 00:00:00'。(当然,这个与SQL_MODE有关,如果SQL_MODE中包含'NO_ZERO_DATE',实际上是不允许将其默认值设置为'0000-00-00 00:00:00'的。)
下面我们来测试下:(本文操作基于MySQL5.7.23 版本 SQL_MODE不包含'NO_ZERO_DATE')
从表结构来看,MySQL自动为第一个timestamp字段自动设置NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP属性,而后面的timestamp字段,若没有指定,则设置了 NOT NULL DEFAULT '0000-00-00 00:00:00'属性。如果向timestamp这个列中插入null值,系统会自动的设置该列的值为current timestamp值。即explicit_defaults_for_timestamp=OFF时,即使timestamp列设为NOT NULL也能插入NULL值,系统会自动将NULL值设为current timestamp。
2.explicit_defaults_for_timestamp = ON
当该参数设置为ON时,其行为如下:
如果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。
同样的,我们来测试下:
从表结构上看出,在参数开启的情况下MySQL默认会为timestamp列添加 null default null属性,而且MySQL也没有为第一个timestamp字段设置该列为current timestamp值。timestamp 字段写入null值,写入之后存储的就是null值,而不是当前的时间。当timestamp 字段指定NOT NULL时,若显式插入NULL则报错提示:该字段不能为空;若不显式插入该字段且SQL_MODE不包含'NO_ZERO_DATE',则会向该列中插入'0000-00-00 00:00:00'并且产生一个warning。
总结:
实际情况下,我们经常会这样创建表:
此时timestamp字段会指定NOT NULL,这时建议explicit_defaults_for_timestamp
参数采用默认的OFF,这样当timestamp字段显式插入NULL值时不报错,特别是程序sql写的不规范时,可以避免程序插入报错。
在不同环境间,此参数建议统一 ,不然可能出现程序在这个环境运行正常 在另外一个环境却出现报错的情况。笔者了解到亚马逊RDS MySQL5.7实例该参数默认为ON,在环境迁移时要特别注意下该参数。
版权声明: 本文为 InfoQ 作者【Simon】的原创文章。
原文链接:【http://xie.infoq.cn/article/683afbf17d79d0fb0d211c515】。文章转载请联系作者。
评论