在左连接上选择group_concat vs group_concat

I'm analyzing a few queries and have noticed some interesting results when trying to extract multiple fields from different tables based on a user_id. Consider the following tables:

我正在分析一些查询,并在尝试根据user_id从不同的表中提取多个字段时发现了一些有趣的结果。请考虑以下表格:

+------------------------+------------------+------+-----+---------+----------------+
| Field                  | Type             | Null | Key | Default | Extra          |
+------------------------+------------------+------+-----+---------+----------------+
| subscription_id        | int(11) unsigned | NO   | PRI | NULL    | auto_increment |
| user_id                | int(11) unsigned | YES  | MUL | NULL    |                |
| expires                | int(11) unsigned | YES  | MUL | NULL    |                |
| created                | int(11) unsigned | YES  | MUL | NULL    |                |
| modified               | int(11)          | YES  |     | NULL    |                |
+------------------------+------------------+------+-----+---------+----------------+

I want to GROUP_CONCAT all of a user's subscriptions when fetching users. Currently this method works for one field:

我想在获取用户时GROUP_CONCAT所有用户的订阅。目前,此方法适用于一个字段:

SELECT u.id,
(
    SELECT GROUP_CONCAT(s.subscription_id)
    FROM subscriptions s
    WHERE s.user_id = u.id
) AS subscription_ids
FROM users u
LIMIT 10

And I can add all of the fields with different SELECT GROUP_CONCATs. However this method is very slow with any real number of rows due to MySQL joining the subscriptions table for every field. Is there a way that I can GROUP_CONCAT all of the fields at once when listing users?

我可以使用不同的SELECT GROUP_CONCAT添加所有字段。但是,由于MySQL加入了每个字段的订阅表,因此该方法对于任何实际行数都非常慢。有没有办法在列出用户时立即GROUP_CONCAT所有字段?

I've tried a LEFT JOIN method:

我尝试过LEFT JOIN方法:

SELECT
    u.id AS user_id,
    GROUP_CONCAT(s.subscription_id) AS subscription_ids
FROM users u
LEFT JOIN subscriptions s
    ON s.user_id = u.id
LIMIT 10

However, that appears to concatenate all of the subscription_ids. I'm a bit stumped why the LEFT JOIN would do that. But I'm surprised there doesn't appear to be an easy way to concatenate a list of fields from a different table.

但是,这似乎连接了所有的subscription_ids。我有点难过为什么LEFT JOIN会这样做。但我很惊讶似乎没有一种简单的方法来连接不同表中的字段列表。

1 个解决方案

#1

The GROUP_CONCAT is an aggregate function and therefore needs to be applied with GROUP BY clause.

GROUP_CONCAT是一个聚合函数,因此需要应用GROUP BY子句。

SELECT
    u.id AS user_id, GROUP_CONCAT(s.subscription_id) AS subscription_ids
FROM 
    users u
    LEFT JOIN subscriptions s ON s.user_id = u.id
GROUP BY
    u.id
LIMIT 10