一、问题
运行 SELECT @@global.sql_mode
-- 结果
ONLY_FULL_GROUP_BY,STRICT_TRANS_TABLES,NO_ZERO_IN_DATE,NO_ZERO_DATE,ERROR_FOR_DIVISION_BY_ZERO,NO_ENGINE_SUBSTITUTION
默认配置sql_mode=only_full_group_by,即要求group by的列必须在select结果中,否则会报sql错误:
Expression #1 of ORDER BY clause is not in SELECT list, references column
'xxxxx' which is not in SELECT list; this is incompatible with DISTINCT
二、解决方法
在/etc/my.cnf配置文件中指定sql_mode,可以[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。