- 浏览: 7854844 次
- 性别:
- 来自: 广州
文章分类
- 全部博客 (2425)
- 软件工程 (75)
- JAVA相关 (662)
- ajax/web相关 (351)
- 数据库相关/oracle (218)
- PHP (147)
- UNIX/LINUX/FREEBSD/solaris (118)
- 音乐探讨 (1)
- 闲话 (11)
- 网络安全等 (21)
- .NET (153)
- ROR和GOG (10)
- [网站分类]4.其他技术区 (181)
- 算法等 (7)
- [随笔分类]SOA (8)
- 收藏区 (71)
- 金融证券 (4)
- [网站分类]5.企业信息化 (3)
- c&c++学习 (1)
- 读书区 (11)
- 其它 (10)
- 收藏夹 (1)
- 设计模式 (1)
- FLEX (14)
- Android (98)
- 软件工程心理学系列 (4)
- HTML5 (6)
- C/C++ (0)
- 数据结构 (0)
- 书评 (3)
- python (17)
- NOSQL (10)
- MYSQL (85)
- java之各类测试 (18)
- nodejs (1)
- JAVA (1)
- neo4j (3)
- VUE (4)
- docker相关 (1)
最新评论
-
xiaobadi:
jacky~~~~~~~~~
推荐两个不错的mybatis GUI生成工具 -
masuweng:
(转)JAVA获得机器码的实现 -
albert0707:
有些扩展名为null
java 7中可以判断文件的contenttype了 -
albert0707:
非常感谢!!!!!!!!!
java 7中可以判断文件的contenttype了 -
zhangle:
https://zhuban.me竹板共享 - 高效便捷的文档 ...
一个不错的网络白板工具
http://www.ywnds.com/?p=8184
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,例如:
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)
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如下:
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
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模式,如下操作:
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
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';
我们把刚才的查询再次执行:
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
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中的表达式的值。但是由于表达式的表现形式非常丰富,对于程序来说,很难精确的确定一些表达式的输出结果是明确的,比如:
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
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模式下是被支持的。
mysql> select id+1 from tt group by id+1;
+------+
| id+1 |
+------+
| 2 |
| 3 |
+------+
2 rows in set (0.00 sec)
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是语义明确的,因此该语句可以通过。
但下面这条就无法通过了。
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
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也是不严格匹配的)。
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
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中。
看下面这条语句:
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)
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,并且其等价于基础列语义。
mysql> select id+count from tt group by id,count;
+----------+
| id+count |
+----------+
| 2 |
| 3 |
| 5 |
| 6 |
+----------+
4 rows in set (0.00 sec)
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中的表达式进行匹配,否者这条查询会被认为不合法。
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,例如:
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)
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如下:
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
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模式,如下操作:
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
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';
我们把刚才的查询再次执行:
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
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中的表达式的值。但是由于表达式的表现形式非常丰富,对于程序来说,很难精确的确定一些表达式的输出结果是明确的,比如:
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
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模式下是被支持的。
mysql> select id+1 from tt group by id+1;
+------+
| id+1 |
+------+
| 2 |
| 3 |
+------+
2 rows in set (0.00 sec)
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是语义明确的,因此该语句可以通过。
但下面这条就无法通过了。
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
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也是不严格匹配的)。
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
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中。
看下面这条语句:
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)
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,并且其等价于基础列语义。
mysql> select id+count from tt group by id,count;
+----------+
| id+count |
+----------+
| 2 |
| 3 |
| 5 |
| 6 |
+----------+
4 rows in set (0.00 sec)
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中的表达式进行匹配,否者这条查询会被认为不合法。
发表评论
-
让 InnoDB 多任务运行
2018-09-06 16:06 746今天偶然看到的一招,记录下 如果服务器上的参数 innodb_ ... -
mysql中查询连接工作状态
2018-05-31 15:13 640#!/bin/bash while true do mysql ... -
MYSQL BACKUP的SHELL相关语句
2018-05-25 20:33 507#!/bin/bash ###############Basi ... -
MySQL This function has none of DETERMINISTIC, NO SQL...错误1418 的原因分析及解决方法
2018-05-08 11:17 574MySQL开启bin-log后,调用存储过程或者函数以及触发器 ... -
NUMA的选择
2018-04-24 09:52 1348现在的机器上都是有 ... -
关于MYSQL 5.7线程池的好文收集
2018-03-27 10:57 1479来自腾讯工程师的好文: https://www.jianshu ... -
MYSQL 的审计日志插件
2017-11-30 10:19 1234MYSQL 的审计日志插件,可惜目前只是LINUX用: 来自M ... -
(转)MySQL InnoDB缓冲池配置详解
2017-10-09 16:55 3993一、InnoDB缓冲池 InnoDB维护一个称为缓冲池的内存 ... -
(转)MySQL 5.7默认SQL模式带来的问题总结
2017-10-05 18:46 1824http://www.ywnds.com/?p=8865 在 ... -
MySQL 5.6 新功能之 Index Condition Pushdown (ICP)
2017-10-05 15:52 746http://www.cnblogs.com/zhoujiny ... -
mysql 5.7中的MBR和BKA算法
2017-10-03 15:11 1679一、什么是MRR MMR全称是Multi-Range Re ... -
(收藏)万字总结:学习MySQL优化原理,这一篇就够了!
2017-09-30 23:37 1146http://dbaplus.cn/news-155-1531 ... -
(转)MySQL中NULL和空值的区别
2017-09-23 15:57 2189MySQL中NULL和空值的区别 http://www.yw ... -
mysql 5.7中关于count(*)的优化
2017-09-20 19:15 2305在mysql 5.7中,对于select count(*) f ... -
MySQL 索引设计概要
2017-09-12 21:12 473<<MySQL 索引设计概要>>,不错 ... -
10分钟学会理解和解决MySQL乱码问题
2017-07-22 18:21 503http://cenalulu.github.io/mysql ... -
MySQL的or/in/union与索引优化
2017-07-22 08:29 901https://mp.weixin.qq.com/s/ZWez ... -
MYSQL中查看某个表或库的大小语句
2017-04-02 09:12 1910在information_schema.tables中有相关记 ... -
(收藏)MYSQL大表方案
2017-01-09 19:58 1395https://segmentfault.com/a/1190 ... -
(转)MySQL 特性分析之内部临时表
2016-11-28 22:54 820MySQL中的两种临时表 外部临时表 通过CREATE TEM ...
相关推荐
mysql5.7 修改max_allowed_packet方法
银河麒麟_飞腾_MYSQL 离线安装包,亲测可以使用
案例一:ONLY_FULL_GROUP_BY 问题描述 MySQL版本从5.6升级至5.7之后,部分SQL执行报错,报错信息如下: ERROR 1055 (42000): Expression #3 of XXXXXX list is not in GROUP BY clause and contain
亲测可以使用,请放心下载。
mysql5.7_for_linux源码安装,有需要的人可以下载看一下。
MySQL5.7.29_yum安装
mysql5.7默认配置文件
mysql5.7 二进制安装一键安装脚本 for centos 需要用bash运行 /bin/bash mysql5.7_bin_install.sh 安装过程中需要外网访问下载mysql程序文件
银河麒麟amd64 MYSQL离线安装包,亲测可以使用,不可使用请私信我,我给你解决方案
银河麒麟_龙芯_MYSQL离线安装包 ,亲测可以使用,放心下载
mysql5.7升级到8.0需要注意事项以及操作步骤等
原因分析:MySQL5.7版本默认设置了 mysql sql_mode = only_full_group_by 属性,导致报错。 其中ONLY_FULL_GROUP_BY就是造成这个错误的罪魁祸首了,对于group by聚合操作,如果在select中的列没有在group by中出现,...
mysql5.7.33 windows
mysql5.7.31的64位安装包,可视化图形安装界面,windows64,windows server可以直接安装
mysql5.7.33 win10 64位安装 安装方法:https://blog.csdn.net/qq_25851237/article/details/118855126
主要介绍了MySql版本问题sql_mode=only_full_group_by的完美解决方案,需要的朋友可以参考下
主要介绍了解决MySQL 5.7.9版本sql_mode=only_full_group_by问题,需要的朋友可以参考下
mysql5.7 免配置安装版本,需要电脑可以联网。动态网络下载安装内容
mysql5.7镜像;mysql5.7镜像;mysql5.7镜像;mysql5.7镜像