分隔符字段转多列查询

背景
在开发中，有可能会遇到数据库一个字段存储了由分隔符分割的多个字符串，而我们需要将这些分割的字符串转为多列进行封装，下面是如何通过sql直接实现的例子。

建表test

create table test
(
    id    varchar(30)  not null,
    name  varchar(30)  null,
    depts varchar(255) null,
    constraint test_pk
        primary key (id)
);

插入数据

insert into test
values ('demo', 'Demo Group',
        '感染性疾病科室(南院区)/心脏内一科门诊(南院区)/妇产药房/儿科药房/急诊药房/门诊西药房/门诊中药房/中草药库/西成药库/中药煎药室')
     , ('Inpatient', 'Inpatient Admission', '心脏内一科门诊(南院区)')
     , ('innurse', 'innurse', '心脏内一科门诊(南院区)')
     , ('med', 'med manager', '门诊部/病案室/医务科/病案室/门诊收费处(北院区)/医务科/医务科');

sql查询的思路是使用一张指针表配合函数substring_index实现
新建指针表

create table s_split_index
(
    id int
);

插入指针数据

insert into s_split_index
values (0),(1),(2),(3),(4),(5),(6),(7),(8),(9),(10),(11),(12),(13),(14),(15),(16);

sql实现

SELECT
	a.`user_id`,
	substring_index( substring_index( a.`depts`, '/', b.`id` + 1 ), '/',- 1 ) AS dept_id
FROM
	( SELECT `depts`, `id` AS user_id FROM test) AS a
	JOIN `s_split_index` AS b ON b.`id` < ( char_length( a.`depts` ) - char_length( REPLACE ( a.`depts`, '/', '' ) ) + 1 )
order by user_id;

这条sql是利用指针表先分割出左边的字符串部分，然后再利用指针-1取出右边的一个字符串以达到分割的目的。