MySQL split存储过程
该存储过程类似于Java的split方法,需要传入两个参数,需要 分割的字符串 和 分割字符,分割字符串默认设置为1000长度,分割后的字符串每个长度设置的为50,若超过设定长度,则会报 1406 - Data too long for column 'str' at row 1,请自行修改str字段的长度;
DELIMITER $$
DROP PROCEDURE IF EXISTS `split`$$
CREATE DEFINER=`root`@`%` PROCEDURE `split`(in splitStr varchar(1000), in splitChar varchar(2))
BEGIN
SET @i = 0;
CREATE TEMPORARY TABLE IF NOT EXISTS split_tab(str varchar(50) NOT NULL);
TRUNCATE TABLE split_tab;
SET @cnt = 1 + (LENGTH(splitStr) - LENGTH(REPLACE(splitStr,splitChar,'')));
SET @i = 1;
START TRANSACTION;
WHILE @i <= @cnt DO
SET @result = REPLACE(SUBSTRING(SUBSTRING_INDEX(splitStr, splitChar, @i),
LENGTH(SUBSTRING_INDEX(splitStr, splitChar, @i -1)) + 1),
splitChar, '');
INSERT INTO split_tab(str) VALUES (@result);
SET @i = @i + 1;
END WHILE;
COMMIT;
SELECT * FROM split_tab;
END;
验证:
CALL split('aaa,bbb,ccc',',');
执行结果: