CakePHP原有代码运行正常 突发SQLSTATE[42000]分组模式兼容错误

该报错触发的核心原因是当前运行环境的MySQL 5.7及以上版本默认开启了only_full_group_by的SQL严格校验模式，旧环境未开启该模式时非标准GROUP BY写法可以正常执行，一旦环境迁移、数据库版本升级就会触发校验拦截。
该校验规则要求：SELECT查询列表中出现的非聚合字段，必须同时出现在GROUP BY子句中，或是被SUM/COUNT/MAX/ANY_VALUE等聚合函数包裹，禁止查询与分组维度无函数依赖的非聚合列。

查看抛出错误的SQL语句可以发现，查询仅按TasksEmployees.employee_id单字段分组，但SELECT列表中直接查询了非聚合字段Tasks.task_completed，该字段既没有加入GROUP BY子句，也没有做聚合处理，完全不符合only_full_group_by的校验规则，因此抛出语法错误。
报错对应的原始SQL：

SELECT
    COUNT(TasksEmployees.task_id) AS `count`,
    TasksEmployees.employee_id AS `employee_id`,
    Tasks.task_completed AS `task_completed` 
FROM
    tasks_employees TasksEmployees 
    LEFT JOIN tasks Tasks ON (
        Tasks.task_completed = :c0 AND
        Tasks.id = (TasksEmployees.task_id)
    ) 
WHERE
    YEAR(TasksEmployees.created) = :c1 
GROUP BY
    TasksEmployees.employee_id

• 方案1（推荐，符合SQL标准，跨环境兼容性强）：调整CakePHP中的查询逻辑，按业务规则处理非聚合字段
  • 如果业务需求是分别统计每个员工已完成、未完成的任务数量，直接将Tasks.task_completed加入GROUP BY字段列表即可
  • 如果业务需求是按员工维度统计总任务数，仅需要附带取分组下任务完成状态的任意值，给字段套上ANY_VALUE()聚合函数即可，CakePHP中写法示例：

    $query = $this->TasksEmployees->find();
    $query->select([
        'count' => $query->func()->count('TasksEmployees.task_id'),
        'employee_id' => 'TasksEmployees.employee_id',
        // 用框架内置的func方法调用ANY_VALUE聚合函数
        'task_completed' => $query->func()->anyValue('Tasks.task_completed')
    ])
    ->leftJoin('Tasks', [
        'Tasks.task_completed' => $c0,
        'Tasks.id = TasksEmployees.task_id'
    ])
    ->where([
        'YEAR(TasksEmployees.created)' => $c1
    ])
    ->group(['TasksEmployees.employee_id']);
    

  • 如果业务需要取分组下任务完成状态的特定值（比如是否存在已完成任务），可以对应使用MAX(Tasks.task_completed)、MIN(Tasks.task_completed)等匹配业务逻辑的聚合函数。
• 方案2（临时应急，不推荐生产环境使用）：关闭MySQL的only_full_group_by校验
  修改MySQL配置文件my.cnf（Linux环境）或my.ini（Windows环境），在[mysqld]配置段中调整sql_mode参数，移除其中的ONLY_FULL_GROUP_BY项，保存后重启MySQL服务即可。配置示例：

  [mysqld]
  sql_mode=STRICT_TRANS_TABLES,NO_ZERO_IN_DATE,NO_ZERO_DATE,ERROR_FOR_DIVISION_BY_ZERO,NO_ENGINE_SUBSTITUTION
  

  也可以在数据库连接初始化时执行SET SESSION sql_mode = '上面配置的sql_mode值'实现临时生效，连接断开后配置失效。

内容的提问来源于stack exchange，提问作者ibiyemi oluyemi