本文详细介绍了如何利用SQL将数据一行变多行，重点讲解了REPLACE和SUBSTRING_INDEX函数的用法，以及在没有mysql.help_topic表权限时如何创建临时表来实现相同功能。内容包括创建测试数据、普通SQL实现、无权限时的处理办法以及函数的解释，展示了将name字段按逗号分割成多行记录的完整过程。
摘要由CSDN通过智能技术生成
一、测试数据
DROP TABLE IF EXISTS `test`;
CREATE TABLE IF NOT EXISTS `test` (
`id` bigint(20) NOT NULL AUTO_INCREMENT ,
`name` varchar(255) DEFAULT NULL,
`num` int(8),
PRIMARY KEY (`id`)
);
INSERT INTO `test`(`name`, `num`) VALUES ('a1,b258,c', 11);
INSERT INTO `test`(`name`, `num`) VALUES ('f,g123456,h,i85,j', 33);
INSERT INTO `test`(`name`, `num`) VALUES ('d,e1234', 22);
1
2
3
4
5
6
7
8
9
10
那么: SELECT * FROM test; :
在这里插入图片描述
那么，把数据转换成为下面这样，需要怎么样实现呢:
在这里插入图片描述
二、普通 sql 实现(需要依赖 mysql.help_topic 表)
SELECT
a.id,a.num,SUBSTRING_INDEX( SUBSTRING_INDEX( a.`name`, ',', b.help_topic_id + 1 ), ',',-1 ) name
FROM
test a
JOIN mysql.help_topic b ON b.help_topic_id < ( LENGTH( a.`name`) - LENGTH( REPLACE ( a.`name`, ',', '' ) ) + 1 );
1
2
3
4
5
三、mysql.help_topic 无权限处理办法
mysql.help_topic 的作用是对 SUBSTRING_INDEX 函数出来的数据(也就是按照分割符分割出来的)数据连接起来做笛卡尔积。
如果 mysql.help_topic 没有权限，可以自己创建一张临时表，用来与要查询的表连接查询。
获取该字段最多可以分割成为几个字符串:
SELECT MAX(LENGTH(a.`name`) - LENGTH(REPLACE(a.`name`, ',', '' )) + 1) FROM `test` a;
1
创建临时表，并给临时表添加数据:
注意:
临时表必须有一列从 0 或者 1 开始的自增数据
临时表表名随意，字段可以只有一个
临时表示的数据量必须比 MAX(LENGTH(a.name) - LENGTH(REPLACE(a.name, ',', '' )) + 1) 的值大
DROP TABLE IF EXISTS tmp_help_topic;
CREATE TABLE IF NOT EXISTS tmp_help_topic (
help_topic_id bigint(20) NOT NULL AUTO_INCREMENT ,
PRIMARY KEY (help_topic_id)
);
INSERT INTO tmp_help_topic() VALUES ();
INSERT INTO tmp_help_topic() VALUES ();
INSERT INTO tmp_help_topic() VALUES ();
INSERT INTO tmp_help_topic() VALUES ();
INSERT INTO tmp_help_topic() VALUES ();
INSERT INTO tmp_help_topic() VALUES ();
INSERT INTO tmp_help_topic() VALUES ();
INSERT INTO tmp_help_topic() VALUES ();
INSERT INTO tmp_help_topic() VALUES ();
INSERT INTO tmp_help_topic() VALUES ();
查询:
SELECT
a.id,a.num,SUBSTRING_INDEX(SUBSTRING_INDEX(a.`name`, ',', b.help_topic_id), ',',-1 ) name
FROM
test a
JOIN tmp_help_topic b ON b.help_topic_id <= (LENGTH( a.`name`) - LENGTH(REPLACE(a.`name`, ',', '')) + 1 );
1
2
3
4
5
四、函数的意思
4.1 REPLACE 函数:
把 字符串 a,b,c,d 里面的逗号替换成空字符串
SELECT REPLACE('a,b,c,d', ',', '');
-- 输出: abcd
1
2
3
那么:
# 获取逗号的个数
SELECT (LENGTH('a,b,c,d') - LENGTH(REPLACE('a,b,c,d', ',', '')));
# 按照逗号分割后会有几个元素，这里分割后就是 a b c d，就是 4 个元素
SELECT (LENGTH('a,b,c,d') - LENGTH(REPLACE('a,b,c,d', ',', '')) + 1);
1
2
3
4
5
4.2 SUBSTRING_INDEX 函数:
SUBSTRING_INDEX 是字符串截取函数
SUBSTRING_INDEX(str, delim, count)
1
str : 表示需要拆分的字符串
delim : 表示分隔符，通过某字符进行拆分
count : 当 count 为正数，取第 n 个分隔符之前的所有字符；当 count 为负数，取倒数第 n 个分隔符之后的所有字符。
例如:
SELECT SUBSTRING_INDEX('a*b*c*d', '*', 1); -- 返回: a
SELECT SUBSTRING_INDEX('a*b*c*d', '*', 2); -- 返回: a*b
SELECT SUBSTRING_INDEX('a*b*c*d', '*', 3); -- 返回: a*b*c
SELECT SUBSTRING_INDEX('a*b*c*d', '*', 4); -- 返回: a*b*c*d
SELECT SUBSTRING_INDEX('a*b*c*d', '*', -1); -- 返回: d
SELECT SUBSTRING_INDEX('a*b*c*d', '*', -2); -- 返回: c*d
SELECT SUBSTRING_INDEX('a*b*c*d', '*', -3); -- 返回: b*c*d
SELECT SUBSTRING_INDEX('a*b*c*d', '*', -4); -- 返回: a*b*c*d
1
2
3
4
5
6
7
8
那么:
SELECT SUBSTRING_INDEX(SUBSTRING_INDEX('a*b*c*d', '*', 1), '*', -1); -- 返回: a
SELECT SUBSTRING_INDEX(SUBSTRING_INDEX('a*b*c*d', '*', 2), '*', -1); -- 返回: b
SELECT SUBSTRING_INDEX(SUBSTRING_INDEX('a*b*c*d', '*', 3), '*', -1); -- 返回: c
SELECT SUBSTRING_INDEX(SUBSTRING_INDEX('a*b*c*d', '*', 4), '*', -1); -- 返回: d
1
2
3
4
五、一行变多行原理
回到 sql:
SELECT
a.id,a.num,SUBSTRING_INDEX(SUBSTRING_INDEX(a.`name`, ',', b.help_topic_id), ',',-1 )
FROM
test a
JOIN tmp_help_topic b ON b.help_topic_id <= (LENGTH( a.`name`) - LENGTH(REPLACE(a.`name`, ',', '')) + 1 );
1
2
3
4
5
SUBSTRING_INDEX(SUBSTRING_INDEX(a.name, ',', b.help_topic_id), ',',-1 ) 就是获取 tmp_help_topic 表的 help_topic_id 字段的值作为 name 字段的第几个子串
使用了 join 就会把字段 name 分为 (LENGTH( a.name) - LENGTH(REPLACE(a.name, ',', '')) + 1 ) 行，并且每行的字段刚好是 name 字段的第 help_topic_id 个子串
抄自：https://blog.csdn.net/qq_34832347/article/details/125807809
网友评论