sql按逗号拆分函数mysql_mysql – SQL拆分逗号分隔行

你可以用纯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

注意：诀窍是在这种情况下使用tally(数字)表和非常方便的MySQL函数SUBSTRING_INDEX().如果你做了很多这样的查询(拆分),那么你可以考虑填充和使用持久化的表,而不是生成它在这个例子中有一个子查询.此示例中的子查询生成从1到100的数字序列,有效地允许您在源表中每行分割最多100个分隔值.如果需要或多或少需要,可以轻松调整.

输出：

| VALUE |

|----------------|

| somethingA |

| somethingB |

| somethingC |

| somethingElseA |

| somethingElseB |

这是查询可能使用持久化表格的方式

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