利用MySQL存储过程分割字符串

朴灿烈づ我的快乐病毒、 2022-01-06 04:55 266阅读 0赞

（转）[http://tec.5lulu.com/detail/104krn1e6p2w78d77.html][http_tec.5lulu.com_detail_104krn1e6p2w78d77.html]

现有一段字符串，如apple,banana,orange,pears,grape，要把它按照逗号(,)分割成：

apple  
banana  
orange  
pears  
grape

然后使用where in()方法可以查询。

**1、具体函数：**

1.  \# 函数：func\_split\_TotalLength 
2.  DELIMITER $$ 
3.  DROP function IF EXISTS \`func\_split\_TotalLength\` $$ 
4.  CREATE DEFINER=\`root\`@\`%\` FUNCTION \`func\_split\_TotalLength\`
5.  (f\_string varchar(1000),f\_delimiter varchar(5)) RETURNS int(11)
6.  BEGIN
7.  \# 计算传入字符串的总length 
8.  return1\+(length(f\_string)\- length(replace(f\_string,f\_delimiter,'')));
9.  END$$ 
10. DELIMITER;

1.  \# 函数：func\_split 
2.  DELIMITER $$ 
3.  DROP function IF EXISTS \`func\_split\` $$ 
4.  CREATE DEFINER=\`root\`@\`%\` FUNCTION \`func\_split\`
5.  (f\_string varchar(1000),f\_delimiter varchar(5),f\_order int) RETURNS varchar(255) CHARSET utf8 
6.  BEGIN
7.  \# 拆分传入的字符串，返回拆分后的新字符串 
8.   declare result varchar(255)default'';
9.  set result = reverse(substring\_index(reverse(substring\_index(f\_string,f\_delimiter,f\_order)),f\_delimiter,1));
10. return result;
11. END$$ 
12. DELIMITER;

1.  \# [存储过程][Link 1]：splitString 
2.  DELIMITER $$ 
3.  DROP PROCEDURE IF EXISTS \`splitString\` $$ 
4.  CREATE PROCEDURE \`splitString\`
5.  (IN f\_string varchar(1000),IN f\_delimiter varchar(5))
6.  BEGIN
7.  \# 拆分结果 
8.  declare cnt intdefault0;
9.  declare i intdefault0;
10. set cnt = func\_split\_TotalLength(f\_string,f\_delimiter);
11. DROP TABLE IF EXISTS \`tmp\_split\`;
12. create temporary table \`tmp\_split\`(\`status\` varchar(128)notnull) DEFAULT CHARSET=utf8;
13. while i < cnt 
14. do
15. set i = i \+1;
16.  insert into tmp\_split(\`status\`) values (func\_split(f\_string,f\_delimiter,i));
17. endwhile;
18. END$$ 
19. DELIMITER;

**2、测试是否能成功分割**

1.  call splitString("apple,banana,orange,pears,grape",",");
2.  select\*from tmp\_split;

运行结果如下，说明分割成功：

**3、应用where in()查询**

1.  1.  \# 先传入字符串，分割后保存在[临时表][Link 2]tmp\_split中 
    2.  call splitString("apple,banana,orange,pears,grape",",");
    3.  \# 将查询的结果作为其他查询的条件来使用 
    4.  select\*from fruit wherein(select\*from tmp\_split);

转载于:https://www.cnblogs.com/hyzhou/p/4511690.html

[http_tec.5lulu.com_detail_104krn1e6p2w78d77.html]: http://tec.5lulu.com/detail/104krn1e6p2w78d77.html
[Link 1]: http://www.5lulu.com/tec/search.html?wd=WENCAT:%E5%AD%98%E5%82%A8%E8%BF%87%E7%A8%8B
[mysql]: http://www.5lulu.com/tec/search.html?wd=WENCAT:mysql
[Link 2]: http://www.5lulu.com/tec/search.html?wd=WENCAT:%E4%B8%B4%E6%97%B6%E8%A1%A8