mysql - 如何在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 秒才能执行，它总是需要 27.5 秒。如果我删除 GROUP_CONCAT ~ 相同的时间。

Answer 1

对于 MySQL，请查看 GROUP_CONCAT 函数。 Mysql GROUP_CONCAT Description

您的查询可能类似于:

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

您可能需要修改 GROUP BY 字段，具体取决于 modx_user_attributes 和 modx_users 表之间的关系。