mysql - 我可以将多个MySQL行连接到一个字段中吗？(Can I concatenate multiple MySQL rows into one field?)

Question

Using MySQL , I can do something like:

(使用MySQL ，我可以做类似的事情：)

SELECT hobbies FROM peoples_hobbies WHERE person_id = 5;

My Output:

(我的输出：)

shopping
fishing
coding

but instead I just want 1 row, 1 col:

(但是我只想要1行1列：)

Expected Output:

(预期产量：)

shopping, fishing, coding

The reason is that I'm selecting multiple values from multiple tables, and after all the joins I've got a lot more rows than I'd like.

(原因是我要从多个表中选择多个值，并且在所有联接之后，我得到的行比我想要的要多得多。)

I've looked for a function on MySQL Doc and it doesn't look like the CONCAT or CONCAT_WS functions accept result sets, so does anyone here know how to do this?

(我在MySQL Doc上寻找了一个函数，它看起来不像CONCAT或CONCAT_WS函数接受结果集，所以这里有人知道如何做到这一点吗？)

  ask by Dean Rather translate from so

Answer 1

You can use GROUP_CONCAT :

(您可以使用GROUP_CONCAT ：)

SELECT person_id, GROUP_CONCAT(hobbies SEPARATOR ', ')
FROM peoples_hobbies
GROUP BY person_id;

As Ludwig stated in his comment, you can add the DISTINCT operator to avoid duplicates:

(正如路德维希（Ludwig）在其评论中所述，您可以添加DISTINCT运算符以避免重复：)

SELECT person_id, GROUP_CONCAT(DISTINCT hobbies SEPARATOR ', ')
FROM peoples_hobbies 
GROUP BY person_id;

As Jan stated in their comment, you can also sort the values before imploding it using ORDER BY :

(正如Jan在他们的评论中所述，您还可以在使用ORDER BY爆破之前对值进行排序：)

SELECT person_id, GROUP_CONCAT(hobbies ORDER BY hobbies ASC SEPARATOR ', ')
FROM peoples_hobbies
GROUP BY person_id;

As Dag stated in his comment, there is a 1024 byte limit on the result.

(正如达格（Dag）在其评论中所述，结果限制为1024个字节。)

To solve this, run this query before your query:

(要解决此问题，请在查询之前运行以下查询：)

SET group_concat_max_len = 2048;

Of course, you can change 2048 according to your needs.

(当然，您可以根据需要更改2048 。)

To calculate and assign the value:

(要计算和分配值：)

SET group_concat_max_len = CAST(
    (SELECT SUM(LENGTH(hobbies)) + COUNT(*) * LENGTH(', ')
    FROM peoples_hobbies 
    GROUP BY person_id)
    AS UNSIGNED
);