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