• 进入"运维那点事"后,希望您第一件事就是阅读“关于”栏目,仔细阅读“关于Ctrl+c问题”,不希望误会!

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

MySQL 5.7 彭东稳 8年前 (2017-02-22) 40572次浏览 已收录 2个评论

在 MySQL 中,由于 TIMESTAMP 类型占用的空间为 4 个字节,理论上其能够存储最大的日期为 “2038-01-19 03:14:07”,而在 MySQL 5.6 之后占用的内存空间为 7 个字节,可以精确到毫秒、微秒,但是这个最大日期并没有被改变。所以,当我们存储时间大于 “2038-01-19 03:14:07” 时系统就会报错。

这个限制在 MySQL 官方 11.2.2 The DATE, DATETIME, and TIMESTAMP Types[1] 也有描述:


explicit_defaults_for_timestamp

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

Deprecated 5.6.6
Command-Line Format --explicit_defaults_for_timestamp=#
System Variable Name explicit_defaults_for_timestamp
Variable Scope Global, Session
Dynamic Variable Yes
Permitted Values Type boolean
Default FALSE

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

  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 日志中会报如下警告:

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

设置 explicit_defaults_for_timestamp=0

1)创建测试表test_time

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

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

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

2)插入测试

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

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

设置 explicit_defaults_for_timestamp=1

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

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

2)插入测试

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

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

上面说的都是关于 timestamp 类型的,如果是 datetime 呢?

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


如果您觉得本站对你有帮助,那么可以支付宝扫码捐助以帮助本站更好地发展,在此谢过。
喜欢 (7)
[资助本站您就扫码 谢谢]
分享 (0)

您必须 登录 才能发表评论!

(2)个小伙伴在吐槽
  1. 3)第一个TIMESTAMP列之后的其他的TIMESTAMP类型的列,如果没有指定null属性,也没有指定默认值,那么该列会被自动加上DEFAULT ‘0000-00-00 00:00:00’属性。如果insert语句中没有为该列指定值,那么该列中插入’0000-00-00 00:00:00’,并且没有warning。 非第一个timestamp列, 不指定null属性, 默认是给加上not null了,而且iinsert的时候不指定也会报错,原文这里说错了吧
    benson2017-12-07 23:01 Linux | Chrome 60.0.3112.40
    • 下面有验证啊,不会报错的。但要注意SQL模式及参数设置。
      彭东稳2017-12-08 14:47 Mac OS X | Chrome 61.0.3163.79