mysql中字符串分割并转换成列

     今天遇到这样的需求，需要将'1,2,3'转换成表：

        1 

        2 

        3 

--执行函数

DELIMITER $$
CREATE DEFINER=`db_develop`@`%` FUNCTION `func_get_split_string_total`(
f_string VARCHAR(1000),f_delimiter VARCHAR(5)
) RETURNS INT(11)
BEGIN
  -- Get the total number of given string.
  RETURN 1+(LENGTH(f_string) - LENGTH(REPLACE(f_string,f_delimiter,'')));
END$$
DELIMITER ;

--执行函数
DELIMITER $$
CREATE DEFINER=`db_develop`@`%` FUNCTION `func_get_split_string`(
f_string VARCHAR(1000),f_delimiter VARCHAR(5),f_order INT) RETURNS VARCHAR(255) CHARSET utf8
BEGIN
  -- Get the separated number of given string.
  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 $$
CREATE  PROCEDURE `sp_print_result`(
 IN f_string VARCHAR(1000),IN f_delimiter VARCHAR(5)
)
BEGIN
  -- Get the separated string.
  DECLARE cnt INT DEFAULT 0;
  DECLARE i INT DEFAULT 0;
  SET cnt = func_get_split_string_total(f_string,f_delimiter);
  DROP TABLE IF EXISTS TOP_SPLIT_TEMP;
  CREATE TEMPORARY TABLE TOP_SPLIT_TEMP (num INT NOT NULL);
  WHILE i < cnt
  DO
    SET i = i + 1;
    INSERT INTO TOP_SPLIT_TEMP(num) VALUES (func_get_split_string(f_string,f_delimiter,i));
  END WHILE;
  SELECT * FROM TOP_SPLIT_TEMP;
END$$
DELIMITER ;

--初始化临时表
CALL sp_print_result('1,3,4,5,6',',');

--查数据
SELECT num FROM TOP_SPLIT_TEMP;
1
3
4
5
6