? ? ? ? ? ? ?Mysql only_full_group_by及Error 1055問題分析圖解

聲明

本文的數(shù)據(jù)來自網(wǎng)絡(luò)，部分代碼也有所參照，這里做了注釋和延伸，旨在技術(shù)交流，如有冒犯之處請聯(lián)系博主及時處理。

注： 1?員工表結(jié)構(gòu)和數(shù)據(jù)初始化SQL腳本

? ? ? ? 2??當前驗證環(huán)境CentOS 8 X86 64、mysql 8.0.23、SQL Server 2019

問題描述

ERROR 1055 (42000): Expression #1 of SELECT list is not in GROUP BY clause and contains nonaggregated column 'trial.B.dname' which is not functionally dependent on columns in GROUP BY clause; this is incompatible with sql_mode=only_full_group_by

編寫SQL時遇到如下錯誤，即出現(xiàn)錯誤 ERROR 1055，SELECT列表不在GROUP BY語句內(nèi)且存在不函數(shù)依賴GROUP BY語句的非聚合字段'trial.B.dname',這是和sql_mode=only_full_group_by不兼容的（即不支持）。

解決方法

Way 1：臨時關(guān)閉only_full_group_by模式，這種方法通過修改系統(tǒng)變量，重啟數(shù)據(jù)庫后失效(修改后需要新開會話或者窗口)。首先查看下當前的sql_mode：

# 查看mysql當前的sql mode show VARIABLES LIKE 'sql_mode';

-- 1 對系統(tǒng)標量進行全局設(shè)置，但數(shù)據(jù)庫重啟后失效 set global sql_mode='STRICT_TRANS_TABLES,NO_ZERO_IN_DATE,NO_ZERO_DATE,ERROR_FOR_DIVISION_BY_ZERO,NO_ENGINE_SUBSTITUTION'; -- 2 修改后需在新的回話(窗口)里驗證原SQL。

Way 2： 永久關(guān)閉only_full_group_by模式，這種方法需要在mysql的配置文件里修改，然后重啟。

Step 1 找到配置文件/etc/my.cnf(或則關(guān)聯(lián)文件夾找到mysql-server.cnf)

Step 2: 在上述文件內(nèi)的[mysqld]后追加

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

Step 3:保存配置文件后，重啟Mysql即可。

大話group by

啥，這就這么結(jié)束？！來來來，別走，我們來拉拉mysql的groupby和sql_mode only_full_group_by模式。開始之前我們得來先做個前期回顧。

SQL 92 group by

首先我們先了解下SQL92標準里關(guān)于group by的定義。

SQL-92?and?earlier?does?not?permit?queries?for?which?the?select?list,?HAVING?condition,?or?ORDER?BY?list?refer?to?nonaggregated?columns?that?are?not?named?in?the?GROUP?BY?clause.

簡單的說：SQL-92里 SELECT、HAVING、ORDER后的非聚合字段必須和GROUP BY后的字段保持完全一致。

來個例子瞧瞧唄？如下以sql server2019為例：

--正確的”姿勢” SELECT B.dname,B.deptno,MAX(sal) FROM emp A JOIN dept B ON A.deptno = B.deptno GROUP BY B.deptno,B.dname;--錯誤的“姿勢” SELECT B.dname,B.deptno,MAX(sal) FROM emp A JOIN dept B ON A.deptno = B.deptno GROUP BY B.deptno;

SQL 99 group by

但是我們經(jīng)常看到MYSQL的SELECT列表的字段并不在GROUP BY后，這又是咋回事？話不多說，先上幾個案例。

-- Msyql: -- Case 1（此腳本在sql server里報錯） SELECT B.deptno,B.dname,MAX(sal) FROM emp A JOIN dept B ON A.deptno = B.deptno GROUP BY B.deptno;

但為什么mysql就能支持呢？

SQL:1999 and later permits such nonaggregates per optional feature T301 if they are functionally dependent on?GROUP BY?columns: If such a relationship exists between?name?and?custid, the query is legal. This would be the case, for example, were?custid?a primary key of?customers.

SQL 99登場，這里即是定義了新的標準，如果group by后面的字段是主鍵（唯一鍵），而且非聚合字段是函數(shù)依賴group by后字段的，那么可以將這些非聚合字段放在SELECT、HAVING、ORDER BY的語句之后。

MySQL implements detection of functional dependence. If the?ONLY_FULL_GROUP_BY?SQL mode is enabled (which it is by default), MySQL rejects queries for which the select list,?HAVING?condition, or?ORDER BY?list refer to nonaggregated columns that are neither named in the?GROUP BY?clause nor are functionally dependent on them.

Mysql 實現(xiàn)了這種檢測函數(shù)依賴，這時ONLY_FULL_GROUP_BY?SQL 模式登場，mysql里SQL mode設(shè)置了這種模式那么如果 group by后的不補全字段或是無函數(shù)依賴的字段時非聚合字段放在SELECT、HAVING、ORDER BY的語句之后是不支持的。

有點繞，我們直接開啟個案例來說明ONLY_FULL_GROUP_BY有何魔力。

啟用only_full_group_by模式

-- Case 2： SELECT B.dname,MAX(sal) FROM emp A JOIN dept B ON A.deptno = B.deptno GROUP BY B.loc

