Mysql only_full_group_by以及其他关于sql_mode原因报错详细解决方案

最新推荐文章于 2024-03-28 15:16:42 发布

zhuxue_

最新推荐文章于 2024-03-28 15:16:42 发布

阅读量156

点赞数

Mysql only_full_group_by以及其他关于sql_mode原因报错详细解决方案

网上太多相关资料，但是抄袭严重，有的讲的也是之言片语的，根本不连贯（可能知道的人确实不想多说）
我总共花了3个多小时，反复测试，总结一下

Mysql only_full_group_by以及其他关于sql_mode原因报错详细解决方案
- 报错信息：
- 原因：
- 怎么查看呢：
- 解决办法1：
- 验证一下：
- 解决办法2
- 解决办法3
  - 对于办法3解决的解释
- 对办法2办法3两种方法的说明
- 附上其他mode解释
  - 对于其他mode说明

报错信息：

1.ERROR 1055 (42000): Expression #7 of SELECT list is not in GROUP BY clause and contains nonaggregated column 'postscan.verifyDelayLog.auditor' which is not functionally dependent on columns in GROUP BY clause; this is incompatible with sql_mode=only_full_group_by

原因：

1.mysql 5.7中的sql_mode的值为：

2.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.我的mysql5.7部署在linux上的，然后我使用navicat 12连接的,就说怎么使用navicat 12查看吧

2.使用navicat 打开数据库,然后工具-->命令列界面（快捷方式F6）

3.然后输入：SELECT @@sql_mode;

4.看到值为：

5.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：

1.我所使用的mysql运行时指定的配置如下：

2./apps/mysql5.7.18/bin/mysqld --defaults-file=/apps/my3306.cnf --basedir=/apps/mysql5.7.18 --datadir=/apps/mysql/data/3306 --plugin-dir=/apps/mysql5.7.18/lib/mysql/plugin --user=mysql --log-error=/apps/mysql/data/3306/BJ-DYC-VM-5-106.err --open-files-limit=8192 --pid-file=/apps/mysql/data/3306/BJ-DYC-VM-5-106.pid --socket=/tmp/mysql_3306.sock --port=3306

3.

4.那么我修改的是/apps/my3306.cnf，你没意见吧哈哈

5.找到：

6.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"

7.

8.修改为：

9.sql_mode = "STRICT_TRANS_TABLES,NO_ZERO_IN_DATE,NO_ZERO_DATE,ERROR_FOR_DIVISION_BY_ZERO,NO_AUTO_CREATE_USER,NO_ENGINE_SUBSTITUTION"

10.

11.因为修改了mysql配置文件，所以重新启动mysql

12./apps/mysql5.7.18/bin/mysqld --defaults-file=/apps/my3306.cnf --basedir=/apps/mysql5.7.18 --datadir=/apps/mysql/data/3306 --plugin-dir=/apps/mysql5.7.18/lib/mysql/plugin --user=mysql --log-error=/apps/mysql/data/3306/BJ-DYC-VM-5-106.err --open-files-limit=8192 --pid-file=/apps/mysql/data/3306/BJ-DYC-VM-5-106.pid --socket=/tmp/mysql_3306.sock --port=3306

验证一下：

1.进入命令列界面

2.然后输入：SELECT @@sql_mode;

3.看到值为：

4.STRICT_TRANS_TABLES,NO_ZERO_IN_DATE,NO_ZERO_DATE,ERROR_FOR_DIVISION_BY_ZERO,NO_AUTO_CREATE_USER,NO_ENGINE_SUBSTITUTION

5.

6.至此，你不用遵守：如下约定了：

7. 对于GROUP BY聚合操作，如果在SELECT中的列，没有在GROUP BY中出现，那么这个SQL是不合法的，因为列不在GROUP BY从句中，所以对于设置了这个mode的数据库，在使用group by 的时候，就要用MAX(),SUM(),ANT_VALUE()这种聚合函数，才能完成GROUP BY 的聚合操作。

解决办法2

1.第二种办法不用修改配置文件，使用navicat修改

2.进入命令行界面（F6）

3.输入：SELECT @@GLOBAL.sql_mode;

4.结果：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

5.

6.然后我们来修改sql_mode

7.输入：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 ';

8.

9.再运行你的sql：

10.SELECT

11. id,ip

12.FROM

13. ip_meta_backup

14.GROUP BY

15. ip

16.HAVING

17. count(ip) > 1

18.*****恭喜你，成功了*****

19.

20.

21.

22.然后你再运行你的sql是不是可以运行啦

23.

24.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';

25.

26.SELECT @@GLOBAL.sql_mode;

解决办法3

1.第三种办法不用修改配置文件，使用navicat修改

2.进入命令行界面（F6）

3.输入：SELECT @@sql_mode; 注意：这边缺省了session,完整的是：SELECT @@SESSION.sql_mode;

4.结果：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

5.

6.然后我们来修改sql_mode

7.输入：set SESSION sql_mode ='STRICT_TRANS_TABLES,NO_ZERO_IN_DATE,NO_ZERO_DATE,ERROR_FOR_DIVISION_BY_ZERO,NO_AUTO_CREATE_USER,NO_ENGINE_SUBSTITUTION ';

8.

9.再运行你的sql：

10.SELECT

11. id,ip

12.FROM

13. ip_meta_backup

14.GROUP BY

15. ip

16.HAVING

17. count(ip) > 1

18.*****还是报only_full_group_by错*****

19.