MySQL 8.0 修改 sql_mode=only_full_group_by

今天在查询sql时遇到

mysql> select id,mobile from mt_user group by mobile having count(1)>1;
ERROR 1055 (42000): Expression #1 of SELECT list is not in GROUP BY clause and contains nonaggregated column 'maestro.mt_user.id' which is not functionally dependent on columns in GROUP BY clause; this is incompatible with sql_mode=only_full_group_by

 

原因分析：MySQL版本默认设置了 mysql sql_mode = only_full_group_by 属性，导致报错。

为了验证是不是数据版本问题, 于是查询版本

SELECT VERSION()

+-------------------------+
| VERSION()               |
+-------------------------+
| 8.0.21-0ubuntu0.20.04.4 |
+-------------------------+

解决办法

#查询当前设置

mysql> SELECT @@sql_mode;
+-----------------------------------------------------------------------------------------------------------------------+
| @@sql_mode                                                                                                            |
+-----------------------------------------------------------------------------------------------------------------------+
| ONLY_FULL_GROUP_BY,STRICT_TRANS_TABLES,NO_ZERO_IN_DATE,NO_ZERO_DATE,ERROR_FOR_DIVISION_BY_ZERO,NO_ENGINE_SUBSTITUTION |
+-----------------------------------------------------------------------------------------------------------------------+

#修改全局 于新建的数据库有效。
mysql> SET @@global.sql_mode ='STRICT_TRANS_TABLES,NO_ZERO_IN_DATE,NO_ZERO_DATE,ERROR_FOR_DIVISION_BY_ZERO,NO_ENGINE_SUBSTITUTION';
Query OK, 0 rows affected (0.00 sec)


#修改当前设置 对于已存在的数据库，则需要在对应的数据下执行：
mysql> SET sql_mode ='STRICT_TRANS_TABLES,NO_ZERO_IN_DATE,NO_ZERO_DATE,ERROR_FOR_DIVISION_BY_ZERO,NO_ENGINE_SUBSTITUTION';
Query OK, 0 rows affected (0.00 sec)

在执行查询语句

mysql> select id,mobile from mt_user group by mobile having count(1)>1;
+-----+--------+
| id  | mobile |
+-----+--------+
| 117 |    111 |
+-----+--------+
1 row in set (0.01 sec)