[Err] 1055
来源:互联网 发布:别克车钥匙淘宝图片 编辑:程序博客网 时间:2024/06/05 02:07
这个错误发生在mysql 5.7.5 和以后上,因为5.7.5默认的sql模式配置是
ONLY_FULL_GROUP_BY,
这个配置启用的是 “严格ANSIsql 规则”,严格ANSI sql 规则要求在group by的时候,没有聚合的列,在group by的时候,必须全部包含在group by 的字段中。
没有聚合的列,指的是没有使用 max, min, count, sum....这些函数的列,直接查询出字段的列。
如果不是aggregate 的列,必须要全部包含在集合里面。aggregate 的列,指的大概就是不能聚合的列,没有用函数的列,比如说 avg, sum, count 这些函数的列。
用了这些函数的列,可以不包含
英文摘要如下:
When MySQL's only_full_group_by mode is turned on, it means that strict ANSI SQL rules will apply when using GROUP BY. With regard to your query, this means that if you GROUP BY of the proof_type column, then you can only select two things:
the proof_type column, or aggregates of any other column
Use ANY_VALUE() to refer to the nonaggregated column.
From MySQL 5.7 docs:
You can achieve the same effect without disabling ONLY_FULL_GROUP_BY by using ANY_VALUE() to refer to the nonaggregated column.
...
This query might be invalid with ONLY_FULL_GROUP_BY enabled because the nonaggregated address column in the select list is not named in the GROUP BY clause:
SELECT name, address, MAX(age) FROM t GROUP BY name;
...
If you know that, for a given data set, each name value in fact uniquely determines the address value, address is effectively functionally dependent on name. To tell MySQL to accept the query, you can use the ANY_VALUE() function:
SELECT name, ANY_VALUE(address), MAX(age) FROM t GROUP BY name;
实际上,在sql规则中,也是一直这么说的,“除聚集计算语句之外,select语句中的每个列,都必须在group by 语句中给出”
上图来源 https://cxiaodian.gitbooks.io/mysql/content/chapter9.html
不过按照官方文档的这么讲,我试了一下,好像没看到什么区别???
create table test_group_by(name varchar(20),address varchar(50),age int )engine=innodb charset utf8;INSERT INTO `easyspider`.`test_group_by` (`name`, `address`, `age`) VALUES ('a', 'a', '22');INSERT INTO `easyspider`.`test_group_by` (`name`, `address`, `age`) VALUES ('b', 'b', '22');INSERT INTO `easyspider`.`test_group_by` (`name`, `address`, `age`) VALUES ('c', 'c', '20');INSERT INTO `easyspider`.`test_group_by` (`name`, `address`, `age`) VALUES ('a', 'a', '25');INSERT INTO `easyspider`.`test_group_by` (`name`, `address`, `age`) VALUES ('b', 'b', '25');select name, address, max(age)from test_group_bygroup by name, address;select name, any_value(address), max(age)from test_group_bygroup by name;set sql_mode='NO_ENGINE_SUBSTITUTION';select name, address, max(age)from test_group_bygroup by name;
具体的原因如下:
参考链接:
https://stackoverflow.com/questions/41887460/select-list-is-not-in-group-by-clause-and-contains-nonaggregated-column-inc
https://stackoverflow.com/questions/34115174/error-related-to-only-full-group-by-when-executing-a-query-in-mysql
http://dict.youdao.com/w/aggregate%20/#keyfrom=dict2.top
https://stackoverflow.com/questions/37951742/1055-expression-of-select-list-is-not-in-group-by-clause-and-contains-nonaggr/37951813
- [Err] 1055
- [Err] 1055
- [Err] 1055
- [Err] 1055
- [Err] 1055
- [Err] 1055
- [Err] 1055
- MYSQL ERR 1055 ONLY_FULL_GROUP_BY 错误解决
- MySQL_插入数据异常[Err] 1055
- 关于mysql5.7 [Err] 1055 group by
- mysql 5.7会遇到 [Err] 1055
- linux mysql5.7报err 1055错误 sql_mode=only_full_group_by
- Err 对象
- err.c
- fastjson-err
- [Err] 42000
- [Err] 23000
- [Err] 1091
- shell 脚本
- Solr 和 ElasticSearch 对比
- Hadoop入门之集群搭建
- python from collections import namedtuple
- set map list的用法和区别
- [Err] 1055
- 文件复制
- Intellij Idea(Mac)调试功能总结
- 做了这么多年的技术,你有写过博客吗?
- Python中的IO编程
- 【整理】web前端相关知识文档手册整理
- 关于kafka的学习
- HDU6170 Two strings(dp,2017 HDU多校联赛 第9场)
- snaker mybatis 配置