【SQL】按特定字符分割一行转多行

原表如下：

with t1 as(
    select 1 as id, "a,b" as course
    union
    select 2 as id, "a,b,c" as course
    union
    select 3 as id, null as course
    union
    select 4 as id, "c" as course
)
select * from t1;

需要将 course 按照 ‘,‘ 拆分成下表：

方法如下：

with t1 as(
    select 1 as id, "a,b" as course
    union
    select 2 as id, "a,b,c" as course
    union
    select 3 as id, null as course
    union
    select 4 as id, "c" as course
)

select
    t1.id,
    substring_index(substring_index(t1.course, ',', b.help_topic_id + 1), ',', -1) as course
from t1 join mysql.help_topic b
on b.help_topic_id < (length(t1.course) - length(replace(t1.course, ',', '')) + 1)
union all
select id, course
from t1
where course is null

这里主要用到 substring_index 和 mysql.help_topic。

substring_index

SUBSTRING_INDEX 是字符串截取函数。

SUBSTRING_INDEX(str, delim, count)

• str : 表示需要拆分的字符串
• delim : 表示分隔符，通过某字符进行拆分
• count : 当 count 为正数，取第 n 个分隔符之前的所有字符；当 count 为负数，取倒数第 n 个分隔符之后的所有字符。

例如：

SELECT SUBSTRING_INDEX('a*b*c*d', '*', 1);  -- 返回: a
SELECT SUBSTRING_INDEX('a*b*c*d', '*', 2);  -- 返回: a*b 
SELECT SUBSTRING_INDEX('a*b*c*d', '*', 3);  -- 返回: a*b*c
SELECT SUBSTRING_INDEX('a*b*c*d', '*', 4);  -- 返回: a*b*c*d
SELECT SUBSTRING_INDEX('a*b*c*d', '*', -1); -- 返回: d
SELECT SUBSTRING_INDEX('a*b*c*d', '*', -2); -- 返回: c*d
SELECT SUBSTRING_INDEX('a*b*c*d', '*', -3); -- 返回: b*c*d
SELECT SUBSTRING_INDEX('a*b*c*d', '*', -4); -- 返回: a*b*c*d

mysql.help_topic

help_topic 本身是 MySQL 一个帮助解释注释表，用于解释 MySQL 各种专有名词，由于这张表数据 ID 是从 0 顺序增加的，方便我们用于计数，但是 8.0.17 版本的只有 686 条数据，超过这个数字，我们就需要己自定义一张表。

可以用做计数的临时表，查询的语句只会用 help_topic 计数，超出的部分其实都是脏数据。

• help_topic_id：帮助主题详细信息在表记录中对应的 ID；
• name：帮助主题给定的关键字名称，与 help_keyword 表中的 name 字段值相等；
• help_category_id：帮助主题类别 ID，与 help_category 表中的 help_category_id 字段值相等；
• description：帮助主题的详细信息（这里就是我们通常查询帮助信息真正想看的内容，例如：告诉我们某某语句如何使用的语法与注意事项等）；
• example：帮助主题的示例信息（这里告诉我们某某语句如何使用的示例）；
• url：该帮助主题对应在 MySQL 官方在线手册中的URL链接地址。

如果不需要展开成多行，只需要计数，则可以将 ‘,‘ 替换成空，计算字符串的长度差。

select
	id, 
	length(course) - length(replace(course, ',', '')) + 1 as cnt
from t1

结果如下：