MySQL系统变量explicit_defaults_for_timestamp与SQL Mode，MySQL中诡异的Timestamp自动更新

置顶怕乌龟骑

已于 2025-02-28 23:53:08 修改

阅读量5.1k

点赞数 2

分类专栏： MySQL 文章标签： MySQL JPA explicit_defaults_for_timestam STRICT_TRANS_TABLES SQL_MODE

于 2019-04-10 19:48:36 首次发布

本文为博主原创文章，欢迎转载，不必注明，欢迎注明。

本文链接：https://blog.youkuaiyun.com/Zereao/article/details/89178438

版权

MySQL 专栏收录该内容

5 篇文章

订阅专栏

探讨了在使用JPA框架时，遇到的create_time和update_time字段自动更新的问题，详细分析了MySQL的explicit_defaults_for_timestamp和STRICT_TRANS_TABLES配置如何影响数据插入，以及如何利用JPA的@CreatedDate和@LastModifiedDate注解正确实现时间戳自动更新。

摘要生成于 C知道，由 DeepSeek-R1 满血版支持，前往体验 >

引子

最近在项目中碰到了一个问题：项目使用的ORM框架为JPA，Entity实体中存在下面两条属性：

create_time：希望在当前条目添加时，自动设置为当前值。
update_time：希望在条目每一次修改时，自动更新为修改时的当前时间。

然而在实际操作中，却出了一些列的问题，在这里，将问题记录并解决。

MySQL版本：5.6.24-log 远程连接工具：Navicat

问题描述

首先，测试环境数据表的建表语句为：

