問題描述

我已經升級了我的系統并為我正在開發的 Web 應用程序安裝了帶有 php 的 MySql 5.7.9.我有一個動態創建的查詢，當在舊版本的 MySql 中運行時，它工作正常.升級到 5.7 后，我收到此錯誤:

I have upgraded my system and have installed MySql 5.7.9 with php for a web application I am working on. I have a query that is dynamically created, and when run in older versions of MySql it works fine. Since upgrading to 5.7 I get this error:

SELECT 列表的表達式 #1 不在 GROUP BY 子句中并且包含非聚合列support_desk.mod_users_groups.group_id"是在功能上不依賴于 GROUP BY 子句中的列；這是與 sql_mode=only_full_group_by 不兼容

Expression #1 of SELECT list is not in GROUP BY clause and contains nonaggregated column 'support_desk.mod_users_groups.group_id' which is not functionally dependent on columns in GROUP BY clause; this is incompatible with sql_mode=only_full_group_by

注意關于服務器SQL模式.

這是給我帶來麻煩的查詢:

This is the query that is giving me trouble:

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 選項，還是我需要做其他事情?

I did some googling on the issue, but I don't understand only_full_group_by enough to figure out what I need to do to fix the query. Can I just turn off the only_full_group_by option, or is there something else I need to do?

如果您需要更多信息，請告訴我.

Let me know if you need more information.

推薦答案

我只想將 group_id 添加到 GROUP BY.

I would just add group_id to the GROUP BY.

當 SELECT 不屬于 GROUP BY 的列時，組內該列可能有多個值，但只有一個空間結果中的單個值.因此，數據庫通常需要被告知如何將這些多個值變成一個值.通常，這是通過一個聚合函數來完成的，比如 COUNT()、SUM()、MAX() 等等......我說通常因為大多數其他流行的數據庫系統都堅持這一點.但是，在 MySQL 5.7 之前的版本中，默認行為更加寬容，因為它不會抱怨然后任意選擇任何值！它還有一個 ANY_VALUE() 函數，如果你真的需要和以前一樣的行為，它可以用作這個問題的另一種解決方案.這種靈活性是有代價的，因為它是不確定的，所以除非你有充分的理由需要它，否則我不會推薦它.MySQL 現在默認打開 only_full_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.

那為什么我上面的簡單回答是?我做了幾個假設:

So why my simple answer above? I've made a couple of assumptions:

1) group_id 是唯一的.看起來有道理，畢竟是一個ID".

1) the group_id is unique. Seems reasonable, it is an 'ID' after all.

2) group_name 也是唯一的.這可能不是一個合理的假設.如果不是這種情況，并且您有一些重復的 group_names，然后您按照我的建議將 group_id 添加到 GROUP BY，您可能會發現您現在可以獲得比以前更多的結果，因為具有相同名稱的組現在將在結果中具有單獨的行.對我來說，這比隱藏這些重復組要好，因為數據庫已經悄悄地任意選擇了一個值！

2) the group_name is also unique. This may not be such a reasonable assumption. If this is not the case and you have some duplicate group_names and you then follow my advice to add group_id to the GROUP BY, you may find that you now get more results than before because the groups with the same name will now have separate rows in the results. To me, this would be better than having these duplicate groups hidden because the database has quietly selected a value arbitrarily!

當涉及多個表時，用它們的表名或別名來限定所有列也是一種很好的做法......

It's also good practice to qualify all the columns with their table name or alias when there's more than one table involved...

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

這篇關于在 MySql 中執行查詢時與 only_full_group_by 相關的錯誤的文章就介紹到這了，希望我們推薦的答案對大家有所幫助，也希望大家多多支持html5模板網！