MySQL SQLSTATE[42000]：语法错误或访问冲突：1055 SELECT 列表的表达式 #3 不在 GROUP BY 子句中并且包含非聚合
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/36950857/
Warning: these are provided under cc-by-sa 4.0 license. You are free to use/share it, But you must attribute it to the original authors (not me):
StackOverFlow
SQLSTATE[42000]: Syntax error or access violation: 1055 Expression #3 of SELECT list is not in GROUP BY clause and contains nonaggregated
提问by Saltern
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 ?
如何解决我的问题？
采纳答案by scaisEdge
In your 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_conditionstable
在您的选择中，您有一个聚合函数 sum 和一组列名，错误告诉您您没有在 group by 子句中指定正确的列名列表。可能是您应该在可能与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),
总和（机会.mantaghe），
otherwise if thes are normal columns you should use the normal sintax without ()
否则，如果它们是普通列，则应使用不带 () 的普通语法
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（我希望）
GROUP BY`opportunity`.`id`, `profile_details`.`user_id`,`opportunity_conditions.money`,
回答by chitwarnold
Run the:
跑过：
sudo mysql -u root -p
And change the SQL Mode for your MySQL Server Instance:
并为您的 MySQL 服务器实例更改 SQL 模式：
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:
另一种方法是使用 MySQL 配置。前往/etc/mysql/my.cnf：
- add a section for
[mysqld]and right below it add the statementsql_mode = "" restart the mysql service:
sudo systemctl restart mysql
- 添加一个部分，
[mysqld]并在其正下方添加语句sql_mode = "" 重启mysql服务：
sudo systemctl restart mysql
回答by pedro.caicedo.la
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 模式更改为严格为 false。
'connections' => [
'mysql' => [
'driver' => 'mysql',
'host' => env('DB_HOST', '127.0.0.1'),
'port' => env('DB_PORT', '3306'),
'database' => env('DB_DATABASE', 'forge'),
'username' => env('DB_USERNAME', 'forge'),
'password' => env('DB_PASSWORD', ''),
'unix_socket' => env('DB_SOCKET', ''),
'charset' => 'utf8mb4',
'collation' => 'utf8mb4_unicode_ci',
'prefix' => '',
'strict' => false, //from true
'engine' => null,
],
],
回答by Humphrey
Please just copy this line and run it. it worked for me.
请复制这一行并运行它。它对我有用。
SET GLOBAL sql_mode=(SELECT REPLACE(@@sql_mode,'ONLY_FULL_GROUP_BY',''));
回答by croppio.com
The solution is to edit the MySQL config file because the config will revert after every restart...
解决方案是编辑 MySQL 配置文件，因为配置将在每次重新启动后恢复...
sudo nano /etc/mysql/mysql.conf.d/mysqld.cnf
and add
并添加
[mysqld]
sql-mode=""
then restart
然后重新启动
sudo systemctl restart mysql
Works on ubuntu 18.04.
适用于 ubuntu 18.04。
回答by Connectify_user
Simply set strict false on following
只需在以下内容上设置严格的 false
Config > database.php set 'strict' => false,
回答by Italo Borssatto
You can use ANY_VALUEfor the non-aggregated columns. So, you could use:
您可以ANY_VALUE用于非聚合列。所以，你可以使用：
SELECT SUM(oc.money),
op.id,
ANY_VALUE(oc.money),
ANY_VALUE(op.mantaghe),
ANY_VALUE(op.`time`), op.`id`, ANY_VALUE(`op`.`logo`),
ANY_VALUE(`pd`.`user_id`), ANY_VALUE(`op`.`name`),
ANY_VALUE(`pd`.`co_name`), ANY_VALUE(`op`.`address`),
ANY_VALUE(`op`.`project_type_id`), ANY_VALUE(`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
More details here.
更多细节在这里。
回答by alex
Thx, this helped to me, but this will not set it PERMANENTLY, and it will revert after every restart.
谢谢，这对我有帮助，但这不会将其设置为PERMANENTLY，并且每次重新启动后都会恢复。
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"
