項(xiàng)目場(chǎng)景

有時(shí)候，遇到數(shù)據(jù)庫(kù)重復(fù)數(shù)據(jù)，需要將數(shù)據(jù)進(jìn)行分組，并取出其中一條來(lái)展示，這時(shí)就需要用到group by語(yǔ)句。
但是，如果mysql是高版本，當(dāng)執(zhí)行g(shù)roup by時(shí)，select的字段不屬于group by的字段的話，sql語(yǔ)句就會(huì)報(bào)錯(cuò)。報(bào)錯(cuò)信息如下：

Expression #1 of SELECT list is not in GROUP BY clause and contains
nonaggregated column ‘?dāng)?shù)據(jù)庫(kù)名.表名.字段名’ which is not functionally dependent
on columns in GROUP BY clause; this is incompatible with
sql_mode=only_full_group_by

問題描述

1.表結(jié)構(gòu)

CREATE TABLE `t_iov_help_feedback` (`ID` INT(11) NOT NULL  AUTO_INCREMENT COMMENT '主鍵ID',`USER_ID` INT(255) DEFAULT NULL  COMMENT '用戶ID',`problems` VARCHAR(255) DEFAULT NULL COMMENT '問題描述',`last_updated_date` DATETIME DEFAULT NULL COMMENT '最后更新時(shí)間',PRIMARY KEY (`ID`)
) ENGINE=INNODB DEFAULT CHARSET=utf8;

2.表數(shù)據(jù)

3.sql語(yǔ)句

1）查詢group by的字段（正常）

SELECT USER_ID  FROM  t_iov_help_feedback GROUP BY USER_ID;

SELECT MAX(ID),USER_ID  FROM  t_iov_help_feedback GROUP BY USER_ID;

2）查詢非group by的字段（報(bào)錯(cuò)）

報(bào)錯(cuò)什么意思呢？
一句話概括：“錯(cuò)誤代碼1055與sql_mode = only_full_group_by不兼容”
翻譯：

“錯(cuò)誤代碼：1055。SELECT列表的表達(dá)式＃1不在GROUP BY子句中，并且包含非聚合列’test.t_iov_help_feedback.ID’，它在功能上不依賴于GROUP BY子句中的列; 這與sql_mode = only_full_group_by不兼容”

原因分析

• 一、原理層面
  這個(gè)錯(cuò)誤發(fā)生在mysql 5.7.5 版本及以上版本會(huì)出現(xiàn)的問題：
  mysql 5.7.5版本以上默認(rèn)的sql配置是:sql_mode=“ONLY_FULL_GROUP_BY”，這個(gè)配置嚴(yán)格執(zhí)行了"SQL92標(biāo)準(zhǔn)"。
  很多從5.6升級(jí)到5.7時(shí)，為了語(yǔ)法兼容，大部分都會(huì)選擇調(diào)整sql_mode，使其保持跟5.6一致，為了盡量兼容程序。

• 二、sql層面
  在sql執(zhí)行時(shí)，出現(xiàn)該原因，簡(jiǎn)單來(lái)說(shuō)就是：
  由于開啟了ONLY_FULL_GROUP_BY的設(shè)置，如果select 的字段不在 group by 中，
  并且select 的字段未使用聚合函數(shù)（SUM,AVG,MAX,MIN等）的話，那么這條sql查詢是被mysql認(rèn)為非法的，會(huì)報(bào)錯(cuò)誤…

驗(yàn)證是否此原因：

1.查詢數(shù)據(jù)庫(kù)版本的語(yǔ)句

SELECT VERSION();

可以看到，我這里數(shù)據(jù)庫(kù)版本是：8.0.16，大于5.7.5了

2. 查看sql_mode的語(yǔ)句

select @@GLOBAL.sql_mode;

查詢出來(lái)的值為：

ONLY_FULL_GROUP_BY,STRICT_TRANS_TABLES,NO_ZERO_IN_DATE,NO_ZERO_DATE,ERROR_FOR_DIVISION_BY_ZERO,NO_ENGINE_SUBSTITUTION

可以看到，sql_mode開啟了only_full_group_by 屬性

解決方案

解決方案一：使用函數(shù)ANY_VALUE()包含報(bào)錯(cuò)字段

將上述報(bào)錯(cuò)語(yǔ)句改成：

SELECT ANY_VALUE(ID),USER_ID,ANY_VALUE(problems),ANY_VALUE(last_updated_date) FROM  t_iov_help_feedback GROUP BY USER_ID;

可以看到，結(jié)果能正常查詢了，根據(jù)需要自己改查詢字段的別名就行。