CREATE TABLE `flag` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `content` varchar(200) DEFAULT NULL COMMENT 'Flag内容',
  `create_time` timestamp NULL DEFAULT CURRENT_TIMESTAMP COMMENT '创建时间',
  `update_time` timestamp NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP COMMENT '更新时间',
  PRIMARY KEY (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8;

对应实体如下，省略 Getter、Setter方法：

/**
 * @author Zereao
 * @version 2019/04/10 13:43
 */
@Entity
@Table(name = "flag")
public class Flag {
    @Id
    @GeneratedValue(strategy = GenerationType.IDENTITY)
    private Long id;
    private String content;
    private Date createTime;
    private Date updateTime;
}

对应的DAO层接口定义为：

/**
 * @author Zereao
 * @version 2019/04/10 13:42
 */
@Repository
public interface FlagDAO extends CrudRepository<Flag, Long> { }

问题一：测试环境，我在开发的时候，当需要新增一条记录时，如果不手动设置createTime、updateTime字段的值，数据库中对应这两字段的值就是NULL。

插入逻辑：

public void add() {
    Flag flag = new Flag();
    flag.setContent("测试内容一");
    flagDAO.save(flag);
}

插入结果：

如上所示，数据库中create_time、update_time的值都是NULL，也就是说，我建表语句中的“DEFAULT CURRENT_TIMESTAMP”并没有起作用！

这时候，我的解决方式是：在Navicat中选中 Flag表，菜单选择 设计表，将 create_time、update_time 两个字段选中 不是null，然后点击保存（首先删除之前的那条create_time/update_time为NULL的记录，否则会报错：【1138 - Invalid use of NULL value】）。如下图：

这时候，我们再执行一遍刚刚的添加接口，结果正常，如下图：【到这里，还是有坑！请看完下文！！！】

如上图，这里我添加的时候，代码还是执行的上面的 add() 方法，并未手动指定两个字段的值，结果数据库自动帮我们设置上了。到这里，我以为问题解决，于是准备上线。

线上生产环境建表语句，我加上了两字段不允许为null的限制：

CREATE TABLE `flag` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `content` varchar(200) DEFAULT NULL COMMENT 'Flag内容',
  `create_time` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP COMMENT '创建时间',
  `update_time` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP COMMENT '更新时间',
  PRIMARY KEY (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8;

问题二：测试环境能自动更新时间，线上生产环境却不能自动更新。

就在我以为问题已经解决了的时候，项目上线，结果却报了错，如下所示：

java.sql.SQLIntegrityConstraintViolationException: Column 'create_time' cannot be null

代码仍然是上面的add()方法，未手动指定create_time、update_time，当我插入数据时，结果却报错了。这就很奇怪了，我又去测试环境测试，发现测试环境并没有问题。初步断定应该是生产环境和测试环境数据库的某一项配置不一致导致的问题。首先，我们赶紧修复问题，通过手动设置两个时间，保证项目正常可用。然后重新上线后，我继续查找资料，尝试解决问题。

通过查资料，针对这个时间自动更新这个功能，JPA其实自己封装了一套处理方案，那就是@CreatedDate、@LastModifiedDate两个注解，分别对应创建时间、最近一次的更新时间。具体使用方法也很简单：

在启动类上添加@EnableJpaAuditing注解，开启JPA自动适配功能。
在Entity实体类上添加注解@EntityListeners(AuditingEntityListener.class)，开启监听。
在create_time、update_time字段上分别加上@CreatedDate、@LastModifiedDate注解。

这样，JPA就会自动实现这两个字段的值的更新啦！下图是修改后的Entity实体类：

/**
 * @author Zereao
 * @version 2019/04/10 13:43
 */
@Entity
@Table(name = "flag")
@EntityListeners(AuditingEntityListener.class)
public class Flag {
    @Id
    @GeneratedValue(strategy = GenerationType.IDENTITY)
    private Long id;
    private String content;
    @CreatedDate
    private Date createTime;
    @LastModifiedDate
    private Date updateTime;
}

实际上，JPA还提供了@CreatedBy和@LastModifiedBy注解，用于保存和更新当前操作用户的信息，这个我不展开讲，有兴趣的同学可以自行查找资料。

到这里，这个问题是解决了。但是，到底是什么原因导致了同样的代码，生产环境和测试环境跑起来的结果不一样呢？

通过继续查找资料，我们查到了这篇文章-MySQL timestamp NOT NULL插入NULL的问题，文中提到了【explicit_defaults_for_timestamp】这个MySQL系统变量。通过查阅官方文档，我们又查到了另一个与这个问题有关的概念——【STRICT_TRANS_TABLES】这种【SQL_MODE】。

我们通过通过下面两条语句查看测试环境、生产环境的配置，发现生产环境和测试环境的配置的确不同！

SHOW VARIABLES LIKE '%explicit_defaults_for_timestamp%';

SHOW VARIABLES LIKE '%sql_mode%';

测试环境结果为：

Variable_name	Value
explicit_defaults_for_timestamp	OFF
sql_mode	NO_ENGINE_SUBSTITUTION

生产环境结果为：

Variable_name	Value
explicit_defaults_for_timestamp	ON
sql_mode	STRICT_TRANS_TABLES,NO_ENGINE_SUBSTITUTION

下面，我就展开讲一下这两几概念。

SQL_MODE

关于SQL_MODE，具体可以可以参考官方文档。官方文档中提到了这么一句话：

Modes affect the SQL syntax MySQL supports and the data validation checks it performs.

简而言之， SQL_MODE会影响MySQL支持的SQL语法以及它执行的数据验证检查。这里不多讲，与我们这个问题有关的SQL_MODE是STRICT_TRANS_TABLES这种模式，下面接着讲。

STRICT_TRANS_TABLES

当我们启用了STRICT_TRANS_TABLES后，表示 对事务表启用严格校验的SQL模式；另一种是：STRICT_ALL_TABLES 表示对所有表启用严格校验的SQL模式。官方文档中是这么描述的：

Strict mode controls how MySQL handles invalid or missing values in data-change statements such as INSERT or UPDATE.

If strict mode is not in effect, MySQL inserts adjusted values for invalid or missing values and produces warnings.

For transactional tables, an error occurs for invalid or missing values in a data-change statement when either STRICT_ALL_TABLES or STRICT_TRANS_TABLES is enabled. The statement is aborted and rolled back.

翻译一下：

严格模式控制MySQL如何处理INSERT或UPDATE等数据更改语句中的无效值或缺失值。

如果严格模式未启用，MySQL会为无效或缺失的值插入调整后的值，并产生警告。

对于事务表，当STRICT_ALL_TABLES或 STRICT_TRANS_TABLES启用时，数据更改语句中的值无效或缺失时会发生错误 。该声明被中止并回滚。

PS：

事务性表、非事务性表，可以参考MySQL的事务性表和非事务性表这篇文章。

我们的表一般都是InnoDB引擎，所以是事务性表，在STRICT_TRANS_TABLES启用时，无效值或缺失值都将会报错。所以，在生产环境报错的原因可能就是：我明确指定了两个字段 NOT NULL，在插入数据时却又没有赋值。

explicit_defaults_for_timestamp

对explicit_defaults_for_timestamp这个系统变量详细的讲解，请参考官方文档。MySQL 8.0的官方文档这么介绍的：

This system variable determines whether the server enables certain nonstandard behaviors for default values and NULL-value handling in TIMESTAMP columns. By default, explicit_defaults_for_timestamp is enabled, which disables the nonstandard behaviors. Disabling explicit_defaults_for_timestamp results in a warning.

翻译一下：

此系统变量确定服务器是否为TIMESTAMP列中的默认值和NULL值处理启用某些非标准行为。默认情况下， explicit_defaults_for_timestamp 是启用状态，同时这将禁用非标准行为。

针对explicit_defaults_for_timestamp的用法，官方文档是这么介绍的：

If explicit_defaults_for_timestamp is disabled, the server enables the nonstandard behaviors and handles TIMESTAMP columns as follows:

TIMESTAMP columns not explicitly declared with the NULL attribute are automatically declared with the NOT NULL attribute. Assigning such a column a value of NULL is permitted and sets the column to the current timestamp.
The first TIMESTAMP column in a table, if not explicitly declared with the NULL attribute or an explicit DEFAULT or ON UPDATE attribute, is automatically declared with the DEFAULT CURRENT_TIMESTAMP and ON UPDATE CURRENT_TIMESTAMP attributes.
TIMESTAMP columns following the first one, if not explicitly declared with the NULL attribute or an explicit DEFAULT attribute, are automatically declared as DEFAULT '0000-00-00 00:00:00' (the “zero” timestamp). For inserted rows that specify no explicit value for such a column, the column is assigned '0000-00-00 00:00:00' and no warning occurs.
Depending on whether strict SQL mode or the NO_ZERO_DATE SQL mode is enabled, a default value of '0000-00-00 00:00:00' may be invalid. Be aware that the TRADITIONAL SQL mode includes strict mode and NO_ZERO_DATE.

If explicit_defaults_for_timestamp is enabled, the server disables the nonstandard behaviors and handles TIMESTAMP columns as follows:

It is not possible to assign a TIMESTAMP column a value of NULL to set it to the current timestamp. To assign the current timestamp, set the column to CURRENT_TIMESTAMP or a synonym such as NOW().
TIMESTAMP columns not explicitly declared with the NOT NULL attribute are automatically declared with the NULL attribute and permit NULL values. Assigning such a column a value of NULL sets it to NULL, not the current timestamp.
TIMESTAMP columns declared with the NOT NULL attribute do not permit NULL values. For inserts that specify NULL for such a column, the result is either an error for a single-row insert or if strict SQL mode is enabled, or '0000-00-00 00:00:00' is inserted for multiple-row inserts with strict SQL mode disabled. In no case does assigning the column a value of NULL set it to the current timestamp.
TIMESTAMP columns explicitly declared with the NOT NULL attribute and without an explicit DEFAULT attribute are treated as having no default value. For inserted rows that specify no explicit value for such a column, the result depends on the SQL mode. If strict SQL mode is enabled, an error occurs. If strict SQL mode is not enabled, the column is declared with the implicit default of '0000-00-00 00:00:00' and a warning occurs. This is similar to how MySQL treats other temporal types such as DATETIME.
No TIMESTAMP column is automatically declared with the DEFAULT CURRENT_TIMESTAMP or ON UPDATE CURRENT_TIMESTAMP attributes. Those attributes must be explicitly specified.
The first TIMESTAMP column in a table is not handled differently from TIMESTAMP columns following the first one.

这里不翻译了，简单归纳下：

如果 explicit_defaults_for_timestamp 禁用：

建表的时候，如果TIMESTAMP列没有显式地声明为NULL，则该列将自动声明NOT NULL。为TIMESTAMP列分配值的时候，为此列分配NULL值是允许的，但此时此列的值将被设置为当前时间戳。
建表的时候，表中的第一个TIMESTAMP列，只有在以下三种情况都满足时，才会自动使用DEFAULT CURRENT_TIMESTAMP 和 ON UPDATE CURRENT_TIMESTAMP属性进行声明 。感谢@cHWeizZ指出的错误，原文中的not···or···短语应该翻译成既不···，也不···。
1. 没有显式地声明NULL属性（例如，文中最开始测试环境中，`create_time` timestamp NULL DEFAULT CURRENT_TIMESTAMP语句中的NULL属性）
2. 没有使用DEFAULT显式指定默认值
3. 没有使用 ON UPDATE 属性
建表的时候，第一个TIMESTAMP列后面的TIMESTAMP列，如果没有使用NULL属性进行显式声明，或者没有使用DEFAULT显式指定默认值，则自动声明为 DEFAULT '0000-00-00 00:00:00'（官方文档中称为【the “zero” timestamp】）。对于未为此类列指定显式值的插入行，该列将被分配值 '0000-00-00 00:00:00' ，并且不会发出警告。针对这一点，如果启用了“严格SQL模式”（比如，上文提到的STRICT_TRANS_TABLES模式）或者“NO_ZERO_DATE”模式，这里的零值—— '0000-00-00 00:00:00' 可能会失效。

如果 explicit_defaults_for_timestamp 启用：

设置值的时候，不能通过为TIMESTAMP列分配NULL值来将其设置为当前时间戳。如果要设置当前时间戳，需要在SQL语句中手动设置值为 CURRENT_TIMESTAMP ，或者其他意思相同的替代，例如 NOW()函数；
建表的时候，如果TIMESTAMP列未显式指定为NOT NULL，那么它将自动使用NULL属性进行声明，并且允许被设置为NULL值。如果为此列设置一个NULL值，那么此列的值将会被设置为 NULL，而不是当前时间戳。
设置值的时候，使用NOT NULL属性声明的TIMESTAMP列不允许被设置为NULL值。对于被指定为 NOT NULL 的列的 INSERT 操作，在任何情况下，都不会将该列的NULL值设置为当前时间戳，结果只有下面两种情况：
- 若启用了严格SQL模式，将会得到一个单行插入错误——第一行插入就失败，并终止后续的插入；
- 若禁用了严格SQL模式，最终将插入多行 '0000-00-00 00:00:00'。
使用NOT NULL属性显式声明，但没有显式指定DEFAULT属性的TIMESTAMP列被视为没有默认值。对于为此类列没有明确指定值的插入行，结果取决于SQL模式。如果启用了严格的SQL模式，则会发生错误。如果未启用严格SQL模式，则使用隐式默认值 '0000-00-00 00:00:00' 声明该列，并发出警告。这与MySQL处理其他时间类型的方式相同，如 DATETIME。
TIMESTAMP列不会自动声明为 DEFAULT CURRENT_TIMESTAMP 或者 ON UPDATE CURRENT_TIMESTAMP，必须明确指定这些属性。
表中第一个TIMESTAMP列的处理方式与第一个TIMESTAMP列之后的TIMESTAMP列没有区别。

案例：

下面四个案例中，都使用下面的 基础建表语句 创建表：

-- 基础建表语句
CREATE TABLE `test` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `content` varchar(40),
  `create_time` timestamp,
  `update_time` timestamp ,
  PRIMARY KEY (`id`)
) ENGINE=InnoDB;

这里，基础建表语句中有两个 TIMESTAMP列，并且都没有显式使用NULL属性，也没有使用 DEFAULT 和 ON UPDATE 属性。

在建表完成后，使用下面的 数据修改语句 来插入三条数据，观察结果。

-- 数据修改语句

-- 测试一：都传 null 值
INSERT INTO `test` (`content`, `create_time`, `update_time`) VALUES ('测试一', null, null);
-- 测试二：传一个 零值：0000-00-00 00:00:00
INSERT INTO `test` (`content`, `create_time`, `update_time`) VALUES ('测试二', null, '0000-00-00 00:00:00');
-- 测试三：传一个错误值——23，不是日期格式的数据
INSERT INTO `test` (`content`, `create_time`, `update_time`) VALUES ('测试三', '23', null);

有了上面的基础语句后，下面看四个案例。

PS：在每个案例执行完后，都将 test表 删除再重建。

案例一：STRICT_TRANS_TABLES 和 explicit_defaults_for_timestamp 都禁用。

在使用上面的 基础建表语句 建表成功后，使用Navicat查看所建立表的DDL语句：

CREATE TABLE `test` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `content` varchar(40) DEFAULT NULL,
  `create_time` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
  `update_time` timestamp NOT NULL DEFAULT '0000-00-00 00:00:00',
  PRIMARY KEY (`id`)
) ENGINE=InnoDB;

