MySQL 8中含GROUP_CONCAT DISTINCT的SELECT语句报错求助

我曾在MySQL 5中使用以下SELECT语句（运行正常）：

SELECT users.id, users.account_id, 
        ( SELECT GROUP_CONCAT( 
                    DISTINCT users_groups.group_id 
                    ORDER BY users_groups.group_id ASC SEPARATOR ',' 
                ) AS groups 
        FROM users_groups 
        WHERE users_groups.user_id = ? 
        ), 
        ( SELECT GROUP_CONCAT( 
                    DISTINCT users_limits.limit_id 
                    ORDER BY users_limits.limit_id ASC 
                    SEPARATOR ',' 
                    ) AS limits 
        FROM users_limits 
        WHERE users_limits.user_id = ? 
        ) 
FROM users 
    LEFT JOIN users_groups ON users_groups.user_id = users.id 
WHERE users_groups.user_id = ? 
AND users_groups.group_id = ?;

升级服务器至MySQL 8.0.32后出现如下错误：

code: 'ER_PARSE_ERROR',
errno: 1064,
sqlState: '42000',
sqlMessage: "You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near 'groups FROM users_groups WHERE users_groups.user_id = 1 ), ( SELECT GROUP_CONCAT' at line 1"

请问我忽略了什么？需要做哪些修改？

核心原因 #

groups是MySQL 8.0新增的保留关键字，你在子查询中用它作为别名，导致SQL解析器无法识别，触发语法错误。而MySQL 5版本中groups并非保留字，所以之前能正常执行。

具体修改 #

1. 修复别名冲突（最低必要修改） #

将子查询中的AS groups改为AS groups``，用反引号(`)包裹保留字，避免与关键字冲突：

SELECT users.id, users.account_id, 
        ( SELECT GROUP_CONCAT( 
                    DISTINCT users_groups.group_id 
                    ORDER BY users_groups.group_id ASC SEPARATOR ',' 
                ) AS `groups` 
        FROM users_groups 
        WHERE users_groups.user_id = ? 
        ), 
        ( SELECT GROUP_CONCAT( 
                    DISTINCT users_limits.limit_id 
                    ORDER BY users_limits.limit_id ASC 
                    SEPARATOR ',' 
                    ) AS `limits` 
        FROM users_limits 
        WHERE users_limits.user_id = ? 
        ) 
FROM users 
    LEFT JOIN users_groups ON users_groups.user_id = users.id 
WHERE users_groups.user_id = ? 
AND users_groups.group_id = ?;

2. 优化冗余逻辑（可选但推荐） #

你的主查询已经关联了users_groups，且WHERE条件中过滤了users_groups.user_id，实际LEFT JOIN已经等同于INNER JOIN，可以简化逻辑；同时子查询可以关联主表的users.id，避免重复传参：

SELECT 
    users.id, 
    users.account_id, 
    (SELECT GROUP_CONCAT(DISTINCT ug.group_id ORDER BY ug.group_id ASC SEPARATOR ',') 
     FROM users_groups ug 
     WHERE ug.user_id = users.id) AS `groups`,
    (SELECT GROUP_CONCAT(DISTINCT ul.limit_id ORDER BY ul.limit_id ASC SEPARATOR ',') 
     FROM users_limits ul 
     WHERE ul.user_id = users.id) AS `limits`
FROM users
JOIN users_groups ON users_groups.user_id = users.id
WHERE users_groups.group_id = ?;

内容的提问来源于stack exchange，提问作者Philipp M