mysql出现which is not functionally dependent on columns in GROUP BY clause报错解决方案

痛定思痛。 2021-08-28 22:58 268阅读 0赞

**一 数据表结构**

CREATE TABLE `sys_user_role` (
      `id` varchar(30) NOT NULL,
      `uid` varchar(30) NOT NULL,
      `rid` varchar(30) NOT NULL,
      PRIMARY KEY (`id`,`uid`,`rid`),
      KEY `user_role_ibfk_2` (`uid`),
      KEY `user_role_ibfk_1` (`rid`),
      CONSTRAINT `sys_user_role_ibfk_1` FOREIGN KEY (`rid`) REFERENCES `sys_role` (`id`) ON UPDATE CASCADE,
      CONSTRAINT `sys_user_role_ibfk_2` FOREIGN KEY (`uid`) REFERENCES `sys_user` (`id`) ON DELETE CASCADE ON UPDATE CASCADE
    ) ENGINE=InnoDB DEFAULT CHARSET=utf8;

**二 表数据**

mysql> SELECT * from sys_user_role;
    +---------------------+---------------------+--------------------+
    | id                  | uid                 | rid                |
    +---------------------+---------------------+--------------------+
    | 1                   | 1                   | 988623554205990914 |
    | 1258674821306949634 | 1258674238592294913 | 988623554205990914 |
    | 1258674821508276226 | 1258674238592294913 | 989416986389880834 |
    | 989753486051151873  | 2                   | 989416986389880834 |
    | 989419854702944258  | 989416849999503361  | 989419745311301633 |
    | 989419854874910722  | 989416849999503361  | 989416986389880834 |
    | 989423607791329281  | 989423607472562177  | 989419745311301633 |
    +---------------------+---------------------+--------------------+
    7 rows in set (0.00 sec)

**三 查询语句报错**

mysql> SELECT id AS id,uid,rid FROM sys_user_role WHERE (rid = '1259293401992036353') GROUP BY uid;
    ERROR 1055 (42000): Expression #1 of SELECT list is not in GROUP BY clause and contains nonaggregated column 'wdog.sys_user_role.id' which is not functionally dependent on columns in GROUP BY clause; this is incompatible with sql_mode=only_full_group_by

**四 解决方案**

方案一：修改SQL语句，每个字段前加上any\_value

mysql> SELECT any_value(id) AS id,any_value(uid),any_value(rid) FROM sys_user_role WHERE (rid = '1259293401992036353') GROUP BY uid;
    Empty set (0.00 sec)

方案二：修改数据库配置

1 查询sql模式

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_AUTO_CREATE_USER,NO_ENGINE_SUBSTITUTION |
    +-------------------------------------------------------------------------------------------------------------------------------------------+
    1 row in set (0.00 sec)

2 修改配置文件D:\\mysql\\mysql-5.7.22-winx64my.ini

# 增加下面一行
    sql_mode=STRICT_TRANS_TABLES,NO_ZERO_IN_DATE,NO_ZERO_DATE,ERROR_FOR_DIVISION_BY_ZERO,NO_AUTO_CREATE_USER,NO_ENGINE_SUBSTITUTION

3 重启mysql服务

4 再次查询

mysql> SELECT id AS id,uid,rid FROM sys_user_role WHERE (rid = '1259293401992036353') GROUP BY uid;
    Empty set (0.01 sec)

**五 参考**

[https://blog.csdn.net/qq\_42175986/article/details/82384160][https_blog.csdn.net_qq_42175986_article_details_82384160]

[https://blog.csdn.net/qq\_33621326/article/details/95950264][https_blog.csdn.net_qq_33621326_article_details_95950264]

[https_blog.csdn.net_qq_42175986_article_details_82384160]: https://blog.csdn.net/qq_42175986/article/details/82384160
[https_blog.csdn.net_qq_33621326_article_details_95950264]: https://blog.csdn.net/qq_33621326/article/details/95950264