再分别执行上面的 数据修改语句，SQL执行效果如下：

content	create_time	update_time
测试一	2021-06-18 09:47:31	2021-06-18 09:47:31
测试二	2021-06-18 09:47:35	0000-00-00 00:00:00
测试三	0000-00-00 00:00:00	2021-06-18 09:55:20

结论：可以看到，SQL执行效果完全符合上面官方文档中所提到的 explicit_defaults_for_timestamp禁用的情况。

案例二：STRICT_TRANS_TABLES 禁用，explicit_defaults_for_timestamp 启用。

在使用上面的 基础建表语句 建表成功后，使用Navicat查看所建立表的DDL语句：

CREATE TABLE `test` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `content` varchar(40) DEFAULT NULL,
  `create_time` timestamp NULL DEFAULT NULL,
  `update_time` timestamp NULL DEFAULT NULL,
  PRIMARY KEY (`id`)
) ENGINE=InnoDB;

再分别执行上面的 数据修改语句，SQL执行效果如下：

content	create_time	update_time
测试一	NULL	NULL
测试二	NULL	0000-00-00 00:00:00
测试三	0000-00-00 00:00:00	NULL

结论：可以看到，SQL执行效果完全符合上面官方文档中所提到的 explicit_defaults_for_timestamp启用的情况。

