1、MySQL 报错： 1055 – Expression #2 of SELECT list is not in GROUP BY clause and contains nonaggregated column ‘model.group_id’ which is not functionally dependent on columns in GROUP BY clause; this is incompatible with sql_mode=only_full_group_by。如图1
SQL
SELECT * FROM ((SELECT `cpa_task_group`.* FROM `cpa_task_group` LEFT JOIN `cpa_channel_app_task` ON `cpa_task_group`.`id` = `cpa_channel_app_task`.`task_group_id` LEFT JOIN `cpa_task` ON `cpa_channel_app_task`.`task_id` = `cpa_task`.`id` WHERE ((`cpa_task_group`.`is_deleted`=0) AND (`cpa_channel_app_task`.`is_deleted`=0)) AND (`cpa_task_group`.`group_id`='0bba1e30bdcd11ebb8e619991543985c')) UNION ALL ( SELECT `cpa_task_group`.* FROM `cpa_task_group` LEFT JOIN `cpa_pre_pub_log` ON `cpa_task_group`.`id` = `cpa_pre_pub_log`.`task_group_id` WHERE ((`cpa_task_group`.`is_deleted`=0) AND (`cpa_pre_pub_log`.`is_deleted`=0)) AND (`cpa_task_group`.`group_id`='0bba1e30bdcd11ebb8e619991543985c') )) `model` GROUP BY `id`
2、执行第 1 个 SQL 子句，未报错，查询结果为空。如图2
SQL
SELECT `cpa_task_group`.* FROM `cpa_task_group` LEFT JOIN `cpa_channel_app_task` ON `cpa_task_group`.`id` = `cpa_channel_app_task`.`task_group_id` LEFT JOIN `cpa_task` ON `cpa_channel_app_task`.`task_id` = `cpa_task`.`id` WHERE ((`cpa_task_group`.`is_deleted`=0) AND (`cpa_channel_app_task`.`is_deleted`=0)) AND (`cpa_task_group`.`group_id`='0bba1e30bdcd11ebb8e619991543985c')
3、执行第 2 个 SQL 子句，未报错，查询结果为空。如图3
SQL
SELECT `cpa_task_group`.* FROM `cpa_task_group` LEFT JOIN `cpa_channel_app_task` ON `cpa_task_group`.`id` = `cpa_channel_app_task`.`task_group_id` LEFT JOIN `cpa_task` ON `cpa_channel_app_task`.`task_id` = `cpa_task`.`id` WHERE ((`cpa_task_group`.`is_deleted`=0) AND (`cpa_channel_app_task`.`is_deleted`=0)) AND (`cpa_task_group`.`group_id`='0bba1e30bdcd11ebb8e619991543985c')
4、分别对比 2 个 SQL 子句的查询结果的字段。发现结果是完全一样的。
Plaintext
id group_id uuid source source_article_id status pubed_at is_deleted created_at updated_at deleted_at
id group_id uuid source source_article_id status pubed_at is_deleted created_at updated_at deleted_at
5、调整 SQL 语句，明确声明每一个字段。即查询字段与分组字段要完全一致。就不会报错。如图4
SQL
SELECT id,group_id FROM ((SELECT `cpa_task_group`.* FROM `cpa_task_group` LEFT JOIN `cpa_channel_app_task` ON `cpa_task_group`.`id` = `cpa_channel_app_task`.`task_group_id` LEFT JOIN `cpa_task` ON `cpa_channel_app_task`.`task_id` = `cpa_task`.`id` WHERE ((`cpa_task_group`.`is_deleted`=0) AND (`cpa_channel_app_task`.`is_deleted`=0)) AND (`cpa_task_group`.`group_id`='0bba1e30bdcd11ebb8e619991543985c')) UNION ALL ( SELECT `cpa_task_group`.* FROM `cpa_task_group` LEFT JOIN `cpa_pre_pub_log` ON `cpa_task_group`.`id` = `cpa_pre_pub_log`.`task_group_id` WHERE ((`cpa_task_group`.`is_deleted`=0) AND (`cpa_pre_pub_log`.`is_deleted`=0)) AND (`cpa_task_group`.`group_id`='0bba1e30bdcd11ebb8e619991543985c') )) `model` GROUP BY `id`,group_id
6、参考网址：https://stackoverflow.com/questions/40662899/sqlstate42000-syntax-error-or-access-violation-1055-expression-2 。修改 my.ini 文件。因为，修改 SQL 语句的成本过高。不再报错。如图5
INI
# 当前的配置
sql_mode=STRICT_TRANS_TABLES,NO_ZERO_IN_DATE,NO_ZERO_DATE,ERROR_FOR_DIVISION_BY_ZERO,NO_AUTO_CREATE_USER,NO_ENGINE_SUBSTITUTION
# 修改后的配置，添加：STRICT_ALL_TABLES
sql_mode=STRICT_ALL_TABLES,STRICT_TRANS_TABLES,NO_ZERO_IN_DATE,NO_ZERO_DATE,ERROR_FOR_DIVISION_BY_ZERO,NO_AUTO_CREATE_USER,NO_ENGINE_SUBSTITUTION