MYSQL，GROUP BY子句；这与sql_mode=only_full_group_by不兼容。

19 浏览
0 Comments

MYSQL，GROUP BY子句；这与sql_mode=only_full_group_by不兼容。

下面的CodeIgniter查询出现错误，提示：\n

\nSELECT列表中的表达式＃22不在GROUP BY子句中，并且包含非聚合列\'hw3.1.hw_homework.id\'，该列在GROUP BY子句中的列上不具有功能依赖性；这与sql_mode=only_full_group_by不兼容。\n

SELECT *，`studentid`，COUNT（studentid），
`be_user_profiles`。`first_name`，`be_user_profiles`。`last_name`
FROM `be_user_profiles`
JOIN `be_users` ON `be_users`。`id` = `be_user_profiles`。`user_id`
JOIN `hw_homework` ON `be_user_profiles`。`user_id` = `hw_homework`。`studentid`
WHERE `be_user_profiles`。`advisor` ='20'
AND `hw_homework`。`date` <'2018-06-15 00:00:00'
AND `hw_homework`。`date` >'2017-08-24 00:00:00'
AND `active` = 1
GROUP BY `be_user_profiles`。`user_id`
ORDER BY COUNT（studentid）DESC

\n文件名：modules / organization / models / Mhomework.php\n行号：226\n

\n

$ this-> db-> select（'*, studentid，COUNT（studentid），be_user_profiles.first_name，be_user_profiles.last_name'）;
$ this-> db-> from（'be_user_profiles'）;
$ this-> db-> join（'be_users'，'be_users.id = be_user_profiles.user_id'）;
$ this-> db-> join（'hw_homework'，'be_user_profiles.user_id = hw_homework.studentid'）;
$ this-> db-> where（'be_user_profiles.advisor'，$ id）;
$ this-> db-> where（'hw_homework.date<'，$ to）;
$ this-> db-> where（'hw_homework.date>'，$ from）;
$ this-> db-> where（'active'，1）;
$ this-> db-> group_by（'be_user_profiles.user_id'）;
$ this-> db-> order_by（'COUNT（studentid）'，'DESC'）;
$ query = $ this-> db-> get（）;

\n我删除了studentid或添加了group_by studentid等，但它们都不起作用。\n我知道我可以设置全局SQL模式，但我认为这对我来说不是一个解决方案。\n

mysql> set global sql_mode='STRICT_TRANS_TABLES,NO_ZERO_IN_DATE,NO_ZERO_DATE,ERROR_FOR_DIVISION_BY_ZERO,NO_AUTO_CREATE_USER,NO_ENGINE_SUBSTITUTION';
mysql> set session sql_mode='STRICT_TRANS_TABLES,NO_ZERO_IN_DATE,NO_ZERO_DATE,ERROR_FOR_DIVISION_BY_ZERO,NO_AUTO_CREATE_USER,NO_ENGINE_SUBSTITUTION';

\n我想修复代码而不是解决方法。

0

1 答案

0 Comments

MYSQL中，当使用GROUP BY子句时，与sql_mode=only_full_group_by不兼容的问题产生的原因是，标准的SQL要求在SELECT语句中选择的列也必须在GROUP BY子句中出现，除非选择的列的值在GROUP BY子句中已经被函数依赖。MySQL对GROUP BY有一个非标准的扩展，允许选择在GROUP BY子句中没有提到的列，并返回一些不可预测的值。

为了解决这个问题，需要删除*号，具体操作如下：将$this->db->select('*,studentid,COUNT(studentid),be_user_profiles.first_name,be_user_profiles.last_name');修改为$this->db->select('studentid,COUNT(studentid),be_user_profiles.first_name,be_user_profiles.last_name');，将$this->db->group_by('be_user_profiles.user_id');修改为$this->db->group_by('studentid,be_user_profiles.user_id');。

"Cannot SELECT *"是错误的，你误解了问题。正确的做法是删除select *，建议的group by子句依赖于be_user_profiles.first_name和be_user_profiles.last_name与be_user_profiles.user_id之间的函数依赖关系（如果成立的话），这是允许的（尽管不是所有数据库都实现了标准的这个相对较新的部分）。

“By_Already Read what they wrote. I repeat, "Cannot SELECT *" is wrong and the answer misrepresents the problem. What is correct is, you can select columns that are grouped by or functionally dependent on those columns & you can use columns as agregate operands. In this example one cannot use select *. If they said that then they wouldn't be wrong about select *, they'd still be misleading in "You must enumerate the columns you want" (since enumeration is not the issue) and they'd still be lacking the general case. And they shouldn't have answered this chronic faq.”

“By_Already Clearly, if & only if * is only "columns that are grouped by or functionally dependent on those columns". Eg if & only if you grouped on a superset of a CK--eg PK or UNIQUE NOT NULL--since all columns are functionally dependent on such a set of columns. But rather, you should not claim "In standard SQL you cannot use SELECT * in a query with GROUP BY" unless you can show that it follows from the rules--which you cannot, because it does not. Moreover saying it still wouldn't give the rules but merely one consequence of them. PS I said to read them, not that you hadn't.”

“thank you, I now understand the point you made.”

“What is "for best results" supposed to mean? What you suggest cannot make any change to the results, since studentid = be_user_profiles.user_id, and isn't good practice either.”

以上是对MYSQL中出现(MYSQL, GROUP BY clause; this is incompatible with sql_mode=only_full_group_by)问题的原因和解决方法的整理。

0