mysql#1055




在MySql中執行查詢時與only_full_group_by相關的錯誤 (10)

我已經升級了我的系統,並為我正在處理的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不兼容

請注意有關 服務器SQL模式 主題的Mysql 5.7的手冊頁。

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

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)


如果您不想在當前查詢中進行任何更改,請按照以下步驟操作 -

  1. 流浪漢ssh到你的盒子裡
  2. 鍵入: sudo vim /etc/mysql/my.cnf
  3. 滾動到文件底部,然後鍵入 A 進入插入模式
  4. 複製和粘貼

    [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. 輸入 esc 退出輸入模式

  6. 鍵入 :wq 以保存並關閉vim。
  7. 輸入 sudo service mysql restart 重啟MySQL。

如果您正在使用wamp 3.0.6或除穩定版2.5之外的任何高級版本,您可能會遇到此問題,首先問題是sql。 你必須相應地命名字段。 但還有另一種方法可以解決它。 點擊wamp的綠色圖標。 mysql-> mysql settings-> sql_mode-> none。 或者從控制台,您可以更改默認值。

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';

對於localhost / wampserver 3,我們可以設置sql-mode = user_mode來刪除此錯誤:

click on wamp icon -> MySql -> MySql Setting -> sql-mode -> user_mode

然後重啟wamp或apache


您可以向 group_id 添加 unique index ; 如果你確定 group_id 是唯一的。

它可以在不修改查詢的情況下解決您的問題

答案很晚,但答案中還沒有提到。 也許它應該完成已經全面的答案。 至少它確實解決了我的情況,當我不得不拆分一個有太多字段的表。


您可以嘗試通過執行以下操作來禁用 only_full_group_by 設置:

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';

MySQL 8不接受 NO_AUTO_CREATE_USER 因此需要刪除。


我只想將 group_id 添加到 GROUP BY

SELECT 不屬於 GROUP BY 列時,組中的該列可能有多個值,但結果中只有一個值的空間。 因此, 通常 需要告知數據庫如何將這些多個值組合成一個值。 通常,這是通過像 COUNT()SUM()MAX() 等聚合函數來完成的......我 通常會 說,因為大多數其他流行的數據庫系統都堅持這一點。 但是,在版本5.7之前的MySQL中,默認行為更寬容,因為它不會抱怨然後隨意選擇 任何值 ! 它還有一個 ANY_VALUE() 函數,如果你真的需要和以前一樣的行為,它可以用作這個問題的另一個解決方案。 這種靈活性是有代價的,因為它是非確定性的,所以除非你有充分的理由需要它,否則我不推薦它。 MySQL現在默認 only_full_group_by 設置是有充分理由的,所以最好習慣它並讓你的查詢符合它。

那我為什麼簡單回答呢? 我做了幾個假設:

1) group_id 是唯一的。 看似合理,畢竟它是一個'ID'。

2) group_name 也是唯一的。 這可能不是一個合理的假設。 如果不是這種情況並且您有一些重複的 group_names ,然後您按照我的建議將 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

我在laravel宅基地上使用Laravel 5.3,mysql 5.7.12(我相信0.5.0)

即使在明確設置編輯 /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

我還是收到了錯誤。

我不得不將 config/database.phptrue 更改為 false

    'mysql' => [
        'strict' => false, //behave like 5.6
        //'strict' => true //behave like 5.7
    ], 

進一步閱讀:

https://laracasts.com/discuss/channels/servers/set-set-sql-mode-on-homestead https://mattstauffer.co/blog/strict-mode-and-other-mysql-customizations-in-laravel-5-2


抱歉沒有使用您的確切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

這有助於我理解整個問題:

  1. https://.com/a/20074634/1066234
  2. 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,userid,timestamp,questionid,answerid,SUM(correct)as correct,elapsed,ipaddress FROM gameplay WHERE timestamp> = DATE_SUB (NOW(),INTERVAL 1 DAY)和userid = 1

在這種情況下,GROUP BY中缺少 表達式#4





mysql-error-1055