Mysql8:Expression #1 of ORDER BY clause is not in GROUP BY clause and contains nonaggregated column

本文地址:http://www.04007.cn/article/1020.html

    在MYSQL8进行数据库操作的时候出现报错，具体错误：[Err] 1055 - Expression #1 of ORDER BY clause is not in GROUP BY clause and contains nonaggregated column 'information_schema.PROFILING.SEQ' which is not functionally dependent on columns in GROUP BY clause; this is incompatible with sql_mode=only_full_group_by。此错误和MYSQl的sql模式有关，本文地址:http://www.04007.cn/article/1020.html,未经许可,不得转载.

    一般的mysql中的@@sql_mode值为，STRICT_TRANS_TABLES,NO_ZERO_IN_DATE,NO_ZERO_DATE,ERROR_FOR_DIVISION_BY_ZERO,NO_AUTO_CREATE_USER,NO_ENGINE_SUBSTITUTION。可以通过SQL语句：select version(), @@sql_mode;查询看到。但在mysql8中其值已经默认添加ONLY_FULL_GROUP_BY项。得到的结果是：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。
置了sql_mode=only_full_group_by的数据库,在使用时就会报错,ONLY_FULL_GROUP_BY要求sql中select后面的字段必须出现在group by后面，或者被聚合函数包裹，不然会抛出上面的错误。本文地址:http://www.04007.cn/article/1020.html,未经许可,不得转载.

#修改sql_mode
SET sql_mode=(SELECT REPLACE(@@sql_mode,'ONLY_FULL_GROUP_BY',''));
#修改etc/my.cnf文件:
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.04007.cn/article/1020.html,未经许可,不得转载.

    理论上打开这个模式，也应该是在执行group by查询的时候如不合规格才会报错，但我在执行时却是在执行insert语句，却也在最后报这个错误，是不是有些诡异。本文地址:http://www.04007.cn/article/1020.html,未经许可,不得转载.

本文地址:http://www.04007.cn/article/1020.html 未经许可,不得转载. 手机访问本页请扫描右下方二维码.

手机扫码直接打开本页面