前言

• mysql 5.7.25

ERROR 1055 (42000) 錯誤

mysql> select host,user from user; +-----------+---------------+ | host | user | +-----------+---------------+ | % | root | | localhost | mysql.session | | localhost | mysql.sys | | localhost | root | +-----------+---------------+ 4 rows in set (0.00 sec)mysql> select host,user from user group by host; ERROR 1055 (42000): Expression #2 of SELECT list is not in GROUP BY clause and c ontains nonaggregated column 'mysql.user.User' which is not functionally depende nt on columns in GROUP BY clause; this is incompatible with sql_mode=only_full_g roup_by

user列未在group by host子句中出現，導致mysql報1055錯誤。

解決辦法1

修改sql語句可以解決：

mysql> select host,min(user) from user group by host; +-----------+---------------+ | host | min(user) | +-----------+---------------+ | % | root | | localhost | mysql.session | +-----------+---------------+ 2 rows in set (0.00 sec)

解決辦法2

修改sql語句不是萬能的，無法修改sql語句時，需要修改mysql的配置來解決。
該問題跟mysql的Server SQL Modes設置有關系。當sql_mode=only_full_group_by時，會出現1055錯誤。將only_full_group_by從sql_mode中剔除即可解決上面的錯誤。
比如這樣：

mysql> set sql_mode=''; mysql> select host,user from user group by host; +-----------+---------------+ | host | user | +-----------+---------------+ | % | root | | localhost | mysql.session | +-----------+---------------+ 2 rows in set (0.00 sec)

通過set sql_mode='';設置Server SQL Modes時，僅在一次會話中有效。
永久性的設置Server SQL Modes需要修改my.cnf配置文件。

shell> vi /etc/my.cnf

在my.cnf中修改內容:

[mysqld] sql_mode=""

重啟mysql后，登錄mysql查看設置結果：

mysql> select @@sql_mode; +------------+ | @@sql_mode | +------------+ | | +------------+ 1 row in set (0.00 sec)

其它

關于Server SQL Modes

https://dev.mysql.com/doc/refman/5.7/en/sql-mode.html
https://www.cnblogs.com/Zender/p/8270833.html
https://www.cnblogs.com/fireporsche/p/8618691.html

總結

以上是生活随笔為你收集整理的mysql5.7设置SQL Mode的全部內容，希望文章能夠幫你解決所遇到的問題。

如果覺得生活随笔網站內容還不錯，歡迎將生活随笔推薦給好友。