牛魔王的故事
MySQL没有临时表重用,并且函数不返回行。我在Stack Overflow中找不到任何东西可以将csv整数字符串转换为行,因此我在MySQL中编写了自己的代码。DELIMITER $$DROP PROCEDURE IF EXISTS str_split $$CREATE PROCEDURE str_split(IN str VARCHAR(4000),IN delim varchar(1))beginDECLARE delimIdx int default 0;DECLARE charIdx int default 1;DECLARE rest_str varchar(4000) default '';DECLARE store_str varchar(4000) default '';create TEMPORARY table IF NOT EXISTS ids as (select parent_item_id from list_field where 1=0);truncate table ids;set @rest_str = str;set @delimIdx = LOCATE(delim,@rest_str);set @charIdx = 1;set @store_str = SUBSTRING(@rest_str,@charIdx,@delimIdx-1);set @rest_str = SUBSTRING(@rest_str from @delimIdx+1);if length(trim(@store_str)) = 0 then set @store_str = @rest_str;end if; INSERT INTO idsSELECT (@store_str + 0);WHILE @delimIdx <> 0 DO set @delimIdx = LOCATE(delim,@rest_str); set @charIdx = 1; set @store_str = SUBSTRING(@rest_str,@charIdx,@delimIdx-1); set @rest_str = SUBSTRING(@rest_str from @delimIdx+1);select @store_str; if length(trim(@store_str)) = 0 then set @store_str = @rest_str; end if; INSERT INTO ids(parent_item_id) SELECT (@store_str + 0);END WHILE;select parent_item_id from ids;end$$DELIMITER ;call str_split('1,2,10,13,14',',')如果不使用整数,则还需要转换为其他类型。