MySQL 8.0 修改 sql_mode=only_full_group_by

╰半夏微凉° 2022-12-01 15:59 192阅读 0赞

今天在查询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)