mysql 存储过程模拟数组

 1.创建存储过程

#创建获取每一个元素的函数
CREATE FUNCTION `func_get_split_string`(
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

#创建获取指定个数的函数
CREATE FUNCTION `func_get_split_string_total`(
f_string varchar(1000),f_delimiter varchar(5)
) RETURNS int(11)
BEGIN
  return 1+(length(f_string) - length(replace(f_string,f_delimiter,'')));
END

#获取每一个值
CREATE PROCEDURE `get_sub_str`(f_string varchar(1000),f_delimiter varchar(5))
begin

DECLARE total_count int;
set total_count = func_get_split_string_total(f_string, f_delimiter) ;

while total_count>0 DO 
#INSERT INTO sub_str(name) values (func_get_split_string(f_string,f_delimiter,total_count));

SET total_count=total_count-1;
end WHILE;

end

2.测试

"123,456",","

 

3.打印结果

结果1:456

结果2:123