案例三：STRICT_TRANS_TABLES 启用，explicit_defaults_for_timestamp 禁用。

在使用上面的 基础建表语句 建表成功后，使用Navicat查看所建立表的DDL语句：

CREATE TABLE `test` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `content` varchar(40) DEFAULT NULL,
  `create_time` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
  `update_time` timestamp NOT NULL DEFAULT '0000-00-00 00:00:00',
  PRIMARY KEY (`id`)
) ENGINE=InnoDB;

再分别执行上面的 数据修改语句，SQL执行效果如下：

content	create_time	update_time
测试一	2021-06-18 10:10:59	2021-06-18 10:10:59
测试二	2021-06-18 10:11:02	0000-00-00 00:00:00

对于测试三，执行报错：

1292 - Incorrect datetime value: '23' for column 'create_time' at row 1.

结论：

DDL语句的自动调整完全符合上面官方文档中所提到的 explicit_defaults_for_timestamp启用的情况。
严格SQL模式会校验插入的数据是否符合约束条件。（测试三）

案例四：STRICT_TRANS_TABLES 和 explicit_defaults_for_timestamp 都启用。

在使用上面的 基础建表语句 建表成功后，使用Navicat查看所建立表的DDL语句：

CREATE TABLE `test` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `content` varchar(40) DEFAULT NULL,
  `create_time` timestamp NULL DEFAULT NULL,
  `update_time` timestamp NULL DEFAULT NULL,
  PRIMARY KEY (`id`)
) ENGINE=InnoDB;

