MySQL 没有提供 split 函数,但可以自己建立一个存储过程,将具有固定分隔符的字符串转成多行。之所以不能使用自定义函数实现此功能,是因为 MySQL 的自定义函数自能返回标量值,不能返回多行结果集。
MySQL 8:
drop procedure if exists sp_split;
delimiter //
create procedure sp_split(p_str text, p_delimiter varchar(100))
begin
select substring_index(substring_index(p_str, p_delimiter, lv), p_delimiter, - 1) a
from (
with recursive tab1(lv) as (
select 1 lv
union all
select t1.lv + 1 from tab1 t1
where lv < (char_length(p_str) - char_length(replace(p_str, p_delimiter, ''))) / (char_length(p_delimiter)) + 1)
select * from tab1
) t1;
end;
//
delimiter ;
MySQL 5:
drop procedure if exists sp_split;
delimiter //
create procedure sp_split(p_str text, p_delimiter varchar(100))
begin
select substring_index(substring_index(p_str, p_delimiter, help_topic_id + 1), p_delimiter, - 1) a
from mysql.help_topic
where help_topic_id < (char_length(p_str) - char_length(replace(p_str, p_delimiter, ''))) / (char_length(p_delimiter)) + 1;
end;
//
delimiter ;
测试:文章来源:https://www.toymoban.com/news/detail-670847.html
mysql> set @a:='123,456,789';
Query OK, 0 rows affected (0.00 sec)
mysql> call sp_split(@a,',');
+------+
| a |
+------+
| 123 |
| 456 |
| 789 |
+------+
3 rows in set (0.00 sec)
Query OK, 0 rows affected (0.00 sec)
mysql> set @a:='中;;English;;混杂;;多字符分隔符';
Query OK, 0 rows affected (0.00 sec)
mysql> call sp_split(@a,';;');
+--------------------+
| a |
+--------------------+
| 中 |
| English |
| 混杂 |
| 多字符分隔符 |
+--------------------+
4 rows in set (0.00 sec)
Query OK, 0 rows affected (0.00 sec)
返回指定下标的元素:文章来源地址https://www.toymoban.com/news/detail-670847.html
set collation_database=utf8mb4_0900_ai_ci;
set collation_server=utf8mb4_0900_ai_ci;
drop function if exists fn_split;
delimiter $$
create function fn_split ( s text , del char(1) , i int)
returns varchar(1024)
deterministic -- always returns same results for same input parameters
sql security invoker
begin
declare n int ;
-- get max number of items
set n = length(s) - length(replace(s, del, '')) + 1;
if i > n then
return null ;
else
return substring_index(substring_index(s, del, i) , del , -1 ) ;
end if;
end$$
delimiter ;
# 调用
mysql> set @s:='123|345|789';
Query OK, 0 rows affected (0.00 sec)
mysql> select fn_split(@s,'|',1) i1, fn_split(@s,'|',2) i2, fn_split(@s,'|',3) i3;
+------+------+------+
| i1 | i2 | i3 |
+------+------+------+
| 123 | 345 | 789 |
+------+------+------+
1 row in set (0.00 sec)
到了这里,关于MySQL 自定义 split 存储过程的文章就介绍完了。如果您还想了解更多内容,请在右上角搜索TOY模板网以前的文章或继续浏览下面的相关文章,希望大家以后多多支持TOY模板网!