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

已于 2022-03-26 19:33:33 修改
阅读量1w 收藏 5
点赞数 10
CC 4.0 BY-SA版权
分类专栏： MySQL 8.0 文章标签： mysql 数据库开发 python 数据库 database
于 2022-03-26 19:31:36 首次发布
版权声明：本文为博主原创文章，遵循 CC 4.0 BY-SA 版权协议，转载请附上原文出处链接和本声明。
本文链接：https://blog.youkuaiyun.com/wangzihuacool/article/details/123760798
本文讲述了在将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从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万优质内容无限畅学
24、MySQL 8.0 5.1.8服务器SQL模式
dandan520520的博客
03-29 2158
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 935
this is incompatible withsql mode=only_full_group_by 通常是因为你的SQL查询与MySQL的SQL模式设置中的ONLY_FULL_GROUP_BY规则发生了冲突
MySQL 8 中的 sql_mode
学习,分享一直在路上
10-06 2116
sql_mode
mysql8设置sql_mode
ss810540895的博客
09-21 2810
在mysqlld下添加sql-mode。在mysql配置文件下设置。
mysql8.0 新特性 sql_mode(mysql 报错1055)
zero881的博客
11-17 6491
问题描述 因为开发环境和测试环境所使用的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 1153
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 487
mysql8报sql_mode=only_full_group_by问题
关系数据库-9-[mysql8]中的语法校验规则sql_mode应用
qq_20466211的博客
05-04 1682
mysql的sql_mode模式
MySQL的SQL模式sql_mode
小灯数据
03-30 1801
一、默认的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 7097
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 3332
MySQL8.0 my.ini 配置sql-mode MySQL5.7文件导入到MySQL8.0报错
MySQL的sql_mode模式说明
pleaseprintf的博客
04-07 1756
前些天发现了一个巨牛的人工智能学习网站，通俗易懂，风趣幽默，忍不住分享一下给大家。点击跳转到网站，这篇文章男女通用，看懂了就去分享给你的码吧。sql_mode是个很容易被忽视的变量,默认值是空值,在这种设置下是可以允许一些非法操作的,比如允许一些非法数据的插入。在生产环境必须将这个值设置为严格模式,所以开发、测试环境的数据库也必须要设置,这样在开发测试阶段就可以发现问题.sql model 常用来解决下面几类问题。
mysql 8.0 sql_mode=only_full_group_by
flymoringbird的博客
03-05 441
执行如下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]。
评论 3
添加红包

请填写红包祝福语或标题

个

红包个数最小为10个

元

红包金额最低5元

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

抵扣说明：

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

余额充值