转:MySQL 的 TIMESTAMP 类型字段非空和默认值属性的影响

原文地址 mp.weixin.qq.com

MySQL 中的 timestamp 数据类型，在使用上还是有很多讲究的，之前写的一些历史文章，

《MySQL 的 TIMESTAMP 数据类型》

《小白学习 MySQL - TIMESTAMP 类型字段非空和默认值属性的影响》

技术社区推送的这篇文章《技术分享 | MySQL– 测试 –timestamp 字段默认值测试》，讲了一些 timestamp 数据类型定义的相关问题，可以补充到我们的知识库中。

背景：

客户反馈表定义中含 timestamp 字段 (SQL 语句)，定义如下：

1
b timestamp NOT NULL

在 MySQL 中执⾏后，变成如下：

1
b timestamp NOT NULL DEFAULT '0000-00-00 00:00:00'

想知道其原因，这⾥涉及到了两个技术点：

  1. MySQL 如何给 timestamp 字段设置默认值。

  2. 00 这种时间格式是否被允许。

MySQL 如何给 timestamp 字段设置默认值？

涉及参数：explicit_defaults_for_timestamp

参数解析：

默认情况，启⽤ explicit_defaults_for_timestamp，

如果启⽤参数，表示禁⽌⾮标准⾏为，此时按如下⽅式处理 timestamp 列：

  • 若要分配当前时间戳，只能设置列为 CURRENT_TIMESTAMP 或同义词 NOW()，不能通过 NULL。

  • 没有使⽤ NOT NULL 属性显式声明的 timestamp 列将⾃动使⽤ NULL 属性声明并允许使⽤ NULL 值。

  • 对于 NOT NULL 属性声明的 timestamp 列不允许使⽤ NULL 值。

  • 使⽤了 NOT NULL 属性但未使⽤ DEFAULT 属性的 timestamp 会被认为没有默认值。

  • DEFAULT CURRENT_TIMESTAMP 或者 UPDATE CURRENT_TIMESTAMP 属性不会被⾃动声明，只能显式指定。

  • 表中第⼀个 timestamp 列的处理和其他 timestamp 列的处理⽅式并⽆不同。

如果禁⽤参数，表示开启了⾮标准⾏为，此时按如下⽅式处理 timestamp 列：

  • 没有显式声明 NULL 属性的 timestamp 列将⾃动声明 NOT NULL 属性；并允许插⼊ NULL 值 (8.0.22 之前)，但会将该列设置设置为当前时间戳。

  • 表中第⼀个 timestamp 列，如果没有显式声明 NULL 属性或者显式的 DEFAULT 属性或者 ON UPDATE 属性，则⾃动使⽤ DEFAULT CURRENT_TIMESTAMP 和 ON UPDATE CURRENT_TIMESTAMP 属性声明该列。

  • 除第⼀个 timestamp 列以外的其他 timestamp 列，如果没有显式声明 NULL 属性或者显式的 DEFAULT 属性，则会⾃动声明为 DEFAULT ‘0000-00-00 00:00:00’(“zero” 时间戳)。若 sql_mode 中包含了 NO_ZERO_DATE，则默认值可能⽆效。

zero 时间戳是否被允许：

  • 通过 sql_mode 中的 NO_ZERO_DATE 值控制。

  • 如果 sql_mode 中含 NO_ZERO_DATE，则不允许 ‘0000-00-00 00:00:00’(“zero” 时间戳) ，否则允许。

MySQL5.7.24 测试：

测试场景：

建表语句：

1
2
3
4
5
6
7
8
create database if not exists db_test;
use db_test;
drop table if exists tb_a;
create table tb_a(a timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP
,
b timestamp NOT NULL
) engine=InnoDB;
show create table tb_a \G

场景 1：

1
2
set session explicit_defaults_for_timestamp=1;
set session sql_mode='NO_ZERO_DATE';

测试截图 1：

说明 1：

因为 explicit_defaults_for_timestamp 值为 1，并不会为 timestamp 字段设置默认值。

场景 2：

1
2
set session explicit_defaults_for_timestamp=1;
set session sql_mode='';

测试截图 2：

说明 2：

因为 explicit_defaults_for_timestamp 值为 1，并不会为 timestamp 字段设置默认值。

场景 3：

1
2
set session explicit_defaults_for_timestamp=0;
set session sql_mode='NO_ZERO_DATE';

测试截图 3：

说明 3：

因为 explicit_defaults_for_timestamp 值为 0，且 b 字段不是第⼀个 timestamp 字段，没有显示声明 NULL 属性或 DEFAULT 属性，所以会赋予默认值 ‘0000-00-00 00:00:00’，⼜因为 sql_mode 中含 NO_ZERO_DATE，不允许 zero 时间戳，所以报错：

1
2
ERROR 1067 (42000): Invalid default value for 'b'

场景 4：

1
2
set session explicit_defaults_for_timestamp=0;
set session sql_mode='';

测试截图 4：

说明 4：

因为 explicit_defaults_for_timestamp 值为 0，且 b 字段不是第⼀个 timestamp 字段，没有显示声明 NULL 属性或 DEFAULT 属性，所以会赋予默认值 ‘0000-00-00 00:00:00’。

说明：

上面测试围绕客户场景设置，还有更多测试场景可供探索。

不过还是建议：
explicit_defaults_for_timestamp=1
sql_mode 含 NO_ZERO_DATE