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);
原始数据
处理结果展示
三种方式,相同的原理
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表是库里已有表(可以不是序列表),行数必须大于分割字段的最大逗号数
小结
-
序列表必须从0开始,行数与最多的逗号个数有关,行数至少比最长逗号个数加1,可以建0~1000。(其实也可以不从0开始,但是sql语句要做相对应的修改)
-
为什么不用MySQL自带的自增序列表mysql.help_topic?因为好多公司的数据库是没有权限操作这些表的, 不能使用。
-
序列表必须从0开始,行数与最多的逗号个数有关,行数至少比最长逗号个数加1,可以建0~1000。(其实也可以不从0开始,但是sql语句要做相对应的修改)
-
为什么不用MySQL自带的自增序列表mysql.help_topic?因为好多公司的数据库是没有权限操作这些表的, 不能使用。