原因分析，這里group by后的字段loc并沒有定義為主鍵或則唯一鍵，所以在sql mode是ONLY_FULL_GROUP_BY模式下報錯（即不支持）。

我們再來個簡單些的例子，即只涉及一張表。

-- Case 3： --Step 1 創(chuàng)建一張無主鍵、唯一鍵的表，插入4條記錄(id字段的值不重復(fù))。 CREATE TABLE `test_04251019` (`id` int DEFAULT NULL,`name` varchar(10) DEFAULT NULL,`addr` varchar(12) DEFAULT NULL,`memo` varchar(40) DEFAULT NULL ) ENGINE=InnoDB DEFAULT CHARSET=utf8;-- Step 2 插入4條記錄 INSERT INTO `test_04251019` VALUES ('1', 'hanmeimie', 'Guangzhou', 'Fullbakup'); INSERT INTO `test_04251019` VALUES ('2', 'lilei', 'Guangzhou', 'in increament'); INSERT INTO `test_04251019` VALUES ('4', 'Tom', 'Shanghai', 'no more'); INSERT INTO `test_04251019` VALUES ('5', 'John', 'Shanghai', 'no more_5');-- Step 3 此時我們仿照Case 2寫個簡單點的聚合語句 SELECT name, MAX(LENGTH(addr)) FROM test_04251019 GROUP BY id;

-- Case 4： -- Step 1 創(chuàng)建個表類似(test_04251019),但是id定義為主鍵 CREATE TABLE `test_04251019_key` (`id` int NOT NULL,`name` varchar(10) DEFAULT NULL,`addr` varchar(12) DEFAULT NULL,`memo` varchar(40) DEFAULT NULL, PRIMARY KEY (`id`) ) ENGINE=InnoDB AUTO_INCREMENT=1 DEFAULT CHARSET=utf8;-- Step 2 插入數(shù)據(jù)（直接拷貝至test_04251019） INSERT INTO test_04251019_key(id,name,addr,memo) SELECT id,name,addr,memo FROM test_04251019; COMMIT;-- Step 3 新增個唯一字段id_2并更新數(shù)據(jù) ALTER TABLE test_04251019_key ADD id_2 int NOT NULL; UPDATE test_04251019_key SET id_2 = 100+id; ALTER TABLE test_04251019_key ADD CONSTRAINT unique_id_2 UNIQUE(id_2);-- Step 4 驗證group by后含主鍵、唯一鍵，非聚合字段并存的情況 SELECT name, MAX(LENGTH(addr)) FROM test_04251019_key GROUP BY id; SELECT name, MAX(LENGTH(addr)) FROM test_04251019_key GROUP BY id_2;

關(guān)閉only_full_group_by模式

一眼不合，我們就關(guān)閉only_full_group_by模式。那么我們就來關(guān)閉,這里通過修改系統(tǒng)變量的方式。

-- 通過修改系統(tǒng)變量方式，注意修改后需要在新的回話(重開個客戶端)里驗證 set global sql_mode='STRICT_TRANS_TABLES,NO_ZERO_IN_DATE,NO_ZERO_DATE,ERROR_FOR_DIVISION_BY_ZERO,NO_ENGINE_SUBSTITUTION';show variables like 'sql_mode';

這時我們來驗證Case 2

?Case 3

Case 4:追加案例，針對case 3，如果我們往test_04251019里插入一條記錄

-- 插入一條數(shù)據(jù)（這里id是重復(fù)的，已有等于5的了） INSERT INTO test_04251019 VALUES(5,'Jim','Shanghai2','no more_5_2');-- 再執(zhí)行聚合函數(shù)驗證 SELECT name, MAX(LENGTH(addr)) FROM test_04251019 GROUP BY id;

what？“Jim”那條記錄對應(yīng)的GROUP BY統(tǒng)計去哪兒了？為啥不能是我？！

延展閱讀

ONLY_FULL_GROUP_BY下的特例（僅有聚合函數(shù)和WHERE，這里WHERE是必選項），詳見如下截圖：

總結(jié)

敲黑板

Mysql里的sql mode=only_full_group_by模式是對SQL92 group by的擴展，即對應(yīng)SQL1999的標準。

SQL92 group by里要求SELECT、HAVING、ORDER BY語句后的字段需要與GROUP BY后的字段嚴格一致。

Myslq的sql mode=only_full_group_by模式SELECT、HAVING、ORDER BY語句后的字段可以不跟GROUP BY后的字段嚴格一致，但是GROUP BY的字段需是主鍵或唯一鍵時可以寫非聚合字段。

Myslq的sql mode非only_full_group_by模式時在表的定義不嚴格的情況下（如無明確的主鍵且數(shù)據(jù)重復(fù)時作為GROUP BY后的字段 ）執(zhí)行結(jié)果難以解釋。

更多詳細細節(jié)，詳見官網(wǎng)Mannul。

Any question let me know.

總結(jié)

以上是生活随笔為你收集整理的一文带你了解mysql sql model的only_full_group_by模式含Error 1055问题分析的全部內(nèi)容，希望文章能夠幫你解決所遇到的問題。

如果覺得生活随笔網(wǎng)站內(nèi)容還不錯，歡迎將生活随笔推薦給好友。