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

深碍√TFBOYSˉ_ 2022-08-24 04:22 260阅读 0赞

**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)

From：[http://blog.chinaunix.net/u/29134/showart\_1002486.html][http_blog.chinaunix.net_u_29134_showart_1002486.html]

[http_blog.chinaunix.net_u_29134_showart_1002486.html]: http://blog.chinaunix.net/u/29134/showart_1002486.html