MYSQL 列转行方法
目标
上周遇到个业务场景,要求把一列中用分隔符连接的数据,通过分隔符转多行,形如:
转为
准备
表结构
CREATE TABLE `t_tag` (
`id` int NOT NULL AUTO_INCREMENT,
`tags` varchar(255) DEFAULT NULL COMMENT '标签;分隔',
PRIMARY KEY (`id`)
) ENGINE=InnoDB;
操作:
a.使用CAHR_LENGTH()获取每行tags内tag数量
tags内tag数量即分隔符数+1
注:使用CAHR_LENGTH()函数计算字符个数,对于存储内容为字母/数字也可用LENGTH()函数获取字符字节数,utf8编码下,每个字母/数字都只占一个字节,但一个汉字占3个字节,所以获取字符个数推荐使用CHAR_LEGTN()更准确
SELECT
id,
tags,
CHAR_LENGTH( tags )- CHAR_LENGTH(REPLACE ( tags, ';', '' ))+ 1 AS tagCount
FROM
t_tag
结果:
b.通过SUBSTRING_INDEX()切割tag
① SUBSTRING_INDEX(str,delim,count) 用法,该函数三个参数分别为 字符串、分割符、第n个分隔符
例如 substring_index(‘1;2;3’, ‘;’, 2) 的结果是1;2:表示取第二个分隔符左边的子串;
若第三个参数为负数,则表示取倒数第n个分隔符右边的子串,如:substring_index(‘1;2;3’, ‘;’, -1)结果为3
对于多个分隔符,取中间的 tag 用两层SUBSTRING_INDEX(SUBSTRING_INDEX(tags, ‘;’, -1), ‘;’ n) 就都可以拿到了,比如
-- 结果是 1, 内层substring_index结果是 '1', 外层再取 '1' 的倒数第一个分隔符;右边 结果还是1
SELECT substring_index(substring_index('1;2;3', ';', 1), ';', -1);
-- 结果是 2, 内层substring_index结果是 '1;2', 外层再取 '1;2' 的倒数第一个分隔符;右边 结果是2
SELECT substring_index(substring_index('1;2;3', ';', 2), ';', -1);
-- 结果是 3, 内层substring_index结果是 '1;2;3', 外层再取 '1;2;3' 的倒数第一个分隔符;右边 结果是3
SELECT substring_index(substring_index('1;2;3', ';', 3), ';', -1);
这样就分别取到了 tags 内的分隔符隔开的 第一项,第二项 和 第三项,现在只要保证内层substring_index中的n是根据 tag 数量递增就行,这里使用了 mysql.help_topic 表的自增 help_topic_id
c.通过mysql.help_topic 表的自增 help_topic_id 作为substring_index的分割点
②要借助一张 mysql.help_topic 表的从0开始的自增 help_topic_id,通过笛卡尔积join出得help_toppic_id作为分割点
先看下 mysql.help_topic 结构: SELECT * FROM mysql.help_topic
其他字段不用管,可以看到 mysql.help_topic 的 help_topic_id 是从零开始自增的,结合此处分割点 n 从1开始 到 tag 数的场景,使用时要 对help_topic_id+1
注: mysql.help_topic 我这里看只有701条数据,即help_topic_id 只自增到700,所以 tags 中要分割的tag > 700,可另找或自建其他自增id更多的表
通过 join mysql.help_topic 获取分割点
SELECT
a.id,
a.tags,
b.help_topic_id + 1 AS '分割点'
FROM
t_tag AS a
JOIN mysql.help_topic AS b ON b.help_topic_id < CHAR_LENGTH( tags )- CHAR_LENGTH(REPLACE ( tags, ';', '' ))+ 1
结果可以看到分割点 都获取到了:
再把分割点带入 substring_index(substring_index(tags, ‘;’, n), ‘;’, -1) 即可得到所需的结果
SELECT
a.id,
a.tags,
SUBSTRING_INDEX(SUBSTRING_INDEX(tags,';', b.help_topic_id + 1), ';',-1) AS tag
FROM
t_tag AS a
JOIN mysql.help_topic AS b ON b.help_topic_id < CHAR_LENGTH( tags )- CHAR_LENGTH(REPLACE ( tags, ';', '' ))+ 1
得到最终转列之后的结果: