SQL拆分逗号分隔的行

Question

我有一列包含变量数量逗号分隔值的数据：

somethingA,somethingB,somethingC
somethingElseA, somethingElseB

我希望结果能够取每个值并创建一行：

somethingA
somethingB
somethingC
somethingElseA
somethingElseB

我该如何在SQL（MySQL）中做到这一点？

（我尝试过搜索 "implode "和 "lateral view"，但这些似乎没有相关的问题。所有相关的SO问题都在尝试做更复杂的事情）

- Don P

---

在SO上被问了无数次：例如https://dev59.com/d2kv5IYBdhLWcg3wdQnm，https://dev59.com/EmYr5IYBdhLWcg3wM3j6 - Mitch Wheat

@MitchWheat - 这不是这个问题的答案。他们有一组逗号分隔的值，而我没有。 - Don P

你是在寻找一个纯 SQL 的解决方案，还是像 PHP 这样的其他语言的解决方案呢？ - user1864610

如果您要将其标记为“关闭”，请实际链接到答案，而不仅仅假设它是一个常见的问题并且有答案。 - Don P

顺便提一句：从长远来看，更好的做法是修复“我有一个带逗号分隔值的列” - 即第三范式。 - Mitch Wheat

Answer 1

你可以用纯SQL来做到这一点

SELECT SUBSTRING_INDEX(SUBSTRING_INDEX(t.values, ',', n.n), ',', -1) value
  FROM table1 t CROSS JOIN 
(
   SELECT a.N + b.N * 10 + 1 n
     FROM 
    (SELECT 0 AS N UNION ALL SELECT 1 UNION ALL SELECT 2 UNION ALL SELECT 3 UNION ALL SELECT 4 UNION ALL SELECT 5 UNION ALL SELECT 6 UNION ALL SELECT 7 UNION ALL SELECT 8 UNION ALL SELECT 9) a
   ,(SELECT 0 AS N UNION ALL SELECT 1 UNION ALL SELECT 2 UNION ALL SELECT 3 UNION ALL SELECT 4 UNION ALL SELECT 5 UNION ALL SELECT 6 UNION ALL SELECT 7 UNION ALL SELECT 8 UNION ALL SELECT 9) b
    ORDER BY n
) n
 WHERE n.n <= 1 + (LENGTH(t.values) - LENGTH(REPLACE(t.values, ',', '')))
 ORDER BY value

注意：诀窍在于利用记数（数字）表和一个在这种情况下非常方便的MySQL函数SUBSTRING_INDEX()。如果您经常执行此类查询（拆分），则可以考虑填充并使用持久化计数表，而不是像此示例中使用子查询动态生成它。此示例中的子查询从1到100生成一系列数字，有效地允许您在源表中每行拆分多达100个分隔值。如果需要更多或更少，您可以轻松调整它。

输出： | VALUE | |----------------| | somethingA | | somethingB | | somethingC | | somethingElseA | | somethingElseB |

这里是SQLFiddle演示

---

这是使用持久化计数表的查询示例。

SELECT SUBSTRING_INDEX(SUBSTRING_INDEX(t.values, ',', n.n), ',', -1) value
  FROM table1 t CROSS JOIN tally n
 WHERE n.n <= 1 + (LENGTH(t.values) - LENGTH(REPLACE(t.values, ',', '')))
 ORDER BY value

这是SQLFiddle演示

- peterm

---

天才的解决方案。谢谢。 - bharatesh

非常好的答案，完美地将朋友ID插入到用户关系表中（基于包含friendid1、friendid2的列）。 - Michael Clark

可以正常工作。提示：将 table1 -> [your table]，values -> your field name，value -> your field name 替换为您的表和字段名称即可，无需其他操作。 - TheTechGuy

伟大的解决方案运行得很好，但如何进行分组？ - Ucdemir