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

原创

已于 2022-03-26 19:33:33 修改 · 1w 阅读

5 ·

CC 4.0 BY-SA版权

文章标签：

#mysql #数据库开发 #python #数据库 #database

于 2022-03-26 19:31:36 首次发布

本文讲述了在将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 升级踩过的坑

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

MySQL 8.0新特性专栏目录
前言：
1. MySQL升级8.0之后，业务访问数据库报错
2. 问题原因剖析
总结

前言：

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

最低0.47元/天解锁文章

200万优质内容无限畅学

确定要放弃本次机会？

福利倒计时

: :

立减 ¥

普通VIP年卡可用

立即使用

吹水老王

关注关注

10
点赞
踩
5

收藏

觉得还不错? 一键收藏
3
评论
分享

复制链接

分享到 QQ

分享到新浪微博

扫一扫
举报

举报

专栏目录

24、MySQL 8.0 5.1.8服务器SQL模式

dandan520520的博客

03-29

2160

MySQL服务器可以在不同的SQL模式下运行，并且可以根据sql_mode系统变量的值对不同的客户端应用不同的模式。DBA可以设置全局SQL模式以匹配站点服务器操作要求，并且每个应用程序可以将其会话SQL模式设置为自己的要求。模式会影响MySQL支持的SQL语法以及它执行的数据验证检查。这使得在不同的环境中使用MySQL更容易，并且可以将MySQL与其他数据库服务器一起使用。设置SQL模式最重要的...

阿里云mysql8.0 this is incompatible withsql mode=only full group by

教官的博客

04-19

936

this is incompatible withsql mode=only_full_group_by 通常是因为你的SQL查询与MySQL的SQL模式设置中的ONLY_FULL_GROUP_BY规则发生了冲突

3 条评论您还未登录，请先登录后发表或查看评论

MySQL 8 中的 sql_mode

学习,分享一直在路上

10-06

2120

sql_mode

mysql8设置sql_mode

ss810540895的博客

09-21

2811

在mysqlld下添加sql-mode。在mysql配置文件下设置。

mysql8.0 新特性 sql_mode(mysql 报错1055)

zero881的博客

11-17

6492

问题描述因为开发环境和测试环境所使用的mysql数据库的版本不一样，开发环境使用的时候57而测试环境使用的是8.0，而在Mysql的8.0版本中默认是开启sql_mode = only_full_group_by。我们使用分组查询时，出现在select字段后面的只能是group by后面的分组字段，或使用聚合函数包裹着的字段。如何解决（永久性） 1.在MySQL下执行语句 SELECT @@sql_mode 2.将查询结果中的ONLY_FULL_GROUP_BY去掉然后复制找到MySQL的配置

mysql8设置sql-mode

qq_36022463的博客

11-11

5361

mysql8报错，在mysql配置文件下设置 sql_mode=‘NO_AUTO_VALUE_ON_ZERO,STRICT_TRANS_TABLES,NO_ZERO_IN_DATE,NO_ZERO_DATE,ERROR_FOR_DIVISION_BY_ZERO,NO_ENGINE_SUBSTITUTION,PIPES_AS_CONCAT,ANSI_QUOTES’ 在mysqlld下添加sql-mode

MySQL 5.7升级8.0报异常：ONLY_FULL_GROUP_BY

最新发布

tunan666的博客

03-22

939

MySQL8.0默认的SQL模式包含了ONLY_FULL_GROUP_BY，MySQL5.7默认的SQL模式也包含了ONLY_FULL_GROUP_BY，但是MySQL5.6是不包含的

深入理解 MySQL 中的 SQL_MODE

begei的博客

12-09

1156

SQL_MODE 是 MySQL 数据库中的一个系统变量，用于控制 MySQL 如何处理 SQL 语句和数据校验。它可以看作是一组约束和规范，确保数据的准确性、完整性和一致性。例如，可以控制如何处理无效日期、是否允许插入不完整的记录、是否区分大小写等。完成不同严格程度的数据校验，有效地保障数据准确性。保证大多数 SQL 符合标准的 SQL 语法，这样应用在不同数据库之间进行迁移时，则不需要对业务 SQL 进行较大的修改。在不同数据库之间进行数据迁移之前，通过设置 SQL_MODE。

警惕参数变化，MySQL 8.0 升级避免再次踩坑

真水无香，大象无形，做本分的自己，记录技术点滴

04-04

8877

