我已经升级了我的系统,并为我正在开发的web应用程序安装了MySql 5.7.9和php。我有一个查询,是动态创建的,当运行在旧版本的MySQL它工作得很好。自从升级到5.7,我得到这个错误:

SELECT列表的表达式#1不在GROUP BY子句中,并且包含 未聚合列的support_desk.mod_users_groups。Group_id '就是 在功能上不依赖于GROUP BY子句中的列;这是 sql_mode=only_full_group_by不兼容

请注意Mysql 5.7的手册页中关于Server SQL Modes的主题。

这个问题让我很困扰:

SELECT mod_users_groups.group_id AS 'value', 
       group_name AS 'text' 
FROM mod_users_groups
LEFT JOIN mod_users_data ON mod_users_groups.group_id = mod_users_data.group_id 
WHERE  mod_users_groups.active = 1 
  AND mod_users_groups.department_id = 1 
  AND mod_users_groups.manage_work_orders = 1 
  AND group_name != 'root' 
  AND group_name != 'superuser' 
GROUP BY group_name 
HAVING COUNT(`user_id`) > 0 
ORDER BY group_name

我不理解only_full_group_by,不足以弄清楚我需要做什么来修复查询。我是否可以关闭only_full_group_by选项,或者还有其他需要做的事情?


当前回答

增加/etc/mysql/my.cnf文件中的行(如下所述)

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

对我来说没问题。 服务器版本:5.7.18-0ubuntu0.16.04.1 - (Ubuntu)

其他回答

mac:

1.复制默认的my-default.cnf到/etc/my.cnf

sudo cp $(brew --prefix mysql)/support-files/my-default.cnf /etc/my.cnf

2.使用您喜欢的编辑器更改my.cnf中的sql_mode并将其设置为此

sql_mode=STRICT_TRANS_TABLES,NO_ZERO_IN_DATE,NO_ZERO_DATE,ERROR_FOR_DIVISION_BY_ZERO,NO_AUTO_CREATE_USER,NO_ENGINE_SUBSTITUTION

3.重新启动MySQL服务器。

mysql.server restart

使用ANY_VALUE()引用未聚合的列。

SELECT name,           address , MAX(age) FROM t GROUP BY name; -- fails
SELECT name, ANY_VALUE(address), MAX(age) FROM t GROUP BY name; -- works

从MySQL 5.7文档:

You can achieve the same effect without disabling ONLY_FULL_GROUP_BY by using ANY_VALUE() to refer to the nonaggregated column. ... This query might be invalid with ONLY_FULL_GROUP_BY enabled because the nonaggregated address column in the select list is not named in the GROUP BY clause: SELECT name, address, MAX(age) FROM t GROUP BY name; ... If you know that, for a given data set, each name value in fact uniquely determines the address value, address is effectively functionally dependent on name. To tell MySQL to accept the query, you can use the ANY_VALUE() function: SELECT name, ANY_VALUE(address), MAX(age) FROM t GROUP BY name;

很抱歉,没有使用准确的SQL

我使用这个查询来克服Mysql警告。

SELECT count(*) AS cnt, `regions_id`
FROM regionables 
WHERE `regionable_id` = '115' OR `regionable_id` = '714'
GROUP BY `regions_id`
HAVING cnt > 1

注意我存在的关键

count(*) AS cnt

我只需将group_id添加到GROUP BY。

When SELECTing a column that is not part of the GROUP BY there could be multiple values for that column within the groups, but there will only be space for a single value in the results. So, the database usually needs to be told exactly how to make those multiple values into one value. Commonly, this is done with an aggregate function like COUNT(), SUM(), MAX() etc... I say usually because most other popular database systems insist on this. However, in MySQL prior to version 5.7 the default behaviour has been more forgiving because it will not complain and then arbitrarily choose any value! It also has an ANY_VALUE() function that could be used as another solution to this question if you really needed the same behaviour as before. This flexibility comes at a cost because it is non-deterministic, so I would not recommend it unless you have a very good reason for needing it. MySQL are now turning on the only_full_group_by setting by default for good reasons, so it's best to get used to it and make your queries comply with it.

那么为什么我的答案如此简单呢?我做了几个假设:

1) group_id是唯一的。看起来很合理,毕竟这是一个“ID”。

2) group_name也是唯一的。这可能不是一个合理的假设。如果不是这种情况,您有一些重复的group_name,然后按照我的建议将group_id添加到GROUP BY,您可能会发现现在得到的结果比以前更多,因为具有相同名称的组现在将在结果中有单独的行。对我来说,这比隐藏这些重复的组要好,因为数据库已经悄悄地任意选择了一个值!

当涉及多个表时,使用表名或别名限定所有列也是一种很好的实践……

SELECT 
  g.group_id AS 'value', 
  g.group_name AS 'text' 
FROM mod_users_groups g
LEFT JOIN mod_users_data d ON g.group_id = d.group_id 
WHERE g.active = 1 
  AND g.department_id = 1 
  AND g.manage_work_orders = 1 
  AND g.group_name != 'root' 
  AND g.group_name != 'superuser' 
GROUP BY 
  g.group_name, 
  g.group_id 
HAVING COUNT(d.user_id) > 0 
ORDER BY g.group_name

上面的共识答案很好,但如果在修复my.cnf文件后在存储过程中运行查询时遇到问题,那么请再次尝试加载SPs。

我怀疑MySQL必须用默认的only_full_group_by集来编译sp。因此,即使我改变了my.cnf并重新启动mysqld,它对sp没有任何影响,他们不断失败“选择列表不在GROUP BY子句中,包含非聚合列…它在功能上不依赖于GROUP BY子句中的列;这与sql_mode=only_full_group_by"不兼容。

重新加载sp必须导致它们现在被重新编译,只有_full_group_by被禁用。在那之后,它们似乎像预期的那样工作。