MySQL5.7.x的sql_mode参数ONLY_FULL_GROUP_BY引起的查询异常
内容导读
互联网集市收集整理的这篇技术教程文章主要介绍了MySQL5.7.x的sql_mode参数ONLY_FULL_GROUP_BY引起的查询异常，小编现在分享给大家，供广大互联网技能从业者学习和参考。文章包含5872字，纯文字阅读大概需要9分钟。
内容图文
最近把服务器上的MySQL5.6.x升级到MySQL5.7.x，使用navicat查询SQL时，虽然能够查询到正确的结果集，但是在正确结果集下方出现如下一段异常信息：
[Err] 1055 - Expression #1 of ORDER BY clause is not in GROUP BY clause and contains nonaggregated column ‘information_schema.PROFILING.SEQ‘ which is not functionally dependent on columns in GROUP BY clause; this is incompatible with sql_mode=only_full_group_by
这是为什么呢？可以肯定SQL语句没有问题，在MySQL5.6.x上执行相同的SQL语句没有任何异常，难道升级后MySQL5.7.x有细节更新？
Linux版本的MySQL5.x中sql_mode默认为空，例如日期字段可以插入‘0000-00-00 00:00:00‘这样的值，还有如果要插入的字段长度超过列定义的长度，那么MySQL不会终止操作，而是会自动截断后面的字符继续插入操作，如下例：
mysql> create table t5 (c1 char(3));
mysql> insert into t5 values(‘abcd‘);
mysql> select * from t5;
+------+
| c1 |
+------+
| abc |
+------+
1 row in set (0.00 sec)
我们发现插入的字符被自动截断了，但是如果我们本意希望如果长度超过限制就报错，那么我们可以设置sql_mode为STRICT_TRANS_TABLES，如下：
mysql> set session sql_mode=‘STRICT_TRANS_TABLES‘
这样我们再执行同样的操作，mysql就会告诉我们插入的值太长，操作被终止，如下：
mysql> insert into t5 values(‘abcd‘);
ERROR 1406 (22001): Data too long for column ‘c1‘ at row 1
通过上面的例子，对sql_mode的简单设置，便能解决这个问题，即能控制sql规范，也能够识别"这个字符。
Windows版本的MySQL5.x的配置文件中是默认取消ONLY_FULL_GROUP_BY检查。
参数如下：
sql-mode="STRICT_TRANS_TABLES,NO_AUTO_CREATE_USER,NO_ENGINE_SUBSTITUTION"
对于强迫症人来说眼里是不容沙子的，这是我们决不允许的情况，那么我们怎么清除这段异常信息呢？
分析
根据异常信息分析发现，这问题主要是由于sql_mode引起，所以先查看sql_mode，再除ONLY_FULL_GROUP_BY参数。
解决方案 1
SELECT @@sql_mode; 或 SELECT @@global.sql_mode; //全局配置
-- 修改SQL_MODE
SET sql_mode=(SELECT REPLACE(@@sql_mode,‘ONLY_FULL_GROUP_BY‘,‘‘));
-- 或者只清除ONLY_FULL_GROUP_BY
SET sql_mode=(SELECT REPLACE(@@sql_mode,‘ONLY_FULL_GROUP_BY‘,‘STRICT_TRANS_TABLES,NO_ZERO_IN_DATE,ERROR_FOR_DIVISION_BY_ZERO,NO_AUTO_CREATE_USER,NO_ENGINE_SUBSTITUTION‘));
或
SET SESSION sql_mode = ‘STRICT_TRANS_TABLES,NO_ZERO_IN_DATE,ERROR_FOR_DIVISION_BY_ZERO,NO_AUTO_CREATE_USER,NO_ENGINE_SUBSTITUTION‘;注意：这种方法修改是会话级别的，重启MySQL服务后，再执行SQL语句仍然会出现异常信息。
解决方案 2
修改MySQL配置文件
Windows：my.ini
Linux：my.cnf
sql_mode =ONLY_FULL_GROUP_BY,STRICT_TRANS_TABLES,NO_ZERO_IN_DATE,ERROR_FOR_DIVISION_BY_ZERO,NO_AUTO_CREATE_USER,NO_ENGINE_SUBSTITUTION修改为：
sql_mode = STRICT_TRANS_TABLES,NO_ZERO_IN_DATE,ERROR_FOR_DIVISION_BY_ZERO,NO_AUTO_CREATE_USER,NO_ENGINE_SUBSTITUTION
sql_mode的作用是什么呢？
模式定义MySQL会支持哪些SQL语法。以及应执行哪种数据验证检查。
最终达到的目标：MySQL服务器能够工作在不同的SQL模式下，并能针对不同的客户端以不同的方式应用这些模式。这样，应用程序就能对服务器操作进行量身定制以满足自己的需求。这类模式定义了MySQL应支持的SQL语法，以及应该在数据上执行何种确认检查。从而能够在众多不同的环境下，与其他数据库服务器一起更容易地使用MySQL。从MySQL 4.1 开始，也能在启动之后，使用SET[SESSION|GLOBAL]sql_mode=‘mode1,mode2…‘语句，通过设置sql_mode变量更改模式。
那么ONLY_FULL_GROUP_BY是起什么作用的？
对于GROUP BY聚合操作，如果在SELECT中的列，没有在GROUP BY中出现，那么这个SQL是不合法的，因为列不在GROUP BY从句中，所以对于设置了这个mode的数据库，在使用group by 的时候，就要用MAX(),SUM(),ANT_VALUE()这种聚合函数，才能完成GROUP BY 的聚合操作。
sql_mode常用值
| sql_mode值 | 描述 |
|---|---|
| TANS | 宽松模式，对插入数据进行校验，如果不符合定义类型或长度，对数据类型调整或截断保存，报warning警告 |
| STRICT_TRANS_TABLES | 严格模式，如果不能将给定的值插入到事务表中，则放弃该语句。对于非事务表，如果值出现在单行语句或多行语句的第1行，则放弃该语句 |
| TRADITIONAL | 严格模式，Make MySQL的行为象“传统”SQL数据库系统。该模式的简单描述是当在列中插入不正确的值时“给出错误而不是警告”。注释：一旦发现错误立即放弃INSERT/UPDATE。如果你使用非事务存储引擎，这种方式不是你想要的，因为出现错误前进行的数据更改不会“滚动”，结果是更新“只进行了一部分”。 |
说明：如果把sql_mode的值设置成后面的两个值（也就是我们说的严格模式），那么当在列中插入或更新不正确的值时，mysql将会给出错误，并且放弃insert/update操作。在我们的一般应用中建议使用这两种模式，而不是使用默认的空或ANSI模式。但是需要注意的问题是，如果数据库运行在严格模式下，并且你的存储引擎不支持事务，那么有数据不一致的风险存在，比如一组sql中有两个dml语句，如果后面的一个出现了问题，但是前面的已经操作成功，那么mysql并不能回滚前面的操作。因此说设置sql_mode需要应用人员权衡各种得失，从而得到一个合适的选择
sql_mode其他值：
ONLY_FULL_GROUP_BY
对于GROUP BY聚合操作，如果在SELECT中的列，没有在GROUP BY中出现，那么这个SQL是不合法的，因为列不在GROUP BY从句中。
NO_AUTO_VALUE_ON_ZERO
该值影响自增长列的插入。默认设置下，插入0或NULL代表生成下一个自增长值。如果用户希望插入的值为0，该列又是自增长的，那么这个选项就有用了。
STRICT_TRANS_TABLES
在该模式下，如果一个值不能插入到一个事物表中，则中断当前的操作，对非事物表不做限制
NO_ZERO_IN_DATE
在严格模式下，不允许日期和月份为零
NO_ZERO_DATE
设置该值，mysql数据库不允许插入零日期，插入零日期会抛出错误而不是警告。
ERROR_FOR_DIVISION_BY_ZERO
在INSERT或UPDATE过程中，如果数据被零除，则产生错误而非警告。如 果未给出该模式，那么数据被零除时MySQL返回NULL
NO_AUTO_CREATE_USER
禁止GRANT创建密码为空的用户
NO_ENGINE_SUBSTITUTION
如果需要的存储引擎被禁用或未编译，那么抛出错误。不设置此值时，用默认的存储引擎替代，并抛出一个异常
PIPES_AS_CONCAT
将"||"视为字符串的连接操作符而非或运算符，这和Oracle数据库是一样的，也和字符串的拼接函数Concat相类似
ANSI_QUOTES
启用ANSI_QUOTES后，不能用双引号来引用字符串，因为它被解释为识别符
官方文档：https://dev.mysql.com/doc/refman/5.7/en/group-by-handling.html
MySQL5.7.x的sql_mode参数ONLY_FULL_GROUP_BY引起的查询异常
标签：head orm 服务器 rac 放弃 for 引擎 不同 全局
本文系统来源：https://blog.51cto.com/a40406/2534749
内容总结
以上是互联网集市为您收集整理的MySQL5.7.x的sql_mode参数ONLY_FULL_GROUP_BY引起的查询异常全部内容，希望文章能够帮你解决MySQL5.7.x的sql_mode参数ONLY_FULL_GROUP_BY引起的查询异常所遇到的程序开发问题。 如果觉得互联网集市技术教程内容还不错，欢迎将互联网集市网站推荐给程序员好友。
内容备注
版权声明：本文内容由互联网用户自发贡献，该文观点与技术仅代表作者本人。本站仅提供信息存储空间服务，不拥有所有权，不承担相关法律责任。如发现本站有涉嫌侵权/违法违规的内容， 请发送邮件至 gblab@vip.qq.com 举报，一经查实，本站将立刻删除。
内容手机端
扫描二维码推送至手机访问。