再分别执行上面的 数据修改语句，SQL执行效果如下：

content	create_time	update_time
测试一	2021-06-18 10:12:05	2021-06-18 10:12:05
测试二	2021-06-18 10:12:10	0000-00-00 00:00:00

对于测试三，执行报错：

1292 - Incorrect datetime value: '23' for column 'create_time' at row 1.

结论：

DDL语句的自动调整完全符合上面官方文档中所提到的 explicit_defaults_for_timestamp启用的情况。
严格SQL模式会校验插入的数据是否符合约束条件。（测试三）

总结：

在本次上线出现的问题中，原因是：

在测试环境，explicit_defaults_for_timestamp 被禁用。在我们修改create_time、update_time两个字段的属性为 NOT NULL 后，我们的create_time、update_time这两个字段可以仍然支持null值插入，只不过对应的TIMESTAMP列将会被自动设置为当前时间戳。所以，在测试环境，当我们新添加一条记录时，即使都为NULL，create_time和update_time两个字段总是会自动设置为当前时间戳。

而在生产环境，explicit_defaults_for_timestamp 和 STRICT_TRANS_TABLES 都启用。JPA在执行Save()方法的时候，Flag实体的createTime、updateTime的属性都还为null，对于create_time、update_time两个字段，我们明确指定了NOT NULL，所以，这两字段不允许被设置为NULL值，同时也不能通过为两字段分配NULL来设置当前时间戳，所以会报错。

综上，总结下来是：