自定义函数FIND_PART_IN_SET
该自定义函数的作用
该函数的作用是为了判断str1 和 str2 之间有没有一个相同的数
例如: 1,2,3 和 1 ,返回的结果大于0即有相同的数
1和 1,2,3 返回的结果大于0
1,2 和 2,3,4 返回的结果大于0
这种情况适用于想判断两个参数,只要有一个数匹配就行的情况。
DELIMITER //
CREATE FUNCTION FIND_PART_IN_SET(str1 TEXT, str2 TEXT)
RETURNS TEXT
BEGIN
DECLARE CURRENTINDEX INT; #当前下标
DECLARE CURRENTSTR TEXT;
DECLARE result INT;
DECLARE str1_tmp TEXT;
DECLARE str2_tmp TEXT;
SET result = 0;
SET CURRENTINDEX = 0;
SET CURRENTSTR = '';
IF str1 IS NOT NULL AND str1 != '' THEN
# 临时变量用来保存原始字符串
SET str1_tmp = str1;
SET str2_tmp = str2;
# 遍历str1的元素
SET CURRENTINDEX = LOCATE(',', str1_tmp);
WHILE CURRENTINDEX > 0 DO
SET CURRENTSTR = SUBSTRING(str1_tmp, 1, CURRENTINDEX - 1);
IF FIND_IN_SET(CURRENTSTR, str2_tmp) > 0 THEN
SET result = 1;
RETURN result;
END IF;
SET str1_tmp = SUBSTRING(str1_tmp, CURRENTINDEX + 1);
SET CURRENTINDEX = LOCATE(',', str1_tmp);
END WHILE;
# 处理最后一个无逗号的情况
IF LENGTH(str1_tmp) > 0 THEN
IF FIND_IN_SET(str1_tmp, str2_tmp) > 0 THEN
SET result = 1;
RETURN result;
END IF;
END IF;
# 交换str1和str2,重复相同的检查
SET str1_tmp = str2;
SET str2_tmp = str1;
SET CURRENTINDEX = LOCATE(',', str1_tmp);
WHILE CURRENTINDEX > 0 DO
SET CURRENTSTR = SUBSTRING(str1_tmp, 1, CURRENTINDEX - 1);
IF FIND_IN_SET(CURRENTSTR, str2_tmp) > 0 THEN
SET result = 1;
RETURN result;
END IF;
SET str1_tmp = SUBSTRING(str1_tmp, CURRENTINDEX + 1);
SET CURRENTINDEX = LOCATE(',', str1_tmp);
END WHILE;
# 处理最后一个无逗号的情况
IF LENGTH(str1_tmp) > 0 THEN
IF FIND_IN_SET(str1_tmp, str2_tmp) > 0 THEN
SET result = 1;
RETURN result;
END IF;
END IF;
END IF;
RETURN result;
END //
DELIMITER ;
思路来源于一位大佬,可以看看大佬原文
附大佬原文:https://blog.csdn.net/weixin_40141628/article/details/136556165