MySql使用存储过程分割字符串

最新推荐文章于 2022-03-08 10:52:55 发布

码丁农

最新推荐文章于 2022-03-08 10:52:55 发布

阅读量749

点赞数

分类专栏： MySQL 存储过程外键事务索引文章标签： mysql 存储 string function table insert

本文链接：https://blog.csdn.net/dyzhen/article/details/6253011

版权

MySQL 存储过程外键事务索引专栏收录该内容

20 篇文章 0 订阅

订阅专栏

DROP FUNCTION IF EXISTS func_split_TotalNum

CREATE DEFINER = root FUNCTION func_split_TotalNum
    -> (f_string varchar(1000), f_delimiter varchar(5))
    -> RETURNS int(11)
    -> BEGIN
    -> return 1 + (length(f_string) - length(replace(f_string, f_delimiter, '')));
    -> END

    DROP FUNCTION IF EXISTS func_split

    CREATE DEFINER = root FUNCTION func_split
    -> (f_string varchar(1000), f_delimiter varchar(5), f_order int)
    -> RETURNS varchar(255)
    -> BEGIN
    -> declare result varchar(255) default '';
    -> set result = reverse(substring_index(reverse(substring_index(f_string, f_delimiter, f_order)), f_delimiter, 1));
    -> return result;
    -> END

    CREATE PROCEDURE Pro_SplitString
    -> (IN SplitString VARCHAR(1000), IN SplitDeli VARCHAR(5))
    -> BEGIN
    -> DECLARE cnt INT DEFAULT 0;
    -> DECLARE i INT DEFAULT 0;
    -> SET cnt = func_split_TotalNum(SplitString, SplitDeli);
    -> DROP TABLE IF EXISTS TmpTable_Split
    -> ;
    -> CREATE TEMPORARY TABLE TmpTable_Split (PcName VARCHAR(32) NOT NULL);
    -> WHILE i < cnt
    -> DO
    -> SET i = i + 1;
    -> INSERT INTO TmpTable_Split (PcName) VALUES (func_split(SplitString, SplitDeli, i));
    -> END WHILE;
    -> END

    call Pro_SplitString("a,b,c", ",");

    select * from TmpTable_Split;


    create procedure Pro_GetSplitResult
    -> (in SplitString varchar(1000), in SplitDeli varchar(5))
    -> begin
    -> call Pro_SplitString(SplitString, SplitDeli);
    -> select * from TmpTable_Split;
    -> end