前言
在使用mysql是在navicat中查詢一條簡(jiǎn)單語句使用了select * from table雖然查詢成功了,但是報(bào)了
[Err] 1055 - Expression #1 of ORDER BY clause is not in GROUP BY clause and contains nonaggregated column 'information_schema.PROFILING.SEQ' which is not functionally dependent on columns in GROUP BY clause; this is incompatible with sql_mode=only_full_group_by錯(cuò)誤.
錯(cuò)誤原因
在MySQL5.7之后，sql_mode中默認(rèn)存在ONLY_FULL_GROUP_BY，SQL語句未通過ONLY_FULL_GROUP_BY語義檢查所以報(bào)錯(cuò)俭正。
ONLY_FULL_GROUP_BY模式要求select語句中查詢出來的列必須是明確的（其他語句也是一樣）可霎。
以SQL語句select columes from table group by list為例：columns必須是聚集函數(shù)或者在group by后的表達(dá)式list中蒋歌，并且list中必須包含主鍵惫谤，否則也會(huì)報(bào)錯(cuò)肘习。
insert喜鼓、update副砍、delete語句都會(huì)報(bào)錯(cuò)（但不影響SQL語句的執(zhí)行），因?yàn)檫@三種語句執(zhí)行之前也會(huì)執(zhí)行查詢操作庄岖。
以主鍵為id的表為例：
#該SQL執(zhí)行成功豁翎，因?yàn)閏ount是聚集函數(shù)
SELECT count(1) FROM customer GROUP BY `name`;
#該SQL執(zhí)行失敗，因?yàn)?中包含主鍵id隅忿，而group by后的表達(dá)式中并沒有包含id
SELECT * FROM customer GROUP BY `name`
#該SQL執(zhí)行成功心剥，因?yàn)閚ame包含在group by后的表達(dá)式中
SELECT name FROM customer GROUP BY `name`
#該SQL執(zhí)行失敗，因?yàn)閏ontact沒有包含在group by后的表達(dá)式中
SELECT name, contact FROM customer GROUP BY `name`
- 查詢mysql版本
mysql> select @@version;
+-----------------------------+
| @@version |
+-----------------------------+
| 5.7.28-0ubuntu0.18.04.4-log |
+-----------------------------+
1 row in set (0.00 sec)
- 查詢sql_mode模式
mysql> show VARIABLES LIKE '%sql_mode%';
+---------------+------------------------------------------------------------------------------------------------------------------------+
| Variable_name | Value |
+---------------+------------------------------------------------------------------------------------------------------------------------+
| sql_mode | STRICT_TRANS_TABLES,NO_ZERO_IN_DATE,NO_ZERO_DATE,ERROR_FOR_DIVISION_BY_ZERO,NO_AUTO_CREATE_USER,NO_ENGINE_SUBSTITUTION |
+---------------+------------------------------------------------------------------------------------------------------------------------+
1 row in set (0.01 sec)
解決方案
1. 臨時(shí)解決
執(zhí)行sql語句使sql_mode的模式去掉ONLY_FULL_GROUP_BY,但是重啟服務(wù)會(huì)失效
SET sql_mode=(SELECT REPLACE(@@sql_mode,'ONLY_FULL_GROUP_BY',''));
2. 永久解決方案:修改mysql配置文件
注意: 在ubuntu 18中的mysql配置文件需要修改mysqld.cnf, 其他系統(tǒng)的其他mysql版本需要根據(jù)特定情況查找需要修改的配置文件,如:windows中一般是my.ini,linux中可能是my.cnf
/etc/mysql/mysql.conf.d/mysqld.cnf
在[mysqld]下面增加如下內(nèi)容
[mysqld]
#取消錯(cuò)誤: [Err] 1055 - Expression #1 of ORDER BY clause is not in GROUP BY clause and contains nonaggregated column 'information_schema.PROFILING.SEQ' which is not functionally dependent on columns in GROUP BY clause; this is incompatible with sql_mode=only_full_group_by
sql_mode = STRICT_TRANS_TABLES,NO_ZERO_IN_DATE,NO_ZERO_DATE,ERROR_FOR_DIVISION_BY_ZERO,NO_AUTO_CREATE_USER,NO_ENGINE_SUBSTITUTION
- 重啟服務(wù)
systemctl restart mysql
- 查看端口占用情況
netstat -lnp
root@VM-16-14-ubuntu ~# netstat -lnp
Active Internet connections (only servers)
Proto Recv-Q Send-Q Local Address Foreign Address State PID/Program name
...
tcp6 0 0 :::3306 :::* LISTEN 775/mysqld
...
驗(yàn)證: 再次在navicat中select * from table查詢,已沒有錯(cuò)誤.
