MySQL 迁移中 explicit_defaults_for_timestamp 参数影响

2024-09-06 15:12

本文主要是介绍MySQL 迁移中 explicit_defaults_for_timestamp 参数影响,希望对大家解决编程问题提供一定的参考价值,需要的开发者们随着小编来一起学习吧!

前言

最近在做数据迁移的时候,使用的是云平台自带的同步工具,在预检查阶段,当时报错 explicit_defaults_for_timestamp 参数在目标端为 off 建议修改 on,有什么风险呢?在此记录下。

测试对比

MySQL 默认情况下 explicit_defaults_for_timestamp = 0 我们对比一下看看。

explicit_defaults_for_timestamp = 0

在 explicit_defaults_for_timestamp 参数等于 0 的状态下,如果第一个 timestamp 类型的字段如果没有设置 null 将会自动加上 not null 和默认值。如果设置了默认值,则会自动加上 not null,其他类型的字段是不会的。

create table test03 (a_time timestamp ,b_time timestamp null ,c_time timestamp default '2024-01-01 00:00:00',name varchar(2) default 'a');

show create table test03;

CREATE TABLE `test03` (`a_time` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,`b_time` timestamp NULL DEFAULT NULL,`c_time` timestamp NOT NULL DEFAULT '2024-01-01 00:00:00',`name` varchar(2) DEFAULT 'a'
) ENGINE=InnoDB DEFAULT CHARSET=latin1

然后,我们尝试插入一个记录:

insert into test03 values (null, null, null, null);
a_timeb_timec_timename
2024-09-05 14:37:12NULL2024-09-05 14:37:12NULL

可以看到,在 explicit_defaults_for_timestamp 等于 0 的时候,不仅会影响表结构,还会影响写入。虽然 timestamp 是 not null 我们写入 null 后变为了 CURRENT_TIMESTAMP。

如果此时将参数设置为 1,执行相同的 SQL 语句则会报错:

# 设置参数为 1
set global explicit_defaults_for_timestamp = 1;# 插入相同的数据
insert into test03 values (null, null, null, null);

[23000][1048] Column ‘a_time’ cannot be null

explicit_defaults_for_timestamp = 1

此时在 explicit_defaults_for_timestamp 等于 1 的条件下,执行一个刚才的建表语句:

create table test04 (a_time timestamp ,b_time timestamp null ,c_time timestamp default '2024-01-01 00:00:00',d_time timestamp not null );

show create table test04;

CREATE TABLE `test04` (`a_time` timestamp NULL DEFAULT NULL,`b_time` timestamp NULL DEFAULT NULL,`c_time` timestamp NULL DEFAULT '2024-01-01 00:00:00',`d_time` timestamp NOT NULL
) ENGINE=InnoDB DEFAULT CHARSET=latin1

尝试插入数据:

insert into test04 values (null, null, null, null);
a_timeb_timec_timename
NULLNULLNULLNULL

从上面可以看到,参数开启的情况下,MySQL 默认会为 timestamp 为 NULL 的字段添加 default null 属性。而且 MySQL 没有为第一个 timestamp 设置默认值,当我写入 null 后,则按照 null 来存储。

另外,如果 timestamp 设置为 not null 那么写入 null 时会报错,如果 sql_mode 中不包含 SQL_MODE 的话,则存储为 ‘0000-00-00 00:00:00’ 并抛出一个异常。

总结

生产环境 timestamp 字段一般都会设置为如下样式,而且需要 timestamp 字段为 null 的业务场景很少。

`created_at` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP COMMENT '更新时间',
`updated_at` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP COMMENT '更新时间',

如果生产环境,要修改这个参数,从 0 调整到 1 需要关注的就是 insert 语句,有没有直接图省事插入 null 表示当前时间,如果有这种不规范的语法,那么调整该参数后,可能会报错。

云平台一般不会给用户 super 账号,所以在迁移过程中,可能无法设置 session 级别的参数,此时建议跳过该校验,不修改全局级别的参数。

这篇关于MySQL 迁移中 explicit_defaults_for_timestamp 参数影响的文章就介绍到这儿,希望我们推荐的文章对编程师们有所帮助!



http://www.chinasem.cn/article/1142355

