在 MySQL 查询中如何将联接结果选择为一个数组？

Question

我正在尝试编写一个脚本，将modx用户导出为CSV格式，这是相当简单的事情，但在modx中，用户可以属于多个组。简单地加入 modx_member_groups 表会导致不同用户的多行结果。

我想做的是以某种方式重写以下查询，使在 modx_member_groups 上的连接返回用户所属的一组组 ID 的列表或数组。

例如，我希望返回的数据如下：

user_group | id | username | ...the rest
1,3,5,7    | 12 | johndoe  | ...

这是我的查询。

SELECT mg.user_group, u.id, u.username, ua.*

FROM modx_users u 

LEFT JOIN modx_user_attributes ua ON u.id = ua.internalKey 

LEFT JOIN modx_member_groups mg ON u.id = mg.member

LIMIT 10

理想情况下，以实际组名作为列进行选择会更好。然后只需在组名列中强制使用true或false即可。

更新：

在shtever的回答之后，我已经更新了查询，但是遇到了性能问题： - GROUP_CONCAT返回的是BLOB类型，所以我必须进行转换，将group_concat_max_length设置为小于512不起作用。

SELECT GROUP_CONCAT(CONVERT(mg.user_group, CHAR(10)) ORDER BY mg.user_group SEPARATOR ',') AS groups, u.id, u.username, ua.*
FROM modx_users u 
LEFT JOIN modx_user_attributes ua ON u.id = ua.internalKey 
LEFT JOIN modx_member_groups mg ON mg.member = u.id
GROUP BY u.id

如果我将查询限制为10个结果或让其在整个6000个用户上运行，现在执行查询需要27.5秒。如果我删除GROUP_CONCAT，则需要相同的时间。

- Sean Kimball

---

这是一个MySQL数据库吗？ - schtever

Answer 1

对于MySQL数据库，可以考虑使用GROUP_CONCAT函数。详情请参考Mysql GROUP_CONCAT说明文档。

您的查询语句可能如下所示：

SELECT GROUP_CONCAT(DISTINCT mg.user_group ASC SEPARATOR ',') , u.id, u.username, ua.*
FROM modx_users u 
LEFT JOIN modx_user_attributes ua ON u.id = ua.internalKey 
LEFT JOIN modx_member_groups mg ON u.id = mg.member
GROUP BY u.id, u.username
LIMIT 10

根据 modx_user_attributes 和 modx_users 表之间的关系，您可能需要调整 GROUP BY 字段。

- schtever

---

谢谢 - 这确实起作用了，但我不得不稍微调整一下[请参见更新]。我认为我不需要DISTINCT关键字，因为一个用户不能属于同一组两次，并且它返回的是BLOB类型[即使我设置了group_concat_max_length]，所以我不得不进行转换。然而，无论我做什么，速度都很慢，无论是10条记录还是6000条记录[有6000个用户]，它总是需要27.5秒！ - Sean Kimball

在 modx_member_groups 表中对成员列进行索引可以解决速度问题（查询在一秒内完成），但我不确定这将如何影响数据库的其他部分。 - Sean Kimball

索引占用磁盘空间，并且必须在插入、更新和删除操作时进行维护。总的来说，为了性能提升而付出的额外开销是值得的。 - schtever