mysql分组报错Expression #1 of SELECT list is not in GROUP BY clause and contains nonaggregated column
环境:Server version: 5.7.27-0 ubuntu 18.04.1 (Ubuntu)
执行分组语句:select * from pet group by owner;时报错:
mysql>show global variables; 会发现sql_mode的值确实为only_full_group_by
only_full_group_by表示select字段要存在于被分组的字段中,当然聚合函数count,avg,sum,min,max是没问题的。
解决办法:修改全局变量值和会话变量值,命令行输入
set session sql_mode ='STRICT_TRANS_TABLES,NO_ZERO_IN_DATE,NO_ZERO_DATE,ERROR_FFOR_DIVISION_BY_ZERO,NO_ENGINE_SUBSTITUTION';
set global sql_mode ='STRICT_TRANS_TABLES,NO_ZERO_IN_DATE,NO_ZERO_DATE,ERROR_FFOR_DIVISION_BY_ZERO,NO_ENGINE_SUBSTITUTION';