-- 信任子程序的创建者,禁止创建、修改子程序时对SUPER权限的要求,设置log_bin_trust_routine_creators全局系统变量为1
SET GLOBAL log_bin_trust_function_creators = 1;
-- 删除函数
drop function getTwolevBq ;
-- 在mysql中默认分号(;)为分隔符,也就是说,遇到分号,就认为你输入完了,需要系统执行了。但在创建函数的过程中,输入完一句命令,就需要使用分号分割,这样就产生了冲突。解决方案就是临时切换分隔符。
DELIMITER // -- 先切换成 //
-- 创建函数
-- piv_str1:查询条件(标签,多个值);piv_str2:库中bq_id字段
create FUNCTION getTwolevBq (piv_str1 varchar(4000), piv_str2 varchar(4000))
RETURNS INT
begin
-- l_idx:用于计算piv_str1中分隔符的位置; res:返回结果; loopIndex:循环次数
DECLARE l_idx, res,loopIndex int UNSIGNED DEFAULT 0;
-- str:根据分隔符截取的子字符串; piv_str: 将piv_str1赋值给piv_str
DECLARE str,piv_str varchar(4000);
-- p_sep:查询参数中的分隔符,默认为英文的,
DECLARE p_sep varchar(1);
SET p_sep = ',', piv_str = piv_str1;
-- 如果piv_str中没有分割符,直接判断piv_str1是否在piv_str2中存在,存在则 res=1
IF instr(piv_str, p_sep) = 0 THEN
IF LOCATE(piv_str,piv_str2) > 0 THEN
set res := 1;
END IF;
ELSE
-- 循环按分隔符截取piv_str
bq: LOOP
-- 从输入字符串 piv_str 中查找字符串p_sep 出现的位置,以字符计算
set l_idx := instr(piv_str, p_sep);
set loopIndex:=loopIndex+1;
-- 当piv_str中还有分隔符时
IF l_idx > 0 THEN
-- 截取第一个分隔符前的字段str
set str:= substr(piv_str, 1, l_idx-1);
-- 判断 str 是否在piv_str2中存在,存在 res=1 并继续循环判断
IF LOCATE(str,piv_str2) > 0 THEN
set res:= loopIndex;
-- EXIT;
END IF;
set piv_str := substr(piv_str, l_idx+length(p_sep));
ELSE
-- 当截取后的piv_str 中不存在分割符时,判断piv_str是否在piv_str2中存在,存在则 res=1
IF LOCATE(piv_str,piv_str2) > 0 THEN
set res:= loopIndex;
END IF;
-- 无论最后是否相等,都跳出循环
leave bq;
END IF;
END LOOP;
-- 结束循环
END IF;
-- 返回res
RETURN res;
END//
DELIMITER ; -- 再切换回去
MySQL手册中find_in_set函数的语法解释:
FIND_IN_SET(str,strlist)
str 要查询的字符串
strlist 字段名 参数以”,”分隔 如 (1,2,6,8,10,22)
查询字段(strlist)中包含(str)的结果,返回结果为null或记录
假如字符串str在由N个子链组成的字符串列表strlist 中,则返回值的范围在 1 到 N 之间。 一个字符串列表就是一个由一些被 ‘,' 符号分开的子链组成的字符串。如果第一个参数是一个常数字符串,而第二个是type SET列,则FIND_IN_SET() 函数被优化,使用比特计算。 如果str不在strlist 或strlist 为空字符串,则返回值为 0 。如任意一个参数为NULL,则返回值为 NULL。这个函数在第一个参数包含一个逗号(‘,')时将无法正常运行。