mysql 实现list （也就是Oracle 数组问题）（存储过程）

数据库一直通常会遇到不兼容的问题，例如Oracle里面有数组的概念，但是mysql是没有的，那么在将oracle里面的存储过程移植过mysql里面就要将代码改写。

1.传入数组

首先：mysql不支持数组，所以办法就是传入list的时候，mysql采用的是用分隔符分隔开，再组成一串字符传递进来,一般选择逗号为分隔符。
例子：总共有三个id传入 分别是 A 和B 和C
那么在传入mysql的时候就要组成 A,B,C。

这里解决了传入的问题，但是在存储过程中总是要遇到读取相应位置的元素，替换相应位置的元素，还有数组的长度是多少，进行遍历。

2.获取根据分隔符获取数组的长度

获取字符串的长度，例如 A,B,C 是3个元素，长度应该为3

CREATE DEFINER=`root`@`192.168.1.%` FUNCTION `array_count`(
IN_STR1 varchar(2500)
) RETURNS int(11)
BEGIN

###############################
## 根据,分隔符 算出这个str里面含有的元素的个数
## sparator_count('12,32,5'); 返回 3
##################################

declare result int(10) default 1;
declare postion int(10);
declare str1 varchar(2500);
declare sparator varchar(1) DEFAULT ',';
set str1=IN_STR1;

lp1:while 1=1 do
set postion=instr(str1,sparator);
if postion>0 -- 找到了第一个遇到的分隔符位置
then 
  set result=result+1;
	if(postion<length(str1))-- 说明还有下一次可以找
	then 
	set str1=SUBSTR(str1,postion+1); 
	else leave lp1;
	end if;
else 
leave lp1;
end if;
end while;

return result;
END

3.获取数组相应位置的元素

该方法是在 字符串中找到相应的位置的元素值。A,B,C 第二个元素应该是B。

CREATE DEFINER=`root`@`192.168.1.%` FUNCTION `get_element`(
IN_STR1 varchar(2500),#父字符串
arrayindex int # 下标  从1 开始
) RETURNS varchar(100) CHARSET utf8
BEGIN

###############################
## 根据,去获取第几个元素
## getByArrayIndex('123,12,3,4',2); 返回 12
##################################

declare result varchar(100);
declare postion int(10);
declare str1 varchar(2500);
declare sparator varchar(1) DEFAULT ',';

set str1=IN_STR1;
set result=IN_STR1;

lp1:while arrayindex>0 do
set arrayindex=arrayindex-1;-- 进行了一次查找
set postion=instr(str1,sparator);
if postion>0 -- 找到了第一个遇到的分隔符位置
then 
  set result=SUBSTR(str1,1,postion-1); -- 分隔符前的元素 
	if(postion<length(str1))-- 说明还有下一次可以找
	then 
	set str1=SUBSTR(str1,postion+1); -- 下一次需要的查找的str
	else leave lp1;
	end if;
else -- 没找到
leave lp1;
end if;
end while;

if arrayindex>0 -- 说明没查找到对于的元素就被迫离开
then  set result='error';
end if;

return result;
END

4.替换相应位置的元素

替换相应位置的元素，然后结果是新的整条数据。

CREATE DEFINER=`root`@`192.168.1.%` FUNCTION `replace_element`(
IN_STR1 varchar(2500),#父字符串
arrayindex int, # 第几个元素  从1 开始
IN_STR2 varchar(200) # 需要替换的内容
) RETURNS varchar(2500) CHARSET utf8
BEGIN

###############################
## 根据,去修改相应下标的内容
## replace_element('123,12,3,4',2,'cc');  执行结果 '123,cc,3,4'  
##################################


declare sparator varchar(1) DEFAULT ',';
declare result varchar(2500);
declare postion1 int(10);
declare postion2 int(10);

set postion1 =instr1(IN_STR1,sparator,1,arrayindex-1);-- 这个元素的前面那个,的位置
set postion2 =instr1(IN_STR1,sparator,1,arrayindex);-- 这个元素后面的那个,的位置


if postion1=0 and arrayindex<>1 -- 说明根本没有这个元素
then  return 'error';
end if;


if postion2 = 0 -- 说明是最后一个元素
then set result= concat(substr(IN_STR1,1,postion1),IN_STR2);
else set result= concat(substr(IN_STR1,1,postion1),IN_STR2,substr(IN_STR1,postion2));
end if;






return result;
END

5.在父字符串中，子字符串，从某个起始位置开始，第几次出现的下标（替换的方法里面用到了）

CREATE DEFINER=`root`@`192.168.1.%` FUNCTION `instr1`(IN_STR1 varchar(2500),#父字符串
IN_STR2 varchar(2500),#需要查询的字符串
IN_STARTPOS int(10),#起始位置
IN_NUM int(10)) RETURNS int(11)
BEGIN

###############################
## instr1('12325','2',4,1); 返回 4
##################################

declare str1_length int(10);
declare str2_length int(10);
declare result int(10) default 0;
declare postion int(10);
declare str1 varchar(2500);
declare NUM int(10);
declare i int(10);

set str1_length=length(IN_STR1);
set str2_length=length(IN_STR2);

#字符串长度+起始位置-1 大于父字符串 那么返回0
if(str1_length<(str2_length+IN_STARTPOS-1)) then return 0;end if;

##次数小于1
if IN_NUM<1  THEN RETURN 0; end if;

set str1=SUBSTR(IN_STR1,IN_STARTPOS);##获取第一次的父
set result=IN_STARTPOS-1;##获取初始位置
set NUM=IN_NUM;

lp1:while NUM>0 do
 set postion=instr(str1,IN_STR2);
 if postion=0 then set result=0; leave lp1; end if;##只要有一次没找到就是失败
  set result=result+postion;
  
	set NUM=NUM-1;
	if NUM>0 THEN set str1=SUBSTR(str1,postion+1);end if;#获取下一次的父
	
end while;


return result;
END

6.完整的遍历的写法

在有了以上的几个方法之后，我们就可以在存储过程中循环遍历传入的list了。

CREATE DEFINER=`root`@`192.168.1.%` PROCEDURE `test`( 
IN `liststr` varchar(200), -- 数据应该是 A,B,C,D
INOUT `OUT_Return_Code` varchar(200)
)
BEGIN
declare i int;
declare count in;
declare current varchar(10);

set i=1;
set count=array_count(liststr);
while i<=count
do

-- 里面需要执行的内容开始（这里只是单纯的取当前下标的数据，并且赋值给current）
set current=get_element(liststr, i);
-- 执行的内容结束

set i=i+1;
end while;

 
 
END

注意：由于oracle之类的list里面是可以塞null值的，所以我这边都是允许两个逗号直接相邻的，这里面代表的是null值，或者说空值，但是由于mysql里面null和空是不一样的，所以这代表什么就由你们自己决定了。