一、 MySQL版本

• MySQL版本：8.0.11

二、 問題描述

1. 問題描述

• 在查詢時(shí)使用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

結(jié)果：

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

2.ONLY_FULL_GROUP_BY-SQL示例

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

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

-- 執(zhí)行失敗，因?yàn)?中包含主鍵id，而group by后的表達(dá)式中并沒有包含兩張表中的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í)行成功，因?yàn)閠2.name包含在group by后的表達(dá)式中   
SELECT
	t2.NAME 
FROM
	room t1
	LEFT JOIN person t2 ON t1.id = t2.room_id 
GROUP BY
	t2.NAME

-- 該SQL執(zhí)行失敗，因?yàn)閏ontact沒有包含在group by后的表達(dá)式中
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ù)的指定列值作為返回?cái)?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

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

2.方法二（臨時(shí)）

這種方法只能暫時(shí)解決1055錯誤，當(dāng)MySQL服務(wù)進(jìn)行重啟后就會失效，又得重新設(shè)置；

查詢出所有的 sql_mode；

select @@sql_mode

然后將查詢結(jié)果中的ONLY_FULL_GROUP_BY移除后，再重新設(shè)置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服務(wù)重啟后也不會失效；

在my.ini文件中添加：

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

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

## 關(guān)閉mysql服務(wù)
net stop mysql 
## 開啟mysql服務(wù)
net start mysql

四、sql_mode常用值

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

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

最新評論