MySQL 8.0新特性专栏目录我们知道MySQL 8.0相对于MySQL 5.7加入了很多新特性，在功能和安全性上做了很多的优化和调整，这就不可避免地会修改一些参数或者函数。**我们现有业务数据从MySQL 5.7及之前的版本升级到8.0，需要特别警惕这些参数的变化，这些变化可能会导致业务行为发生一些意料之外的结果！！！** 最近MySQL 8.0 升级的过程中，我梳理了一些业务可能用到的、重要的参数变化，包括一些MySQL 8.0中已经废弃的参数和部分常见参数默认值的变化。

mysql8.0 sql_mode操作

诸葛老刘的博客

11-19

9608

文章目录前置条件:问题及其解决方案: 前置条件: mysql版本: 8.0 安装方法: docker安装使用状态: PC本地可以远程连接 docker 安装mysql:8.0 , docker 安装mysql:5.7 问题及其解决方案: 1 date数据类型不能置为0000-00-00 00:00:00 已尝试方案:mysql5.7的sql_mode操作, 未能解决问题通过各种...

关于MySQL的sql_mode合理设置详解

01-19

MySQL的sql_mode合理设置 sql_mode是个很容易被忽视的变量,默认值是空值,在这种设置下是可以允许一些非法操作的,比如允许一些非法数据的插入。在生产环境必须将这个值设置为严格模式,所以开发、测试环境的数据库也必须要设置,这样在开发测试阶段就可以发现问题. 1.sql model 常用来解决下面几类问题：　　(1) 通过设置sql mode, 可以完成不同严格程度的数据校验，有效地保障数据准备性。　　(2) 通过设置sql model 为宽松模式，来保证大多数sql符合标准的sql语法，这样应用在不同数据库之间进行迁移时，则不需要对业务sql 进行较大的修改。　　(3)

Mysql8配置sql_mode(解决group by遇到的Expression #2 of SELECT list is not in GROUP BY clause问题)

hayhead的博客

01-22

488

mysql8报sql_mode=only_full_group_by问题

关系数据库-9-[mysql8]中的语法校验规则sql_mode应用

qq_20466211的博客

05-04

1685

mysql的sql_mode模式

MySQL的SQL模式sql_mode

小灯数据

03-30

1803

一、默认的SQL模式　　MySQL 8.0的默认SQL模式为：ONLY_FULL_GROUP_BY,STRICT_TRANS_TABLES,NO_ZERO_IN_DATE,NO_ZERO_DATE,ERROR_FOR_DIVISION_BY_ZERO,NO_ENGINE_SUBSTITUTION 　　ONLY_FULL_GROUP_BY：使用group by时，select的字段必须在gro...

sql_mode详解

质量不太好的博客

09-07

7102

sql_mode详解

关于mysql8中sql_mode的设置，踩坑1055,1136错误

各位多多关照

04-09

2271

其它的都不管，只管两个值，一个ONLY_FULL_GROUP_BY，一个NO_AUTO_CREATE_USER，这两个则是1055的关键，ONLY_FULL_GROUP_BY这个值是要去掉的，不然则会出现1055；经过各种文章的查阅，以及参考官方文档，发现，mysql8的NO_AUTO_CREATE_USER这种sql模式找不到，据资料查询，mysql8已经不支持NO_AUTO_CREATE_USER这种sql模式。那么，就把sql_mode中的NO_AUTO_CREATE_USER去掉，就成功解决了；

MySQL8.0 my.ini 配置sql-mode

qq_39280694的博客

11-28

3336

MySQL8.0 my.ini 配置sql-mode MySQL5.7文件导入到MySQL8.0报错

MySQL的sql_mode模式说明

pleaseprintf的博客

04-07

1757

前些天发现了一个巨牛的人工智能学习网站，通俗易懂，风趣幽默，忍不住分享一下给大家。点击跳转到网站，这篇文章男女通用，看懂了就去分享给你的码吧。sql_mode是个很容易被忽视的变量,默认值是空值,在这种设置下是可以允许一些非法操作的,比如允许一些非法数据的插入。在生产环境必须将这个值设置为严格模式,所以开发、测试环境的数据库也必须要设置,这样在开发测试阶段就可以发现问题.sql model 常用来解决下面几类问题。

mysql 8.0 sql_mode=only_full_group_by

flymoringbird的博客

03-05

442

执行如下sql set global sql_mode='STRICT_TRANS_TABLES,NO_ZERO_IN_DATE,NO_ZERO_DATE,ERROR_FOR_DIVISION_BY_ZERO,NO_ENGINE_SUBSTITUTION'; set session sql_mode='STRICT_TRANS_TABLES,NO_ZERO_IN_DATE,NO_ZERO_D...

如何修改 sql_mode

03-13

<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]。