DELIMITER $$
DROP function IF EXISTS `func_split_TotalLength` $$
CREATE DEFINER = `root` @ `%` FUNCTION `func_split_TotalLength`
( f_string varchar ( 1000 ), f_delimiter varchar ( 5 )) RETURNS int ( 11 )
BEGIN
# 计算传入字符串的总length
return 1 +( length ( f_string ) - length ( replace ( f_string , f_delimiter , ‘‘ )));
END $$
DELIMITER ;
# 函数：func_split
DELIMITER $$
DROP function IF EXISTS `func_split` $$
CREATE DEFINER = `root` @ `%` FUNCTION `func_split`
( 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 $$
DELIMITER ;
# 存储过程 ：splitString
DELIMITER $$
DROP PROCEDURE IF EXISTS `splitString` $$
CREATE 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_split_TotalLength ( f_string , f_delimiter );
DROP TABLE IF EXISTS `tmp_split` ;
create temporary table `tmp_split` ( `status` varchar ( 128 ) not null ) DEFAULT CHARSET = utf8 ;
while i < cnt
do
set i = i + 1 ;
insert into tmp_split ( `status` ) values ( func_split ( f_string , f_delimiter , i ));
end while ;
END $$
DELIMITER ;
2、测试是否能成功分割
call splitString ( "apple,banana,orange,pears,grape" , "," );
select * from tmp_split ;
运行结果如下，说明分割成功：
mysql> call splitString("apple,banana,orange,pears,grape",",");
select * from tmp_split;
Query OK, 1 row affected
+--------+
| status |
+--------+
| apple |
| banana |
| orange |
| pears |
| grape |
+--------+
5 rows in set
mysql>
3、应用where in()查询
# 先传入字符串，分割后保存在 临时表 tmp_split中
call splitString ( "apple,banana,orange,pears,grape" , "," );
# 将查询的结果作为其他查询的条件来使用
select * from fruit where in ( select * from tmp_split );
利用MySQL存储过程分割字符串
标签：
查看更多关于利用MySQL存储过程分割字符串的详细内容...