一、 MySQL版本

• MySQL版本：8.0.11

二、 問題描述

1. 問題描述

• 在查詢時使用group by語句，出現(xiàn)錯誤代碼：1055；
• 執(zhí)行發(fā)生錯誤語句：

SELECT
  t1.id,
  t1.`room_name`,
  t2.`room_id`,
  t2.`name`
FROM
  room t1
  LEFT JOIN person t2
  ON t1.id = t2.room_id 
GROUP BY t1.id

結果：

• 上圖說 select表達式#4-t2.name不是聚合函數(shù)列，沒有依賴group by 子句中的列，所以導致報錯

2.ONLY_FULL_GROUP_BY-SQL示例

• 在MySQL5.7之后sql_mode中默認存在ONLY_FULL_GROUP_BY，SQL語句未通過ONLY_FULL_GROUP_BY語義檢查所以報錯。
• ONLY_FULL_GROUP_BY：要求select語句中所查詢出的列必須是在group by中進行聲明，否則就會報錯。簡單的說來，在此模式下，select target list中的值要么是來自于聚合函數(shù)（sum、avg、max等）的結果，要么是來自于group by list中的表達式的值。

-- 執(zhí)行成功，因為select語句中的count()是聚集函數(shù)；
SELECT
   COUNT(1)
FROM
   room t1
   LEFT JOIN person t2 ON t1.id = t2.room_id
GROUP BY 
   t2.name;

-- 執(zhí)行失敗，因為*中包含主鍵id，而group by后的表達式中并沒有包含兩張表中的id
SELECT
	* 
FROM
	room t1
	LEFT JOIN person t2 ON t1.id = t2.room_id 
GROUP BY
	t2.NAME
	
-- 執(zhí)行成功，將上句SQL改為如下：
SELECT
	* 
FROM
	room t1
	LEFT JOIN person t2 ON t1.id = t2.room_id 
GROUP BY
	t1.id,
	t2.id

-- 執(zhí)行成功，因為t2.name包含在group by后的表達式中   
SELECT
	t2.NAME 
FROM
	room t1
	LEFT JOIN person t2 ON t1.id = t2.room_id 
GROUP BY
	t2.NAME

-- 該SQL執(zhí)行失敗，因為contact沒有包含在group by后的表達式中
SELECT
  t2.name,
  t2.room_id
FROM
  room t1
  LEFT JOIN person t2
    ON t1.id = t2.room_id
GROUP BY t2.name
    

三、解決辦法

1.方法一

這種方法不需要修改任何配置文件，給不需要分組的字段上加上 any_value() 函數(shù)即可。

any_value：選擇被分到同一組數(shù)據(jù)中的第一條數(shù)據(jù)的指定列值作為返回數(shù)據(jù)。

示例：

SELECT
  t2.name,
  any_value(t2.room_id) AS room_id
FROM
  room t1
  LEFT JOIN person t2
    ON t1.id = t2.room_id
GROUP BY 
	t2.name

當然，這種對于已經開發(fā)了不少功能的項目不太合適，畢竟要把原來的sql都給修改一遍

2.方法二（臨時）

這種方法只能暫時解決1055錯誤，當MySQL服務進行重啟后就會失效，又得重新設置；

查詢出所有的 sql_mode；

select @@sql_mode

然后將查詢結果中的ONLY_FULL_GROUP_BY移除后，再重新設置sql_model；

SET sql_mode = 'STRICT_TRANS_TABLES,NO_ZERO_IN_DATE,NO_ZERO_DATE,ERROR_FOR_DIVISION_BY_ZERO,NO_ENGINE_SUBSTITUTION'

• 操作執(zhí)行完成后就OK了

3.方法三（永久）

• 直接通過修改MySQL的my.ini文件（如果是Linux則在my.cnf中），這樣就算MySQL服務重啟后也不會失效；

在my.ini文件中添加：

# 設置sql_mode,關閉ONLY_FULL_GROUP_BY,避免使用group by函數(shù)導致1055錯誤
sql_mode=STRICT_TRANS_TABLES,NO_ZERO_IN_DATE,NO_ZERO_DATE,ERROR_FOR_DIVISION_BY_ZERO,NO_ENGINE_SUBSTITUTION

重啟MySQL服務就OK，以管理員身份打開cmd窗口輸入命令（不然可能會拒絕訪問）；

## 關閉mysql服務
net stop mysql 
## 開啟mysql服務
net start mysql

四、sql_mode常用值

1. ONLY_FULL_GROUP_BY：在SELECT中的列，沒有在GROUP BY中出現(xiàn)，那么將認為這個SQL是不合法的，因為列不在GROUP BY從句中，即只能展示group by的字段，其他均都要報1055的錯；
2. STRICT_TRANS_TABLES：在該模式下，如果一個值不能插入到一個事務表中，則中斷當前的操作，對非事務表不做限制；
3. NO_AUTO_VALUE_ON_ZERO：影響自增長列的插入。默認設置下，插入0或NULL代表生成下一個自增長值。如果用戶希望插入的值為0，而該列又是自增長的，那么這個選項就有用了。
4. NO_ZERO_IN_DATE： 在嚴格模式下，不接受月或日部分為0的日期。如果使用IGNORE選項，我們?yōu)轭愃频娜掌诓迦?rsquo;0000-00-00’。在非嚴格模式，可以接受該日期，但會生成警告。
5. NO_ZERO_DATE：在嚴格模式下，mysql數(shù)據(jù)庫不允許插入零日期。它實際的行為受到 strictmode是否開啟的影響2。
6. ERROR_FOR_DIVISION_BY_ZERO：在INSERT或UPDATE過程中，如果數(shù)據(jù)被零除，則產生錯誤而非警告。如果未給出該模式，那么數(shù)據(jù)被零除時MySQL返回NULL
7. NO_AUTO_CREATE_USER：禁止GRANT創(chuàng)建密碼為空的用戶
8. NO_ENGINE_SUBSTITUTION：如果需要的存儲引擎被禁用或未編譯，那么拋出錯誤。不設置此值時，用默認的存儲引擎替代，并拋出一個異常
9. PIPES_AS_CONCAT：將”||”視為字符串的連接操作符而非或運算符，這和Oracle數(shù)據(jù)庫是一樣的，也和字符串的拼接函數(shù)Concat相類似
10. ANSI_QUOTES：啟用ANSI_QUOTES后，不能用雙引號來引用字符串，因為它被解釋為識別符

以上就是MySQL出現(xiàn)錯誤代碼：1055的三種解決方案(推薦！)的詳細內容，更多關于MySQL錯誤代碼1055的資料請關注腳本之家其它相關文章！

最新評論