SQLSTATE [42000]：语法错误或访问冲突：1055 SELECT列表的表达式＃3不在GROUP BY子句中并包含nonaggregated

when i got upgraded my ubuntu from 15.10 to 16.04 i have this erro in my yii2 project

当我将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

The SQL being executed was:

正在执行的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

how to solve my problem ?

怎么解决我的问题？

5 个解决方案

#1

In you select you have an aggregate function sum and a set of column name, the error tell you that you have not specified the correct list of column name in group by clause . could be you should add more columns name in group by probably related to the profile_details, opportunity_conditions table

在您选择具有聚合函数和和一组列名称时，错误会告诉您没有在group by子句中指定正确的列名列表。可能你应该在group中添加更多列名称，可能与profile_details，opportunity_conditions表有关

You have also ,(opportunity.id),(opportunity_conditions.money), (opportunity.mantaghe), why the () if you need sum you must add sum to all column

你还有，（opportunity.id），（opportunity_conditions.money），（opportunity.mantaghe），为什么（）如果你需要求和你必须为所有列添加总和

sum(opportunity.id), sum(opportunity_conditions.money),

sum(opportunity.mantaghe),

总和（opportunity.mantaghe），

otherwise if thes are normal columns you should use the normal sintax without ()

否则，如果这是正常的列你应该使用正常的sintax没有（）

opportunity.id, opportunity_conditions.money,opportunity.mantaghe,

opportunity.id，opportunity_conditions.money，opportunity.mantaghe，

I have tried to rewrite a possible query

我试图重写一个可能的查询

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

with group by on the essential column name (i hope)

与基本列名称上的分组（我希望）

GROUP BY`opportunity`.`id`,   `profile_details`.`user_id`,`opportunity_conditions.money`,  

#2

OR Just Run the

或者只是运行

$ sudo mysql -u root -p

$ sudo mysql -u root -p

change the SQL Mode for Your MySQL Server Instance

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

another way would be using the mysql configs

go to /etc/mysql/my.cnf

• add a section for [mysqld] and right below it add the statement sql_mode = ""
• 为[mysqld]添加一个部分，在它下面添加语句sql_mode =“”
• restart the mysql service $ sudo systemctl restart mysql
• 重启mysql服务$ sudo systemctl重启mysql

#3

Please just copy this line and run it. it worked for me.

请复制此行并运行它。它对我有用。

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

#4

In laravel with MySql go to file config/database.php and it change in array MySql mode strict to false.

在使用MySql的laravel中，转到文件config / database.php，它将数组MySql mode strict更改为false。

#5

Thx, this helped to me, but this will not set it PERMANENTLY, and it will revert after every restart.

Thx，这对我有帮助，但这不会永久设置它，并且每次重启后它都会恢复。

So you should set this in your config file (e.g. /etc/mysql/my.cnf in the [mysqld] section), so that the changes remain in effect after a MySQL restart:

因此，您应该在配置文件中设置它（例如[mysqld]部分中的/etc/mysql/my.cnf），以便在MySQL重启后更改仍然有效：

Config File: /etc/mysql/my.cnf

配置文件：/etc/mysql/my.cnf

[mysqld]
sql-mode="STRICT_TRANS_TABLES,NO_ENGINE_SUBSTITUTION"