mysql根据分隔符将一行数据拆分成多行数据

mysql根据分隔符将一行数据拆分成多行数据

关键函数

substring_index(str,delim,count)

​ str:要处理的字符串

​ delim:分隔符

​ count:计数

例子：str=f.s.z

​ substring_index(str,’.’,1)

​ 结果是：f

​ substring_index(str,’.’,2)

​ 结果是：f.s

​ 也就是说，如果count是正数，那么就是从左往右数，第N个分隔符的左边的全部内容

​ 相反，如果是负数，那么就是从右边开始数，第N个分隔符右边的所有内容，如：

​ substring_index(str,’.’,-2)

​ 结果为：s.z

​ 有人会问，如果我要中间的的s怎么办？

​ 很简单的，两个方向：

​ 从右数第二个分隔符的右边全部，再从左数的第一个分隔符的左边：

substring_index(substring_index(str,’.’,-2),’.’,1);

原始数据

处理结果展示

三种方式，相同的原理

• 使用MySql库中的自增序列表

SELECT
    a.id,
    a.email,
    substring_index(
        substring_index(
            a.name,
            ',',
            b.help_topic_id + 1
        ),
        ',' ,- 1
    ) AS splitName
FROM
    t_emp a
JOIN mysql.help_topic b ON b.help_topic_id < (
    length(a.name) - length(
        REPLACE (a.name, ',', '')
    ) + 1
)

• 自建自增序列表

CREATE TABLE `auto_increment` (
`id` int(20) DEFAULT NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8;

INSERT INTO `auto_increment` VALUES ('0'); 
INSERT INTO `auto_increment` VALUES ('1');
INSERT INTO `auto_increment` VALUES ('2');
INSERT INTO `auto_increment` VALUES ('3');
INSERT INTO `auto_increment` VALUES ('4');

SELECT
    a.id,
    a.email,
    substring_index(
        substring_index(
            a.name,
            ',',
            b.id+ 1
        ),
        ',' ,- 1
    ) AS splitName
FROM
    t_emp a
JOIN auto_increment b ON b.id < (
    length(a.name) - length(
        REPLACE (a.name, ',', '')
    ) + 1
)

• 以数据库里已有的表，构建自增序列表

SELECT 
    a.id,
    a.email,
    substring_index(
        substring_index(
            a.name,
            ',',
            b.id+1
        ),
        ',',-1
    ) AS splitName
FROM
    t_emp a
join
(
    SELECT 
    (
        @ROW :=@Row + 1
    ) AS id 
    FROM 
    t_user,
    (
        SELECT @Row:=-1
    ) AS zz
) AS b ON b.id < (
    length(a.name) - length(
        replace(a.name,',','')
    ) + 1
)
ORDER BY a.id

t_user表是库里已有表(可以不是序列表)，行数必须大于分割字段的最大逗号数

小结

1. 序列表必须从0开始，行数与最多的逗号个数有关，行数至少比最长逗号个数加1，可以建0~1000。（其实也可以不从0开始，但是sql语句要做相对应的修改）

2. 为什么不用MySQL自带的自增序列表mysql.help_topic？因为好多公司的数据库是没有权限操作这些表的, 不能使用。

3. 序列表必须从0开始，行数与最多的逗号个数有关，行数至少比最长逗号个数加1，可以建0~1000。（其实也可以不从0开始，但是sql语句要做相对应的修改）

4. 为什么不用MySQL自带的自增序列表mysql.help_topic？因为好多公司的数据库是没有权限操作这些表的, 不能使用。

参考博客