DELIMITER $$DROP FUNCTION IF EXISTS `test`.`func_split_Total`$$CREATE FUNCTION `func_split_Total` (f_string varchar(1000),f_delimiter varchar(5)) RETURNS int(11)BEGIN
return 1+(length(f_string) - length(replace(f_string,f_delimiter,'')));
END$$
DELIMITER;
-------------------------------------------------------------------------
DELIMITER $$DROP FUNCTION IF EXISTS `test`.`func_split`$$CREATE FUNCTION `test`.`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 $$DROP PROCEDURE IF EXISTS `test`.`INSERTTREE`$$CREATE PROCEDURE `test`.`INSERTTREE`
(
IN f_string varchar(1000),
IN f_delimiter varchar(5)
)
BEGIN declare count int default 0; declare i int default 0; set count = func_split_Total(f_string, f_delimiter); DROP TABLE IF EXISTS `test`.`tmp_table`; create temporary table `test`.`tmp_table`(`field` varchar(128)) DEFAULT CHARSET=utf8; while i < count do set i = i + 1; select func_split(f_string, f_delimiter, i); #insert into `test`.`tmp_table`(`field`) values (func_split(f_string, f_delimiter, i)); end while;END$$DELIMITER ;
return 1+(length(f_string) - length(replace(f_string,f_delimiter,'')));
END$$
DELIMITER;
-------------------------------------------------------------------------
DELIMITER $$DROP FUNCTION IF EXISTS `test`.`func_split`$$CREATE FUNCTION `test`.`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 $$DROP PROCEDURE IF EXISTS `test`.`INSERTTREE`$$CREATE PROCEDURE `test`.`INSERTTREE`
(
IN f_string varchar(1000),
IN f_delimiter varchar(5)
)
BEGIN declare count int default 0; declare i int default 0; set count = func_split_Total(f_string, f_delimiter); DROP TABLE IF EXISTS `test`.`tmp_table`; create temporary table `test`.`tmp_table`(`field` varchar(128)) DEFAULT CHARSET=utf8; while i < count do set i = i + 1; select func_split(f_string, f_delimiter, i); #insert into `test`.`tmp_table`(`field`) values (func_split(f_string, f_delimiter, i)); end while;END$$DELIMITER ;
技术重在分享,顶楼主。