MySQL中实现分割字符串的方法

比如现在有一字符串：1,2,44,5,666,29232
要把它按照逗号分割成：
1
2
44
5
666
29232
而且还要求它的总数。这个我以前写过。不过今天规范一下。

1、具体函数

SQL code

DELIMITER $$

CREATE DEFINER = `root`@` % ` FUNCTION `func_get_split_string_total`(
f_string varchar ( 1000 ),f_delimiter varchar ( 5 )
) RETURNS int ( 11 )
BEGIN

-- Get the total number of given string.
return 1 + (length(f_string) - length( replace (f_string,f_delimiter, '' )));
END $$

DELIMITER ;

SQL code

DELIMITER $$


CREATE DEFINER = `root`@` % ` FUNCTION `func_get_split_string`(
f_string varchar ( 1000 ),f_delimiter varchar ( 5 ),f_order int ) RETURNS varchar ( 255 ) CHARSET utf8
BEGIN

-- Get the separated number of given string.
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 $$

DELIMITER ;

测试的存储过程：

DELIMITER $$

CREATE PROCEDURE `sp_print_result`(
 IN f_string varchar(1000),IN f_delimiter varchar(5)
)
BEGIN
  -- Get the separated string.
  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_print;
  create temporary table tmp_print (num int not null);
  while i < cnt
  do
    set i = i + 1;
    insert into tmp_print(num) values (func_get_split_string(f_string,f_delimiter,i));
  end while;
  select * from tmp_print;
  
END$$

DELIMITER ;

2、来做一下测试
   mysql> call sp_print_result('1,2,44,5,666,29232',',');
+-------+
| num   |
+-------+
|     1 |
|     2 |
|    44 |
|     5 |
|   666 |
| 29232 |
+-------+
6 rows in set (0.01 sec)

Query OK, 0 rows affected (0.01 sec)