幽灵资源网 Design By www.bzswh.com
1:定义存储过程,用于分隔字符串
DELIMITER $$ USE `mess`$$ DROP PROCEDURE IF EXISTS `splitString`$$ CREATE DEFINER=`root`@`%` PROCEDURE `splitString`(IN f_string VARCHAR(1000),IN f_delimiter VARCHAR(5)) BEGIN DECLARE cnt INT DEFAULT 0; DECLARE i INT DEFAULT 0; SET cnt = func_get_splitStringTotal(f_string,f_delimiter); DROP TABLE IF EXISTS `tmp_split`; CREATE TEMPORARY TABLE `tmp_split` (`val_` VARCHAR(128) NOT NULL) DEFAULT CHARSET=utf8; WHILE i < cnt DO SET i = i + 1; INSERT INTO tmp_split(`val_`) VALUES (func_splitString(f_string,f_delimiter,i)); END WHILE; END$$ DELIMITER ;
2:实现func_get_splitStringTotal函数:该函数用于计算分隔之后的长度,这里需要了解的函数:
REPLACE(str,from_str,to_str) Returns the string str with all occurrences of the string from_str replaced by the string to_str. REPLACE() performs a case-sensitive match when searching for from_str. 例如: mysql> SELECT REPLACE('www.mysql.com', 'w', 'Ww'); -> 'WwWwWw.mysql.com'
具体实现:
DELIMITER $$ USE `mess`$$ DROP FUNCTION IF EXISTS `func_get_splitStringTotal`$$ CREATE DEFINER=`root`@`%` FUNCTION `func_get_splitStringTotal`( f_string VARCHAR(10000),f_delimiter VARCHAR(50) ) RETURNS INT(11) BEGIN RETURN 1+(LENGTH(f_string) - LENGTH(REPLACE(f_string,f_delimiter,''))); END$$ DELIMITER ;
3:实现func_splitString函数:用于获取分隔之后每次循环的值,这里需要了解的函数:
(1)REVERSE(str) Returns the string str with the order of the characters reversed. 例如:mysql> SELECT REVERSE('abc'); -> 'cba' (2) SUBSTRING_INDEX(str,delim,count) Returns the substring from string str before count occurrences of the delimiter delim. If count is positive, everything to the left of the final delimiter (counting from the left) is returned. If count is negative, everything to the right of the final delimiter (counting from the right) is returned. SUBSTRING_INDEX() performs a case-sensitive match when searching for delim. 例如: mysql> SELECT SUBSTRING_INDEX('www.mysql.com', '.', 2); -> 'www.mysql' mysql> SELECT SUBSTRING_INDEX('www.mysql.com', '.', -2); -> 'mysql.com'
具体实现:
DELIMITER $$ USE `mess`$$ DROP FUNCTION IF EXISTS `func_splitString`$$ CREATE DEFINER=`root`@`%` FUNCTION `func_splitString`( f_string VARCHAR(1000),f_delimiter VARCHAR(5),f_order INT) RETURNS VARCHAR(255) CHARSET utf8 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$$ DELIMITER ;
使用:
(1)调用存储过程:
CALL splitString('1,3,5,7,9',',');
(2):查看临时表
SELECT val_ FROM tmp_split AS t1;
结果:
幽灵资源网 Design By www.bzswh.com
广告合作:本站广告合作请联系QQ:858582 申请时备注:广告合作(否则不回)
免责声明:本站文章均来自网站采集或用户投稿,网站不提供任何软件下载或自行开发的软件! 如有用户或公司发现本站内容信息存在侵权行为,请邮件告知! 858582#qq.com
免责声明:本站文章均来自网站采集或用户投稿,网站不提供任何软件下载或自行开发的软件! 如有用户或公司发现本站内容信息存在侵权行为,请邮件告知! 858582#qq.com
幽灵资源网 Design By www.bzswh.com
暂无评论...