sql_mode兼容性,MySQL 8.0 升级踩过的坑

本文讲述了在将MySQL从5.7升级到8.0时遇到的sql_mode兼容性问题,详细分析了NO_AUTO_CREATE_USER模式的变更,以及如何检查和解决不兼容的sql_mode,提醒开发者注意升级过程中的会话级参数设置问题。

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

MySQL 8.0新特性专栏目录

双重密码,MySQL 8.0创新特性
sql_mode兼容性,MySQL 8.0 升级踩过的坑



前言:

MySQL 8.0从GA到现在已经过去4年了,被各大互联网公司广泛使用,稳定性得到了充分的验证。最近,我们也在将存量的旧版本数据库升级到8.0。虽然前期做了很多的检查和验证,不过升级过程中终究免不了踩一些坑。

sql_mode,在MySQL 5.7之前是宽松模式;在MySQL 5.7之后一直到最新的8.0.28版本,都还是严格模式。不过,在不同的数据库版本之间、不同的数据库实例之间,sql_mode总会有一些变化;升级或者迁移的过程中一不小心就会掉入坑中。


1. MySQL升级8.0之后,业务访问数据库报错

一套业务库从MySQL 5.7升级到8.0之后,业务请求报错,SQLSTATE[42000]: Syntax error or access violation。

# 业务侧报错信息
SQLSTATE[42000]: Syntax error or access violation: 1231 Variable 'sql_mode' can't be set to the value of 'NO_AUTO_CREATE_USER' (SQL: select `id`, `name` from 
<think>嗯,用户问的是如何在MySQL中设置或更改sql_mode参数。我需要根据提供的引用内容来回答。首先,记得系统级指令里要求用中文,数学公式要用$...$,独立公式用$$...$$,但这个问题可能不涉及数学公式,所以可能不需要。不过还是要检查有没有可能需要的地方,比如参数值里的下划线?不过可能不需要。 用户提供的引用中有四个引用,其中引用[1]提到通过修改配置文件设置sql_mode,这样重启后也不会变。引用[3]给出了具体的配置文件例子,在my.cnf或my.ini中添加[mysqld]部分,设置sql_mode的值。引用[4]提到了MySQL 8.0的默认值,这可能对用户有用,比如需要恢复默认设置时。引用[2]讲的是SHOW CREATE TABLE不输出特定选项,可能和问题不太相关,但暂时先忽略。 用户的问题主要是修改sql_mode的方法,所以应该分两种:临时修改和永久修改。临时的话用SET命令,永久的话修改配置文件。引用[1]和[3]都提到了配置文件的方法,引用[3]给出了具体的配置示例。引用[4]的默认值可能在用户需要恢复默认时有用。 步骤应该是: 1. 临时设置:使用SET GLOBAL或SESSION,但GLOBAL需要权限,重启后会失效。 2. 永久设置:修改my.cnf或my.ini,加入sql_mode的值,然后重启服务。 3. 验证:用SELECT @@sql_mode查看当前值。 另外,用户可能需要知道如何查看当前sql_mode,所以在回答里要包括查看方法。可能还需要注意事项,比如不同的作用域(全局和会话),以及不同MySQL版本的默认值不同,特别是MySQL 8.0的变化,如引用[4]所述。 还要注意是否需要举例,比如设置多个模式,像引用[3]中的STRICT_ALL_TABLES,NO_ZERO_DATE,用逗号分隔。同时提醒用户正确分隔模式,避免语法错误。 最后,生成相关问题,可能涉及如何恢复默认值、不同模式的影响、排查sql_mode相关错误的方法等。</think>### 如何在MySQL中设置或更改`sql_mode`参数 #### 一、临时设置(会话级/全局级) 1. **会话级设置**(仅影响当前连接) 使用`SET SESSION`命令,修改仅在当前会话生效: ```sql SET SESSION sql_mode = '模式列表'; -- 例如:SET SESSION sql_mode = 'STRICT_ALL_TABLES,NO_ZERO_DATE'; ``` 2. **全局级设置**(影响所有新连接,需管理员权限) 使用`SET GLOBAL`命令,但重启后失效: ```sql SET GLOBAL sql_mode = '模式列表'; ``` #### 二、永久设置(需修改配置文件) 1. 打开MySQL配置文件: - Linux:`my.cnf`(通常位于`/etc/mysql`或`/etc`) - Windows:`my.ini`(通常位于MySQL安装目录) 2. 在`[mysqld]`部分添加或修改`sql_mode`参数: ```ini [mysqld] sql_mode = 'STRICT_ALL_TABLES,NO_ZERO_DATE' -- 示例配置[^3] ``` 3. **重启MySQL服务**使配置生效: ```bash systemctl restart mysql # Linux # 或通过服务管理器重启(Windows) ``` #### 三、验证设置 执行以下SQL查询确认当前`sql_mode`值: ```sql SELECT @@GLOBAL.sql_mode; -- 查看全局设置 SELECT @@SESSION.sql_mode; -- 查看会话设置 ``` #### 四、注意事项 1. **模式分隔符**:多个模式需用逗号分隔,例如`STRICT_TRANS_TABLES,NO_ENGINE_SUBSTITUTION`[^4]。 2. **默认值差异**:MySQL 8.0默认包含`ONLY_FULL_GROUP_BY`等模式,需注意版本兼容性。 3. **严格模式建议**:生产环境推荐启用`STRICT_TRANS_TABLES`或`STRICT_ALL_TABLES`,避免数据截断[^3]。
评论 3
添加红包

请填写红包祝福语或标题

红包个数最小为10个

红包金额最低5元

当前余额3.43前往充值 >
需支付:10.00
成就一亿技术人!
领取后你会自动成为博主和红包主的粉丝 规则
hope_wisdom
发出的红包
实付
使用余额支付
点击重新获取
扫码支付
钱包余额 0

抵扣说明:

1.余额是钱包充值的虚拟货币,按照1:1的比例进行支付金额的抵扣。
2.余额无法直接购买下载,可以购买VIP、付费专栏及课程。

余额充值