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