我可以将多个MySQL行连接到一个字段中吗? [英] Can I concatenate multiple MySQL rows into one field?
问题描述
使用MySQL
,我可以执行以下操作:
Using MySQL
, I can do something like:
SELECT hobbies FROM peoples_hobbies WHERE person_id = 5;
我的输出:
shopping
fishing
coding
但是我只想要1行1列:
but instead I just want 1 row, 1 col:
预期输出:
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.
我在 MySQL Doc ,它看起来不像CONCAT
或CONCAT_WS
函数接受结果集.
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?
推荐答案
You can use GROUP_CONCAT
:
SELECT person_id, GROUP_CONCAT(hobbies SEPARATOR ', ')
FROM peoples_hobbies
GROUP BY person_id;
如路德维希在他的评论中所述,您可以添加DISTINCT
运算符以避免重复:
As Ludwig stated in his comment, you can add the DISTINCT
operator to avoid duplicates:
SELECT person_id, GROUP_CONCAT(DISTINCT hobbies SEPARATOR ', ')
FROM peoples_hobbies
GROUP BY person_id;
如Jan在他们的评论中所述,您也可以在使用ORDER BY
将其内插之前对值进行排序:
As Jan stated in their comment, you can also sort the values before imploding it using ORDER BY
:
SELECT person_id, GROUP_CONCAT(hobbies ORDER BY hobbies ASC SEPARATOR ', ')
FROM peoples_hobbies
GROUP BY person_id;
如达格在他的评论中所述, 结果限制为1024个字节.要解决此问题,请在查询之前运行以下查询:
As Dag stated in his comment, there is a 1024 byte limit on the result. To solve this, run this query before your query:
SET group_concat_max_len = 2048;
当然,您可以根据需要更改2048
.要计算和分配值,请执行以下操作:
Of course, you can change 2048
according to your needs. 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
);
这篇关于我可以将多个MySQL行连接到一个字段中吗?的文章就介绍到这了,希望我们推荐的答案对大家有所帮助,也希望大家多多支持IT屋!