您现在的位置是: 网站首页> 精品案例 which is not functionally dependent on columns in GROUP BY clause; this is incompatible with sql_mod
which is not functionally dependent on columns in GROUP BY clause; this is incompatible with sql_mod
若谷 2024-01-15 363 人已围观 【演示URL:http://www.huchengbin.com/languagedetail-3.html】
简介which is not functionally dependent on columns in GROUP BY clause; this is incompatible with sql_mod
mysql升级到5.7之后,使用之前的分组查询语句报错
which is not functionally dependent on columns in GROUP BY clause; this is incompatible with sql_mode=only_full_group_by
原因是5.7默认设置了
sql_mode=only_full_group_by
only_full_group_by :使用这个就使用和oracle一样的group 规则, select的列都要在group中,或本身是聚合列(SUM,AVG,MAX,MIN) 才行。
因为selece的列不在group中,所以报错。
解决办法如下:
方案一:在mysql的ini配置文件的[mysqld]下面添加
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也启动不了
方案二:链接数据库,执行sql:
(1)先查询 @@GLOBAL.sql_mode 执行语句,查看查询结果值;
查询sql语句:select @@GLOBAL.sql_mode;
(2)设置新的值,sql语句:
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'
下一篇:没有下一篇了!
推荐资讯
站点信息
- 程序员(若谷)的博客
- http://www.huchengbin.com
- 微信公众号:扫描二维码,关注一下