c mysql查询语句报错_mysql出现which is not functionally dependent on columns in GROUP BY clause报错解决方案...

一 数据表结构

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)

五 参考