MySQL 拆分行到列 - 暗香斋.Blog

问题

有一个表是下面这样的

id | name    
1  | a,b,c    
2  | b

如何把 a,b,c拆分到不同的行，然后做一些处理呢？类似下面这样

id | name    
1  | a    
1  | b    
1  | c    
2  | b

在 hive 里面，因为有数组类型，结合分割函数和 Lateral View 语法，可以比较优雅的做到。MySQL 是以事务处理为主的数据库，似乎还不支持字段为数组类型（截至 v8.4），也就无法使用 Lateral View把行转换成列。那么在 MySQL的场景下怎么实现类似的功能呢。下面提供两种方式。

实现方案

辅助表法

假设我们现在有一张表叫 numbers，他里面只有一列 n，值是从 1,2,3,4.....max。这个 max 只要大于你用逗号分割的项的最大数量即可，比如500，那就在这个表中插入1-500。然后可以用下面的方式来获得Lateral View的效果。

select
  tablename.id,
  SUBSTRING_INDEX(SUBSTRING_INDEX(tablename.name, ',', numbers.n), ',', -1) name
from
  numbers inner join tablename
  on CHAR_LENGTH(tablename.name)-CHAR_LENGTH(REPLACE(tablename.name, ',', ''))+1>=numbers.n
order by
  id, n

解释一些表达式：SUBSTRING_INDEX(SUBSTRING_INDEX(tablename.name, ',', numbers.n), ',', -1) 实现的效果是，把tablename.name里面第numbers.n个逗号前面的那一项提取出来。CHAR_LENGTH(tablename.name)-CHAR_LENGTH(REPLACE(tablename.name, ',', ''))+1 用来推导tablename.name字段里面有多少项，他的计算方式是对比原始字符串和删掉逗号的字符串的长度差，即是逗号的个数，因为逗号个数比项的个数少1，所以后面+1即是有多少项。通过关联numbers表，把行数先扩出来，然后用前面的SUBSTRING_INDEX提取出对应的项。

创建辅助表的方法可以用下面的 sql 语句实现（MySQL 语法）

CREATE TABLE `dim_numbers` (
  `n` mediumint NOT NULL AUTO_INCREMENT,
  PRIMARY KEY (`n`)
) ENGINE=InnoDB AUTO_INCREMENT=501 DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_general_ci COMMENT='500 个数字，用于行列转换';

INSERT INTO dim_numbers (n)
WITH RECURSIVE seq AS (
  SELECT 1 AS n
  UNION ALL
  SELECT n + 1
  FROM seq
  WHERE n < 500
)
SELECT n FROM seq;

如果项数不多，我们又不想创建独立的numbers表，我们可以用行内表的方式来构造numbers，参考《冷门SQL技能之行内数据表》。

还看到有人用一种奇技淫巧的方式也可以不用额外创建表，可以用mysql.help_topic表来代替numbers表，这是MySQL的内置表，他的主键help_topic_id是从0开始递增的，整体又几百行，足够应付绝大部分场景，因为我们前面设计的numbers表是从1开始的，而这个表从0开始，所以在使用的时候注意对齐。因为这个表存储的是帮助信息，所以他不会被改变，可以放心使用。但是过于奇技淫巧，如果这段SQL会有多人维护的话，可读性会非常差。

递归表达式法

从MySQL8.0开始，支持使用递归表达式，示例如下：

WITH RECURSIVE split_string AS (
    SELECT
        SUBSTRING_INDEX('apple,banana,cherry', ',', 1) AS part,
        SUBSTRING('apple,banana,cherry' FROM LOCATE(',', 'apple,banana,cherry') + 1) AS rest
    UNION ALL
    SELECT
        SUBSTRING_INDEX(rest, ',', 1),
        SUBSTRING(rest FROM LOCATE(',', rest) + 1)
    FROM
        split_string
    WHERE
        rest <> ''
)
SELECT part FROM split_string;

这个方法是我问ChatGPT得到的，大概的原理是通过union的方式来扩展行，然后也是用SUBSTRING_INDEX拆出每个逗号分隔的项，这种方式虽然不用新增一个附注表，似乎可移植性更好一点，但是这种表达式的维护成本和可读性是更差的。全当增长见闻吧。