MySQL 中 GROUP_CONCAT 的反面是什么？
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/17308669/
Warning: these are provided under cc-by-sa 4.0 license. You are free to use/share it, But you must attribute it to the original authors (not me):
StackOverFlow
What is the opposite of GROUP_CONCAT in MySQL?
提问by Jason Hamje
I seem to come against this problem a lot, where I have data that's formatted like this:
我似乎经常遇到这个问题，我的数据格式如下：
+----+----------------------+
| id | colors |
+----+----------------------+
| 1 | Red,Green,Blue |
| 2 | Orangered,Periwinkle |
+----+----------------------+
but I want it formatted like this:
但我希望它的格式如下：
+----+------------+
| id | colors |
+----+------------+
| 1 | Red |
| 1 | Green |
| 1 | Blue |
| 2 | Orangered |
| 2 | Periwinkle |
+----+------------+
Is there a good way to do this? What is this kind of operation even called?
有没有好的方法可以做到这一点？这种手术到底叫什么？
采纳答案by kmas
I think it is what you need (stored procedure) : Mysql split column string into rows
我认为这是您需要的（存储过程）：Mysql 将列字符串拆分为行
DELIMITER $$
DROP PROCEDURE IF EXISTS explode_table $$
CREATE PROCEDURE explode_table(bound VARCHAR(255))
BEGIN
DECLARE id INT DEFAULT 0;
DECLARE value TEXT;
DECLARE occurance INT DEFAULT 0;
DECLARE i INT DEFAULT 0;
DECLARE splitted_value INT;
DECLARE done INT DEFAULT 0;
DECLARE cur1 CURSOR FOR SELECT table1.id, table1.value
FROM table1
WHERE table1.value != '';
DECLARE CONTINUE HANDLER FOR NOT FOUND SET done = 1;
DROP TEMPORARY TABLE IF EXISTS table2;
CREATE TEMPORARY TABLE table2(
`id` INT NOT NULL,
`value` VARCHAR(255) NOT NULL
) ENGINE=Memory;
OPEN cur1;
read_loop: LOOP
FETCH cur1 INTO id, value;
IF done THEN
LEAVE read_loop;
END IF;
SET occurance = (SELECT LENGTH(value)
- LENGTH(REPLACE(value, bound, ''))
+1);
SET i=1;
WHILE i <= occurance DO
SET splitted_value =
(SELECT REPLACE(SUBSTRING(SUBSTRING_INDEX(value, bound, i),
LENGTH(SUBSTRING_INDEX(value, bound, i - 1)) + 1), ',', ''));
INSERT INTO table2 VALUES (id, splitted_value);
SET i = i + 1;
END WHILE;
END LOOP;
SELECT * FROM table2;
CLOSE cur1;
END; $$
回答by fthiella
You could use a query like this:
您可以使用这样的查询：
SELECT
id,
SUBSTRING_INDEX(SUBSTRING_INDEX(colors, ',', n.digit+1), ',', -1) color
FROM
colors
INNER JOIN
(SELECT 0 digit UNION ALL SELECT 1 UNION ALL SELECT 2 UNION ALL SELECT 3) n
ON LENGTH(REPLACE(colors, ',' , '')) <= LENGTH(colors)-n.digit
ORDER BY
id,
n.digit
Please see fiddle here. Please notice that this query will support up to 4 colors for every row, you should update your subquery to return more than 4 numbers (or you should use a table that contains 10 or 100 numbers).
请在此处查看小提琴。请注意，此查询每行最多支持 4 种颜色，您应该更新子查询以返回 4 个以上的数字（或者您应该使用包含 10 或 100 个数字的表）。
回答by gerrit_hoekstra
This saved me many hours! Taking it a step further: On a typical implementation there would in all likelyhood be a table that enumerates the colours against an identitying key, color_list. A new colour can be added to the implementation without having to modify the query and the potentially endless union-clause can be avoided altogether by changing the query to this:
这节省了我很多小时！更进一步：在典型的实现中，很可能会有一个表格，它根据标识键枚举颜色，color_list. 无需修改查询即可将新颜色添加到实现中，并且union可以通过将查询更改为以下内容来完全避免潜在的无限子句：
SELECT id,
SUBSTRING_INDEX(SUBSTRING_INDEX(colors, ',', n.digit+1), ',', -1) color
FROM
colors
INNER JOIN
(select id as digit from color_list) n
ON LENGTH(REPLACE(colors, ',' , '')) <= LENGTH(colors)-n.digit
ORDER BY id, n.digit;
It is important that the Ids in table color_list remain sequential, however.
然而，重要的是表 color_list 中的 Id 保持顺序。
回答by yael alfasi
notice this can be done without creating a temporary table
注意这可以在不创建临时表的情况下完成
select id, substring_index(substring_index(genre, ',', n), ',', -1) as genre
from my_table
join
(SELECT @row := @row + 1 as n FROM
(select 0 union all select 1 union all select 3 union all select 4 union all select 5 union all select 6 union all select 6 union all select 7 union all select 8 union all select 9) t,
(SELECT @row:=0) r) as numbers
on char_length(genre)
- char_length(replace(genre, ',', '')) >= n - 1
回答by sailesh
if delimiter is part of data but embedded by double quotes then how can we split it.
如果分隔符是数据的一部分但被双引号嵌入，那么我们如何拆分它。
Example first,"second,s",third
例如第一个，“第二个，s”，第三个
it should come as first second,s third
它应该作为第一第二，第三
