mysql - SQLSTATE[42000] : Syntax error or access violation: 1055 Expression #3 of SELECT list is not in GROUP BY clause and contains nonaggregated

Question

当我将我的 ubuntu 从 15.10 升级到 16.04 时，我的 yii2 项目中出现了这个错误

SQLSTATE[42000]: Syntax error or access violation: 1055 Expression #3
of SELECT list is not in GROUP BY clause and contains nonaggregated column 
'iicityYii.opportunity_conditions.money' which is not functionally dependent 
on columns in GROUP BY clause; this is incompatible with sql_mode=only_full_group_by

正在执行的 SQL 是:

SELECT SUM(oc.money),op.id,oc.money,
            op.mantaghe,
            op.`time`, op.`id`, `op`.`logo`,
           `pd`.`user_id`, `op`.`name`, 
           `pd`.`co_name`, `op`.`address`, 
           `op`.`project_type_id`, `op`.`state_id`
FROM `opportunity` op 
INNER JOIN `profile_details` pd  ON op.user_id=pd.user_id  
INNER JOIN `opportunity_conditions` oc ON   op.id=oc.opportunity_id
GROUP BY `op`.`id`
ORDER BY `op`.`id` DESC

如何解决我的问题？

Answer 1

运行:

sudo mysql -u root -p
mysql> SELECT @@global.sql_mode;

(然后可以选择将输出复制到您的笔记中，以防您以后想恢复到原始设置。)

还有 change the SQL Mode对于您的 MySQL 服务器实例:

mysql> SET GLOBAL sql_mode=(SELECT REPLACE(@@sql_mode,'ONLY_FULL_GROUP_BY',''));

(如果您想回滚，可以使用您保存的值运行 mysql> SET 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'; 之类的东西。 )

---

一种更永久的方法(可以在 MySQL 重新启动后继续存在)是使用 MySQL 配置。转到/etc/mysql/my.cnf(或者你可能需要运行sudo vim/etc/mysql/mysql.conf.d/mysql.cnf):

• 为[mysqld] 并在其正下方添加语句sql_mode = "" 或类似 sql_mode="STRICT_TRANS_TABLES,NO_ZERO_IN_DATE,NO_ZERO_DATE,ERROR_FOR_DIVISION_BY_ZERO,NO_ENGINE_SUBSTITUTION"。

• 重启 MySQL 服务:

   sudo systemctl restart mysql
  

(或sudo service mysql restart)

另见 https://dba.stackexchange.com/a/113153/18098