ANY_VALUE()函數(shù)說(shuō)明：

MySQL有any_value(field)函數(shù)，它主要的作用就是抑制ONLY_FULL_GROUP_BY值被拒絕。
這樣sql語(yǔ)句不管是在ONLY_FULL_GROUP_BY模式關(guān)閉狀態(tài)還是在開啟模式都可以正常執(zhí)行，不被mysql拒絕。
any_value()會(huì)選擇被分到同一組的數(shù)據(jù)里第一條數(shù)據(jù)的指定列值作為返回?cái)?shù)據(jù)。
官方有介紹，地址：https://dev.mysql.com/doc/refman/5.7/en/miscellaneous-functions.html#function_any-value

解決方案二：通過(guò)sql語(yǔ)句暫時(shí)性修改sql_mode

去掉ONLY_FULL_GROUP_BY，重新設(shè)置值

SET @@global.sql_mode ='STRICT_TRANS_TABLES,NO_ZERO_IN_DATE,NO_ZERO_DATE,ERROR_FOR_DIVISION_BY_ZERO,NO_ENGINE_SUBSTITUTION';

上面是改變了全局sql_mode，對(duì)于新建的數(shù)據(jù)庫(kù)有效。對(duì)于已存在的數(shù)據(jù)庫(kù)，則需要在對(duì)應(yīng)的數(shù)據(jù)庫(kù)下執(zhí)行：

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

問題：

重啟mysql數(shù)據(jù)庫(kù)服務(wù)之后，ONLY_FULL_GROUP_BY還會(huì)出現(xiàn)，所以這只是暫時(shí)性的。

備注：
網(wǎng)上有些朋友提供的sql語(yǔ)句如下：

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

但是卻執(zhí)行不了，報(bào)sql語(yǔ)法錯(cuò)誤：

這時(shí)只需要加上單引號(hào)即可：

但是，添加了單引號(hào)仍然報(bào)錯(cuò)：

這里說(shuō)sql_mode不能設(shè)置NO_AUTO_CREATE_USER這個(gè)值，那直接去掉這個(gè)值就行了唄，也就是上面我提供的值。

解決方案三：通過(guò)配置文件永久修改sql_mode

mysql安裝在服務(wù)器上和安裝在本地，修改配置文件的方式有點(diǎn)區(qū)別。

1、Linux下修改配置文件

1）登錄進(jìn)入MySQL
使用命令 mysql -u username -p 進(jìn)行登陸，然后輸入密碼，輸入SQL：

show variables like ‘%sql_mode’;

2）編輯my.cnf文件
文件地址一般在：/etc/my.cnf，/etc/mysql/my.cnf
使用vim命令編輯文件，不知道vim命令怎么使用的，可以參考我的另外篇文章：Linux中使用vi工具進(jìn)行文本編輯
找到sql-mode的位置，去掉ONLY_FULL_GROUP_BY
然后重啟MySQL；
有的my.cnf中可能沒有sql-mode，需要追加：

sql-mode=STRICT_TRANS_TABLES,NO_ZERO_IN_DATE,NO_ZERO_DATE,ERROR_FOR_DIVISION_BY_ZERO,NO_AUTO_CREATE_USER,NO_ENGINE_SUBSTITUTION

注意要加入到[mysqld]下面，如加入到其他地方，重啟后也不生效，具體的如下圖：

3）修改成功后重啟MySQL服務(wù)

service mysql restart

重啟好后，再登錄mysql，輸入SQL：show variables like ‘%sql_mode’; 如果沒有ONLY_FULL_GROUP_BY，就說(shuō)明已經(jīng)成功了。

如果還不行，那么只保留STRICT_TRANS_TABLES,NO_ENGINE_SUBSTITUTION 即可
追加內(nèi)容為：

sql-mode=STRICT_TRANS_TABLES,NO_ENGINE_SUBSTITUTION

2、window下修改配置文件

1）找到mysql安裝目錄，用記事本直接打開my.ini文件

2）編輯my.ini文件，在[mysql]標(biāo)簽下追加內(nèi)容

sql_mode=STRICT_TRANS_TABLES,NO_ZERO_IN_DATE,NO_ZERO_DATE,ERROR_FOR_DIVISION_BY_ZERO,NO_ENGINE_SUBSTITUTION

3）重啟mysql 服務(wù)

備注：
網(wǎng)上有些提供了sql_mode的值，卻導(dǎo)致重啟mysql服務(wù)啟動(dòng)不了

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

這時(shí)，只需要將sql_mode 值中 “NO_AUTO_CREATE_USER” 這個(gè)屬性去掉即可。