mysql - 如何使用逗号分隔符从mysql获取数据

Question

我有一个表，在这个表中，每一行都包含一些列，每一列都包含逗号分隔的值，例如:

 column1   |  column2            | column3
 ------------------------------------------
 1,2,3,4,5 | abc,xyz,pqr,std,bca |  etc.

现在我需要将每一行转换为 5 行，例如:

1st row like col1 1 col2 abc col3 ........
2nd row like col1 2 col2 xyz col3.........
3st row like col1 3 col2 pqr col3 ........
4nd row like col1 4 col2 std col3.........
5st row like col1 5 col2 bca col3 ........

我怎样才能做到这一点？

Answer 1

这是可能的，但很困惑。

SELECT DISTINCT SUBSTRING_INDEX(SUBSTRING_INDEX(column1, ',', units.i + tens.i * 10), ',', -1) AS col1,
        SUBSTRING_INDEX(SUBSTRING_INDEX(column2, ',', units.i + tens.i * 10), ',', -1) AS col2,
        SUBSTRING_INDEX(SUBSTRING_INDEX(column3, ',', units.i + tens.i * 10), ',', -1) AS col3,
        SUBSTRING_INDEX(SUBSTRING_INDEX(column4, ',', units.i + tens.i * 10), ',', -1) AS col4
FROM sometable
CROSS JOIN (SELECT 0 i UNION SELECT 1 UNION SELECT 2 UNION SELECT 3 UNION SELECT 4 UNION SELECT 5 UNION SELECT 6 UNION SELECT 7 UNION SELECT 8 UNION SELECT 9)units
CROSS JOIN (SELECT 0 i UNION SELECT 1 UNION SELECT 2 UNION SELECT 3 UNION SELECT 4 UNION SELECT 5 UNION SELECT 6 UNION SELECT 7 UNION SELECT 8 UNION SELECT 9)tens

假设一行的所有列都包含相同数量的逗号分隔字段，这将处理多达 100 个逗号分隔字段。

我正在使用 DISTINCT 来消除重复项(使用 substring_index 的工作方式，如果逗号分隔值少于 100 个，则将重复最后一个值，直到有 100 个)，但您可能希望原始表中的唯一键选择以及避免合法重复。另一种方法是计算一行中某个字段中逗号分隔值的数量，并添加一个 where 子句以根据此计数检查生成的数字。

在实时代码中执行此操作可能是一个非常糟糕的主意，但如果您正在将现有表格转换为新格式以供将来使用，则可能是值得的。