MySQL 5.7默认ONLY_FULL_GROUP_BY语义介绍
内容导读
互联网集市收集整理的这篇技术教程文章主要介绍了MySQL 5.7默认ONLY_FULL_GROUP_BY语义介绍,小编现在分享给大家,供广大互联网技能从业者学习和参考。文章包含6915字,纯文字阅读大概需要10分钟。
内容图文
![MySQL 5.7默认ONLY_FULL_GROUP_BY语义介绍](/upload/InfoBanner/zyjiaocheng/500/e703490a091645de88067825e64c282e.jpg)
ONLY_FULL_GROUP_BY是MySQL提供的一个sql_mode,通过这个sql_mode来提供SQL语句GROUP BY合法性的检查,在MySQL的sql_mode是非ONLY_FULL_GROUP_BY语义时。一条select语句,MySQL允许target list中输出的表达式是除聚集函数或group by column以外的表达式,这个表达式的值可能在经过group by操作后变成undefined,例如:
1 2 3 4 5 6 7 8 9 10 11 12 | mysql> create database test charset utf8mb4; mysql> use test; mysql> create table tt(id int,count int); mysql> insert into tt values(1,1),(1,2),(2,3),(2,4); mysql> select * from tt group by id; +------+-------+ | id | count | +------+-------+ | 1 | 1 | | 2 | 3 | +------+-------+ 2 rows in set (0.00 sec) |
而对于语义限制都比较严谨的多家数据库,如SQLServer、Oracle、PostgreSql都不支持select target list中出现语义不明确的列,这样的语句在这些数据库中是会被报错的,所以从MySQL 5.7版本开始修正了这个语义,就是我们所说的ONLY_FULL_GROUP_BY语义,例如查看MySQL 5.7默认的sql_mode如下:
1 2 | mysql> select @@global.sql_mode; ONLY_FULL_GROUP_BY,STRICT_TRANS_TABLES,NO_ZERO_IN_DATE,NO_ZERO_DATE,ERROR_FOR_DIVISION_BY_ZERO,NO_AUTO_CREATE_USER,NO_ENGINE_SUBSTITUTION |
去掉ONLY_FULL_GROUP_BY模式,如下操作:
1 | mysql> set global sql_mode=‘STRICT_TRANS_TABLES,NO_ZERO_IN_DATE,NO_ZERO_DATE,ERROR_FOR_DIVISION_BY_ZERO,NO_AUTO_CREATE_USER,NO_ENGINE_SUBSTITUTION‘; |
我们把刚才的查询再次执行:
1 2 3 | mysql> select id,count from tt group by id; ERROR 1055 (42000): Expression #2 of SELECT list is not in GROUP BY clause and contains nonaggregated column ‘test.tt.count‘ which is not functionally dependent on columns in GROUP BY clause; this is incompatible with sql_mode=only_full_group_by |
刚才通过的查询语句被server拒绝掉了!
所以ONLY_FULL_GROUP_BY的语义就是确定select target list中的所有列的值都是明确语义,简单的说来,在ONLY_FULL_GROUP_BY模式下,target list中的值要么是来自于聚集函数的结果,要么是来自于group by list中的表达式的值。但是由于表达式的表现形式非常丰富,对于程序来说,很难精确的确定一些表达式的输出结果是明确的,比如:
1 2 3 | mysql> select count from tt group by id+count,id; ERROR 1055 (42000): Expression #1 of SELECT list is not in GROUP BY clause and contains nonaggregated column ‘test.tt.count‘ which is not functionally dependent on columns in GROUP BY clause; this is incompatible with sql_mode=only_full_group_by |
在上面的查询语句中,其实count的值也是能被唯一确定的,但是由于程序无法分析出这种复杂的关系,所以这条查询也被拒绝掉了。
我们来看下哪些语句是在mysql的ONLY_FULL_GROUP_BY模式下是被支持的。
1 2 3 4 5 6 7 8 | mysql> select id+1 from tt group by id+1; +------+ | id+1 | +------+ | 2 | | 3 | +------+ 2 rows in set (0.00 sec) |
这条语句target list中的id+1和group by中的id+1是严格匹配的,所以mysql认为target list中的id+1是语义明确的,因此该语句可以通过。
但下面这条就无法通过了。
1 2 3 | mysql> select id+1 from tt group by 1+id; ERROR 1055 (42000): Expression #1 of SELECT list is not in GROUP BY clause and contains nonaggregated column ‘test.tt.id‘ which is not functionally dependent on columns in GROUP BY clause; this is incompatible with sql_mode=only_full_group_by |
因此,如果查询语句中的target list, having condition 或者order by list里引用了的表达式不是聚集函数,但是和group by list中的表达式严格匹配,该语句也是合法的(id+1和id+1是严格匹配的,id+1和id+2在mysql认为是不严格匹配的, id+1和1+id也是不严格匹配的)。
1 2 3 | mysql> select id,max(count) from tt group by count; ERROR 1055 (42000): Expression #1 of SELECT list is not in GROUP BY clause and contains nonaggregated column ‘test.tt.id‘ which is not functionally dependent on columns in GROUP BY clause; this is incompatible with sql_mode=only_full_group_by |
这条query被server拒绝掉了,因为target list中的id没有出现在聚集函数中,并且也没有出现在group by list中。
看下面这条语句:
1 2 3 4 5 6 7 8 | mysql> select id+1 as a from tt group by a order by id+1; +------+ | a | +------+ | 2 | | 3 | +------+ 2 rows in set (0.00 sec) |
mysql允许target list中对于非聚集函数的alias column被group by、having condition以及order by语句引用(version 5.7中允许having condition引用alias column,version 5.6不支持having condition引用alias column),从上面两条语句可以看出,group by和order by中引用了alias column,并且其等价于基础列语义。
1 2 3 4 5 6 7 8 9 10 | mysql> select id+count from tt group by id,count; +----------+ | id+count | +----------+ | 2 | | 3 | | 5 | | 6 | +----------+ 4 rows in set (0.00 sec) |
从上面的语句可以看出,mysql的ONLY_FULL_GROUP_BY模式支持对basic column进行组合但是不支持对于复杂表达式进行组合,这个受限于表达式分析程度。
总结一下:
MySQL对于ONLY_FULL_GROUP_BY语义的判断规则是,如果group by list中的表达式是basic column,那么target list中允许出现表达式是group by list中basic column或者alias column的组合结果,如果group by list中的表达式是复杂表达式(非basic column或者alias column),那么要求target list中的表达式必须能够严格和group by list中的表达式进行匹配,否者这条查询会被认为不合法。
mysql命令gruop by报错this is incompatible with sql_mode=only_full_group_by
在mysql 工具 搜索或者插入数据时报下面错误:
ERROR 1055 (42000): Expression #1 of SELECT list is not in GROUP BY clause and contains nonaggregated column ‘database_tl.emp.id‘ which is not functionally dependent on columns in GROUP BY clause; this is incompatible with sql_mode=only_full_group_by
原因:
看一下group by的语法:
select 选取分组中的列+聚合函数 from 表名称 group by 分组的列
从语法格式来看,是先有分组,再确定检索的列,检索的列只能在参加分组的列中选。
我当前Mysql版本5.7.17,
再看一下ONLY_FULL_GROUP_BY的意思是:对于GROUP BY聚合操作,如果在SELECT中的列,没有在GROUP BY中出现,那么这个SQL是不合法的,因为列不在GROUP BY从句中,也就是说查出来的列必须在group by后面出现否则就会报错,或者这个字段出现在聚合函数里面。
查看mysql版本命令:select version();
查看sql_model参数命令:
SELECT @@GLOBAL.sql_mode;
SELECT @@SESSION.sql_mode;
发现:
ONLY_FULL_GROUP_BY,STRICT_TRANS_TABLES,NO_ZERO_IN_DATE,NO_ZERO_DATE,ERROR_FOR_DIVISION_BY_ZERO,NO_AUTO_CREATE_USER,NO_ENGINE_SUBSTITUTION
第一项默认开启ONLY_FULL_GROUP_BY,
解决方法:
1.只选择出现在group by后面的列,或者给列增加聚合函数;(不推荐)
2.命令行输入:
set @@GLOBAL.sql_mode=‘‘;
set sql_mode =‘STRICT_TRANS_TABLES,NO_ZERO_IN_DATE,NO_ZERO_DATE,ERROR_FOR_DIVISION_BY_ZERO,NO_AUTO_CREATE_USER,NO_ENGINE_SUBSTITUTION‘;
默认关掉ONLY_FULL_GROUP_BY!
这个时候 在用工具select 一下
SELECT @@sql_mode;
SELECT @@GLOBAL.sql_mode;
发现已经不存在ONLY_FULL_GROUP_BY ,感觉已经OK。但是如果你重启Mysql服务的话,发现ONLY_FULL_GROUP_BY还是会存在的
想要彻底解决这个问题 就得去改my.ini 配置(如果你们mysql 没有这个文件,就把my-default.ini 改成my.ini,我这个版本就是没有my.ini配置问题)
在 [mysqld]和[mysql]下添加
SET sql_mode =‘STRICT_TRANS_TABLES,NO_ZERO_IN_DATE,NO_ZERO_DATE,ERROR_FOR_DIVISION_BY_ZERO,NO_AUTO_CREATE
MySQL 5.7默认ONLY_FULL_GROUP_BY语义介绍
标签:oba ase exp ini 否则 def and sql -o
本文系统来源:https://www.cnblogs.com/xzjf/p/8466858.html
内容总结
以上是互联网集市为您收集整理的MySQL 5.7默认ONLY_FULL_GROUP_BY语义介绍全部内容,希望文章能够帮你解决MySQL 5.7默认ONLY_FULL_GROUP_BY语义介绍所遇到的程序开发问题。 如果觉得互联网集市技术教程内容还不错,欢迎将互联网集市网站推荐给程序员好友。
内容备注
版权声明:本文内容由互联网用户自发贡献,该文观点与技术仅代表作者本人。本站仅提供信息存储空间服务,不拥有所有权,不承担相关法律责任。如发现本站有涉嫌侵权/违法违规的内容, 请发送邮件至 gblab@vip.qq.com 举报,一经查实,本站将立刻删除。
内容手机端
扫描二维码推送至手机访问。