相关文章

mysql出现ERROR 2003 (HY000): Can‘t connect to MySQL server on ‘localhost‘ (10061)的解决方法

《mysql出现ERROR2003(HY000):Can‘tconnecttoMySQLserveron‘localhost‘(10061)的解决方法》本文主要介绍了mysql出现... 目录前言:第一步:第二步:第三步:总结:前言:当你想通过命令窗口想打开mysql时候发现提http://www.cpp

MySQL大表数据的分区与分库分表的实现

《MySQL大表数据的分区与分库分表的实现》数据库的分区和分库分表是两种常用的技术方案,本文主要介绍了MySQL大表数据的分区与分库分表的实现,文中通过示例代码介绍的非常详细,对大家的学习或者工作具有... 目录1. mysql大表数据的分区1.1 什么是分区?1.2 分区的类型1.3 分区的优点1.4 分

MySQL错误代码2058和2059的解决办法

《MySQL错误代码2058和2059的解决办法》:本文主要介绍MySQL错误代码2058和2059的解决办法,2058和2059的错误码核心都是你用的客户端工具和mysql版本的密码插件不匹配,... 目录1. 前置理解2.报错现象3.解决办法(敲重点!!!)1. php前置理解2058和2059的错误

Mysql删除几亿条数据表中的部分数据的方法实现

《Mysql删除几亿条数据表中的部分数据的方法实现》在MySQL中删除一个大表中的数据时,需要特别注意操作的性能和对系统的影响,本文主要介绍了Mysql删除几亿条数据表中的部分数据的方法实现,具有一定... 目录1、需求2、方案1. 使用 DELETE 语句分批删除2. 使用 INPLACE ALTER T

MySQL INSERT语句实现当记录不存在时插入的几种方法

《MySQLINSERT语句实现当记录不存在时插入的几种方法》MySQL的INSERT语句是用于向数据库表中插入新记录的关键命令,下面:本文主要介绍MySQLINSERT语句实现当记录不存在时... 目录使用 INSERT IGNORE使用 ON DUPLICATE KEY UPDATE使用 REPLACE

MySQL Workbench 安装教程(保姆级)

《MySQLWorkbench安装教程(保姆级)》MySQLWorkbench是一款强大的数据库设计和管理工具,本文主要介绍了MySQLWorkbench安装教程,文中通过图文介绍的非常详细,对大... 目录前言:详细步骤:一、检查安装的数据库版本二、在官网下载对应的mysql Workbench版本,要是

mysql数据库重置表主键id的实现

《mysql数据库重置表主键id的实现》在我们的开发过程中,难免在做测试的时候会生成一些杂乱无章的SQL主键数据,本文主要介绍了mysql数据库重置表主键id的实现,具有一定的参考价值,感兴趣的可以了... 目录关键语法演示案例在我们的开发过程中,难免在做测试的时候会生成一些杂乱无章的SQL主键数据,当我们

浅谈mysql的sql_mode可能会限制你的查询

《浅谈mysql的sql_mode可能会限制你的查询》本文主要介绍了浅谈mysql的sql_mode可能会限制你的查询,这个问题主要说明的是,我们写的sql查询语句违背了聚合函数groupby的规则... 目录场景:问题描述原因分析:解决方案:第一种:修改后,只有当前生效,若是mysql服务重启,就会失效;

MySQL多列IN查询的实现

《MySQL多列IN查询的实现》多列IN查询是一种强大的筛选工具,它允许通过多字段组合快速过滤数据,本文主要介绍了MySQL多列IN查询的实现,具有一定的参考价值,感兴趣的可以了解一下... 目录一、基础语法:多列 IN 的两种写法1. 直接值列表2. 子查询二、对比传统 OR 的写法三、性能分析与优化1.

MySQL新增字段后Java实体未更新的潜在问题与解决方案

《MySQL新增字段后Java实体未更新的潜在问题与解决方案》在Java+MySQL的开发中,我们通常使用ORM框架来映射数据库表与Java对象,但有时候,数据库表结构变更(如新增字段)后,开发人员可... 目录引言1. 问题背景:数据库与 Java 实体不同步1.1 常见场景1.2 示例代码2. 不同操作