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

相关文章

Linux内核参数配置与验证详细指南

《Linux内核参数配置与验证详细指南》在Linux系统运维和性能优化中,内核参数(sysctl)的配置至关重要,本文主要来聊聊如何配置与验证这些Linux内核参数,希望对大家有一定的帮助... 目录1. 引言2. 内核参数的作用3. 如何设置内核参数3.1 临时设置(重启失效)3.2 永久设置(重启仍生效

数据库面试必备之MySQL中的乐观锁与悲观锁

《数据库面试必备之MySQL中的乐观锁与悲观锁》:本文主要介绍数据库面试必备之MySQL中乐观锁与悲观锁的相关资料,乐观锁适用于读多写少的场景,通过版本号检查避免冲突,而悲观锁适用于写多读少且对数... 目录一、引言二、乐观锁(一)原理(二)应用场景(三)示例代码三、悲观锁(一)原理(二)应用场景(三)示例

SQL表间关联查询实例详解

《SQL表间关联查询实例详解》本文主要讲解SQL语句中常用的表间关联查询方式,包括:左连接(leftjoin)、右连接(rightjoin)、全连接(fulljoin)、内连接(innerjoin)、... 目录简介样例准备左外连接右外连接全外连接内连接交叉连接自然连接简介本文主要讲解SQL语句中常用的表

SQL server配置管理器找不到如何打开它

《SQLserver配置管理器找不到如何打开它》最近遇到了SQLserver配置管理器打不开的问题,尝试在开始菜单栏搜SQLServerManager无果,于是将自己找到的方法总结分享给大家,对SQ... 目录方法一:桌面图标进入方法二:运行窗口进入方法三:查找文件路径方法四:检查 SQL Server 安

SpringMVC获取请求参数的方法

《SpringMVC获取请求参数的方法》:本文主要介绍SpringMVC获取请求参数的方法,本文通过实例代码给大家介绍的非常详细,对大家的学习或工作具有一定的参考借鉴价值,需要的朋友可以参考下... 目录1、通过ServletAPI获取2、通过控制器方法的形参获取请求参数3、@RequestParam4、@

MySQL 中的 LIMIT 语句及基本用法

《MySQL中的LIMIT语句及基本用法》LIMIT语句用于限制查询返回的行数,常用于分页查询或取部分数据,提高查询效率,:本文主要介绍MySQL中的LIMIT语句,需要的朋友可以参考下... 目录mysql 中的 LIMIT 语句1. LIMIT 语法2. LIMIT 基本用法(1) 获取前 N 行数据(

MySQL 分区与分库分表策略应用小结

《MySQL分区与分库分表策略应用小结》在大数据量、复杂查询和高并发的应用场景下,单一数据库往往难以满足性能和扩展性的要求,本文将详细介绍这两种策略的基本概念、实现方法及优缺点,并通过实际案例展示如... 目录mysql 分区与分库分表策略1. 数据库水平拆分的背景2. MySQL 分区策略2.1 分区概念

MySQL高级查询之JOIN、子查询、窗口函数实际案例

《MySQL高级查询之JOIN、子查询、窗口函数实际案例》:本文主要介绍MySQL高级查询之JOIN、子查询、窗口函数实际案例的相关资料,JOIN用于多表关联查询,子查询用于数据筛选和过滤,窗口函... 目录前言1. JOIN(连接查询)1.1 内连接(INNER JOIN)1.2 左连接(LEFT JOI

MySQL 中查询 VARCHAR 类型 JSON 数据的问题记录

《MySQL中查询VARCHAR类型JSON数据的问题记录》在数据库设计中,有时我们会将JSON数据存储在VARCHAR或TEXT类型字段中,本文将详细介绍如何在MySQL中有效查询存储为V... 目录一、问题背景二、mysql jsON 函数2.1 常用 JSON 函数三、查询示例3.1 基本查询3.2

MySQL中动态生成SQL语句去掉所有字段的空格的操作方法

《MySQL中动态生成SQL语句去掉所有字段的空格的操作方法》在数据库管理过程中,我们常常会遇到需要对表中字段进行清洗和整理的情况,本文将详细介绍如何在MySQL中动态生成SQL语句来去掉所有字段的空... 目录在mysql中动态生成SQL语句去掉所有字段的空格准备工作原理分析动态生成SQL语句在MySQL