DELIMITER $$
USE `mysql_data`$$ -- 数据库名称
DROP FUNCTION IF EXISTS `func_split_TotalLength`$$
-- user_name=用户名
CREATE DEFINER=`user_name`@`%` FUNCTION `func_split_TotalLength`(f_string VARCHAR(1000),f_delimiter VARCHAR(5)) RETURNS INT(11)
BEGIN
# 计算传入字符串的总length
RETURN 1+(LENGTH(f_string) - LENGTH(REPLACE(f_string,f_delimiter,'')));
END$$
DELIMITER ;
DELIMITER $$
USE `mysql_data`$$
DROP FUNCTION IF EXISTS `func_split`$$
-- user_name=用户名
CREATE DEFINER=`user_name`@`%` FUNCTION `func_split`(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 ;
DELIMITER $$
USE `mysql_data`$$
DROP PROCEDURE IF EXISTS `splitString`$$
-- user_name=用户名
CREATE DEFINER=`user_name`@`%` 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_split_TotalLength(f_string,f_delimiter);
DROP TABLE IF EXISTS `tmp_split`;
CREATE TEMPORARY TABLE `tmp_split` (`NAME1` VARCHAR(128) NOT NULL) DEFAULT CHARSET=utf8;
WHILE i < cnt
DO
SET i = i + 1;
INSERT INTO tmp_split(`NAME1`) VALUES (func_split(f_string,f_delimiter,i));
END WHILE;
END$$
DELIMITER ;
/*参数:要分隔的字符串,以什么分隔*/
CALL splitString('sdfsd,sdfsd,sdf,sdfsdf,sdfsd,sdfsdf',',')
SELECT * FROM tmp_split