MySql split一个字符串

1.函数func_get_split_string_total：将字符串按指定方式分割，并计算单元总数

CREATE DEFINER=`root`@`localhost` FUNCTION `func_get_split_string_total`(f_string varchar(1000),f_delimiter varchar(5)) RETURNS int(11)
BEGIN
  return 1+(length(f_string) - length(replace(f_string,f_delimiter,"")));
END

CREATE DEFINER=`root`@`localhost` FUNCTION `func_get_split_string`(f_string varchar(1000),f_delimiter varchar(5),f_order int) RETURNS varchar(255) CHARSET utf8
BEGIN
  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

</pre><pre code_snippet_id="498294" snippet_file_name="blog_20141027_3_655983" name="code" class="sql">3.过程splitString 将字符串分割，并放到临时表tmp_split 里面

CREATE DEFINER=`root`@`localhost` PROCEDURE `splitString`(IN f_string varchar(1000),IN f_delimiter varchar(5))
BEGIN      
    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 tmp_split;    
    create temporary table tmp_split (val_ varchar(128) not null) DEFAULT CHARSET=utf8;    
 
    while i < cnt    
    do    
        set i = i + 1;    
        insert into tmp_split(val_) values (func_get_split_string(f_string,f_delimiter,i));    
    end while;    
END

</pre><p>4.使用</p><p></p><pre code_snippet_id="498294" snippet_file_name="blog_20141027_7_2403259" name="code" class="sql">call splitString((select fileids from exampaper where id=90),";"); 

select * from xmlinfo where id in (SELECT * from tmp_split)