MySQL问题：sql_mode=only_full_group_by

问题

ERROR: Expression #1 of SELECT list is not in GROUP BY clause 
and contains nonaggregated column 'xxx.sd.dic_cn_name' which 
is not functionally dependent on columns in GROUP BY clause; 
this is incompatible with sql_mode=only_full_group_by Error 
Code: 1055 

原因

从报错信息看，就是需要把xxx.sd.dic_cn_name字段放到group by子句即可。
错误SQL：

SELECT
	sd.dic_cn_name as memberTypeCnName,
	sd.dic_en_name as memberTypeEnName,
	GROUP_CONCAT( cm.member_cn_name ) as castCnName,
	GROUP_CONCAT( cm.member_en_name ) as castEnName
FROM
	xxx.sn_movie_cast_members mcm,
	xxx.sn_cast_members cm,
	xxx.sn_system_dictionary sd
WHERE
	mcm.cast_members_id = cm.id AND
	sd.dic_code = mcm.member_type_code AND
	mcm.movie_id = 214
GROUP BY
	mcm.member_type_code;

修复后的SQL：

SELECT
	sd.dic_cn_name as memberTypeCnName,
	sd.dic_en_name as memberTypeEnName,
	GROUP_CONCAT( cm.member_cn_name ) as castCnName,
	GROUP_CONCAT( cm.member_en_name ) as castEnName
FROM
	xxx.sn_movie_cast_members mcm,
	xxx.sn_cast_members cm,
	xxx.sn_system_dictionary sd
WHERE
	mcm.cast_members_id = cm.id AND
	sd.dic_code = mcm.member_type_code AND
	mcm.movie_id = 214
GROUP BY
	mcm.member_type_code, sd.dic_cn_name, sd.dic_en_name;

将需要添加的字段dic_cn_name和dic_en_name，加入GROUP BY子句中即可，不需要去改mysql配置。

参考

• 解决MySQL 5.7.9版本sql_mode=only_full_group_by问题，this is incompatible with sql_mode=only_full_group_by错误
• Mysql8.0报this is incompatible with sql_mode=only_full_group_by
• Server SQL Modes