我已经升级了我的系统，并为我正在开发的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选项，或者还有其他需要做的事情?
使用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;
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
这有助于我理解整个问题:
https://stackoverflow.com/a/20074634/1066234
https://dev.mysql.com/doc/refman/5.7/en/group-by-handling.html
下面是另一个问题查询的例子。
问题:
SELECT COUNT(*) as attempts, SUM(elapsed) as elapsedtotal, userid, timestamp, questionid, answerid, SUM(correct) as correct, elapsed, ipaddress FROM `gameplay`
WHERE timestamp >= DATE_SUB(NOW(), INTERVAL 1 DAY)
AND cookieid = #
通过在最后加上这个来解决:
GROUP BY timestamp, userid, cookieid, questionid, answerid, elapsed, ipaddress
注意:查看PHP中的错误消息，它会告诉您问题出在哪里。
例子:
MySQL查询错误1140:在没有GROUP BY的聚合查询中，SELECT列表中的表达式#4包含非聚合列'db.gameplay.timestamp';这与sql_mode=only_full_group_by不兼容-查询:SELECT COUNT(*) as attempts, SUM(elapsed) as elapsedtotal，用户id，时间戳，questionid, answerid, SUM(correct) as correct, elapsed, ipaddress FROM gameplay
WHERE时间戳>= DATE_SUB(NOW()，间隔1天)
AND userid = 1
在本例中，表达式#4在GROUP BY中缺失。
