mysql分割字符串成临时表_Mysql分割字符串

DELIMITER $$

DROP PROCEDURE IF EXISTS sp_split_str $$

CREATE PROCEDURE sp_split_str(

IN str VARCHAR(1000),

IN split VARCHAR(2)

)

BEGIN

DECLARE sub_str VARCHAR(1000);

DECLARE pos INT(11);

DROP TEMPORARY TABLE IF EXISTS temp_str;

CREATE TEMPORARY TABLE temp_str(

str VARCHAR(100)

)ENGINE = INNODB DEFAULT CHARSET=utf8;

SELECT LOCATE(split,str) INTO pos;

WHILE pos > 0 DO

BEGIN

-- 把','左边的字符串放到临时表中;

INSERT INTO temp_str(str) VALUES (LEFT(str,pos-1));

-- 把','右边的字符串重新赋值给str;

SELECT RIGHT(str,CHAR_LENGTH(str)-pos) INTO str;

-- 更新pos的值，进行下一次循环.

SELECT LOCATE(split,str) INTO pos;

END;

END WHILE;

-- 把最后一个字符串放到临时表中.

INSERT INTO temp_str(str) VALUES (str);

SELECT * FROM temp_str;

DROP TEMPORARY TABLE IF EXISTS temp_str;

END $$

DELIMITER ;