mysql - SilverStripe MYSQL查询错误: only_full_group_by

Question

我正在将一些旧的 SS 2.4 代码升级到 SS4。执行以下查询时出现以下错误:

错误:

Expression #1 of SELECT list is not in GROUP BY clause and contains non-aggregated column 't.Total' which is not functionally dependent on columns in GROUP BY clause;
this is incompatible with sql_mode=only_full_group_by.

如何更新此查询以与 mysql 严格模式兼容？编辑my.cnf mysql_mode好像没有生效

SELECT SalesRepRegion,COUNT(DISTINCT(SalesRepID)) as Total FROM Sale s
 WHERE SalesRepRegion <> 'NULL' AND YEARWEEK( SalesDate, 1 ) = ".$yearweek." AND Status <> 'Void'
 GROUP BY SalesRepRegion

Answer 1

原来 SilverStripe 4 在 MysqlDatabase.php 中将 mysql_mode 设置为 ANSI。要修复，我只是将以下内容放在我遇到问题的查询上方。这删除了 ​​only_full_group_by 模式

DB::get_conn()->setSQLMode('REAL_AS_FLOAT,PIPES_AS_CONCAT,ANSI_QUOTES,IGNORE_SPACE');