解决问题

概述

整理数据时，有时候需要把以某分隔符分隔的字符串分割成多行并导入数据库表中，比如：

技术部 刘一,陈二,张三 
销售部 李四,王五,赵六,孙七 
行政部 周八,吴九,郑十

需要分割成下面的格式放到数据库表中：

技术部 刘一 
技术部 陈二 
技术部 张三 
销售部 李四 
销售部 王五 
销售部 赵六 
销售部 孙七 
行政部 周八 
行政部 吴九 
行政部 郑十

截图字符串的函数

我们先来看看MySQL中的几个函数的使用

1. LEFT(str, length)

LEFT函数用于从一个字符串中提取左侧的字符，提取的字符数由length参数指定。

示例代码如下：

SELECT LEFT('Hello World', 5);

上面的sql将返回字符串'Hello'，因为我们指定了从左侧开始截取5个字符。

2. RIGHT(str, length)

RIGHT函数用于从一个字符串中提取右侧的字符，提取的字符数由length参数指定。

SELECT RIGHT('Hello World', 5);

上面的sql将返回字符串'World'，因为我们指定了从右侧开始截取5个字符。

3. SUBSTRING_INDEX(str, delim, count)

参数str：被截取的字符串

参数delim：分隔符

参数count：第几个分隔符，为正数时，截取从左往右第几个分隔符左边的内容，为负数时，截取从右往左第几个分隔符右边的内容

SELECT SUBSTRING_INDEX('刘一,陈二,张三', ',', 2);

上面的sql将返回字符串'刘一,陈二'，因为我们指定了截取从左往右第2个逗号左边的内容

SELECT SUBSTRING_INDEX('刘一,陈二,张三', ',', -2);

上面的sql将返回字符串'陈二,张三'，因为我们指定了截取从右往左第2个逗号右边的内容

如果我们想将'刘一,陈二,张三’分割成三列，那么sql如下：

SELECT SUBSTRING_INDEX('刘一,陈二,张三', ',', 1) as column1, 
SUBSTRING_INDEX(SUBSTRING_INDEX('刘一,陈二,张三', ',', 2), ',', -1) as column2, 
SUBSTRING_INDEX('刘一,陈二,张三', ',', -1) as column3;

分割成多行

需要借助一张序号从0开始的自增表

CREATE TABLE `auto_increment_table` ( 
`id` int(11) NOT NULL 
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COMMENT='自增表';

插入连续自增的序号，序号的最大值根据分割行数确定，这里插入15行

insert into auto_increment_table(id) values(0); 
insert into auto_increment_table(id) values(1); 
insert into auto_increment_table(id) values(2); 
insert into auto_increment_table(id) values(3); 
insert into auto_increment_table(id) values(4); 
insert into auto_increment_table(id) values(5); 
insert into auto_increment_table(id) values(6); 
insert into auto_increment_table(id) values(7); 
insert into auto_increment_table(id) values(8); 
insert into auto_increment_table(id) values(9); 
insert into auto_increment_table(id) values(10); 
insert into auto_increment_table(id) values(11); 
insert into auto_increment_table(id) values(12); 
insert into auto_increment_table(id) values(13); 
insert into auto_increment_table(id) values(14);

创建部门和用户关系表：

CREATE TABLE `test_dept_user` ( 
`id` int(11) NOT NULL AUTO_INCREMENT COMMENT '自增主键id', 
`dept` varchar(16) NOT NULL DEFAULT '' COMMENT '部门', 
`users` varchar(32) NOT NULL DEFAULT '' COMMENT '用户', 
PRIMARY KEY (`id`) 
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COMMENT='部门和用户关系-测试表';

插入数据：

insert into test_dept_user(dept, users) values('技术部','刘一,陈二,张三');
insert into test_dept_user(dept, users) values('销售部','李四,王五,赵六,孙七');
insert into test_dept_user(dept, users) values('行政部','周八,吴九,郑十');

将部门和用户的关系分割成多行：

select tdu.dept, substring_index(substring_index(tdu.users, ',', ait.id+1), ',', -1) as user 
from test_dept_user tdu cross join auto_increment_table ait on ait.id<=length(tdu.users)-length(replace(tdu.users,',','')) 
order by tdu.id

此sql的显示结果如下：