主页 >> 程序猿的东西 >> MySQL 拆分行到列

MySQL 拆分行到列

问题

有一个表是下面这样的

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拆出每个逗号分隔的项,这种方式虽然不用新增一个附注表,似乎可移植性更好一点,但是这种表达式的维护成本和可读性是更差的。全当增长见闻吧。