mysql数组分割成多列

CREATE TABLE sub_array (
testName VARCHAR(30) DEFAULT NULL
) ENGINE=INNODB DEFAULT CHARSET=utf8;

– 分割函数
DROP FUNCTION IF EXISTS func_sub ;
DELIMITER //
CREATE FUNCTION func_sub(f_name VARCHAR(1000),f_comma VARCHAR(55),f_index INT)
RETURNS VARCHAR(255)
BEGIN
DECLARE result VARCHAR(255) DEFAULT ‘’;
SET result = REVERSE(SUBSTRING_INDEX(REVERSE(SUBSTRING_INDEX(f_name,f_comma,f_index)),f_comma,1));
RETURN result;
END//

– 拼接成多列列
DROP PROCEDURE IF EXISTS sub_proc ;
DELIMITER //
CREATE PROCEDURE sub_proc(IN f_name VARCHAR(1000))
BEGIN

DECLARE f_nameTemp VARCHAR(1000) DEFAULT ‘’;
DECLARE f_comma VARCHAR(1000) DEFAULT ‘,’;
DECLARE f_index INT(11) DEFAULT 1;
DECLARE end_while INT(11) DEFAULT 0;
DECLARE comma_index INT(11) DEFAULT 0;
DECLARE result VARCHAR(2000) DEFAULT ‘’;
DECLARE exe_sql VARCHAR(1000) DEFAULT ‘’;

SET f_nameTemp=f_name;
WHILE 0=end_while DO
SET comma_index=CAST(LOCATE(’,’,f_name) AS SIGNED INTEGER);
IF comma_index=0 THEN
SET end_while=1;
END IF;
SET f_name=SUBSTRING(f_name,LENGTH(SUBSTRING_INDEX(f_name,’,’,1))+2);
SET result=CONCAT_WS(’,’,result,CONCAT(‘func_sub(’,CONCAT(’’’’,f_nameTemp,’’’’),CAST(f_comma AS CHAR),CONCAT(’’’’,CAST(f_comma AS CHAR),’’’’),CAST(f_comma AS CHAR),f_index,’)’));
SET f_index = f_index+1;
SET comma_index=0;
END WHILE;
SET result=SUBSTRING(result,LENGTH(SUBSTRING_INDEX(result,’,’,0))+2);
SET exe_sql=CONCAT(‘select ‘,result,’ FROM sub_array’);
SET @exesql = exe_sql;
PREPARE Statement FROM @exesql;
EXECUTE Statement;
DEALLOCATE PREPARE Statement;
END//

CALL sub_proc(‘1,2,3,4,5,6,7,8’);