mysql字符串分割函数（⾏转列）
由于⼯作需要需要处理⼀些以逗号分隔的字符串，每次都要现做很是⿇烦，⽹上了很多都没有现成的，好吧，⾃⼰动⼿写⼀个好了 1CREATE DEFINER = `harri`@`%` FUNCTION `str_for_substr`(`num` int, `str` varchar(5000))
2RETURNS varchar(100)
3BEGIN
4/*函数功能: 把带逗号的字符串分割取出
5参数: num 要取出的字符串的索引值, 以0开始
6                    str 以逗号分割的字符串
7扩展: 将逗号替换成其他符合，即可完成不同分隔符拆分字符串，亦可以把分隔符作为参数
8*/
9SET@str_for_substr=
10
11SUBSTRING(
12        SUBSTRING_INDEX(str, ',', num +1),
13CASE num
14WHEN0THEN
15        CHAR_LENGTH(
16            SUBSTRING_INDEX(str, ',', num)
17        ) +1
18ELSE
19        CHAR_LENGTH(
20            SUBSTRING_INDEX(str, ',', num)
21        ) +2
22END,
23CASE num
24WHEN0THEN
25    CHAR_LENGTH(
26        SUBSTRING_INDEX(str, ',', num +1)
27    ) - CHAR_LENGTH(
28        SUBSTRING_INDEX(str, ',', num)
29    )
30ELSE
31    CHAR_LENGTH(
32        SUBSTRING_INDEX(str, ',', num +1)
33    ) - CHAR_LENGTH(
34        SUBSTRING_INDEX(str, ',', num)
35    ) -1
36END
37    );
38
39
40RETURN@str_for_substr;
41END;
测试：
mysql> SELECT str_for_substr(3,'one,two,three,four,five,six,seven') as 'value';
+-------+
| value |
+-------+
| four |
+-------+
1 row in set
实例应⽤
mysql> select * from test;
+---+------------------------+
| a | b |
+---+------------------------+
| 1 | 你好，哈哈，不错 |
| 2 | 测试，不错 |
| 3 | test,test2,test3,test4 |
| 4 | 你好，哈哈，不错 |
| 5 | 你好，哈哈，不错 |
| 6 | 你好，哈哈，不错 |
| 7 | 你好，哈哈，不错 |
| 8 | 你好，哈哈，不错 |
+---+------------------------+
8 rows in set
创建存储过程如下
CREATE DEFINER = `root`@`%` PROCEDURE `split_str`()
SQL SECURITY INVOKER
BEGIN
DECLARE a1 varCHAR(20);
DECLARE b1 varchar(10000);
DECLARE done INT DEFAULT FALSE;
DECLARE cur CURSOR FOR SELECT a,b from test ;
DECLARE CONTINUE HANDLER FOR NOT FOUND SET done = TRUE; OPEN cur;
read_loop: LOOP
FETCH cur INTO a1,b1;
IF done THEN
LEAVE read_loop;
END IF;
SET@num= LENGTH(b1) - LENGTH(REPLACE(b1, ',', ''));
SET@i=0;
WHILE (@i<=@num ) DO
INSERT INTO test1
VALUES
(
a1,
mysql 字符串转数组str_for_substr(@i,b1)
)
;
set@i=@i+1;
END WHILE;
END LOOP;
CLOSE cur;
END;
执⾏结果如下
mysql> select * from test1;
+---+-------+
| a | b |
+---+-------+
| 1 | 你好 |
| 1 | 哈哈 |
| 1 | 不错 |
| 2 | 测试 |
| 2 | 不错 |
| 3 | test |
| 3 | test2 |
| 3 | test3 |
| 3 | test4 |
| 4 | 你好 |
| 4 | 哈哈 |
| 4 | 不错 |
| 5 | 你好 |
| 5 | 哈哈 |
| 5 | 不错 |
| 6 | 你好 |
| 6 | 哈哈 |
| 6 | 不错 |
| 7 | 你好 |
| 7 | 哈哈 |
| 7 | 不错 |
| 8 | 你好 |
| 8 | 哈哈 |
| 8 | 不错 |
+---+-------+ 24 rows in set

版权声明：本站内容均来自互联网，仅供演示用，请勿用于商业和其他非法用途。如果侵犯了您的权益请与我们联系QQ:729038198，我们将在24小时内删除。