一、函数实现原理

1.调用 LOCATE 函数 获得逗号出现的位置。

2.调用 SUBSTRING_INDEX 函数截取逗号前的字符串。

3.调用 SUBSTR 函数截取逗号后的字符串。

4.调用 REPLACE 和 LENGTH 函数的组合 统计逗号出现次数。

二、创建交集函数

-- ===== 删除函数-- DROP FUNCTION IF EXISTS `FIND_IN_SET_INTERSECT`;-- ===== 创建函数CREATE FUNCTION `FIND_IN_SET_INTERSECT`(paramA text, paramB text) RETURNS INTBEGIN-- =====================================DECLARE currIdx INT DEFAULT 0; DECLARE tmpStr text;DECLARE currStr text; -- ===== 统计参数A 逗号出现的次数DECLARE delimiterA INT DEFAULT 0; -- ===== 统计参数B 逗号出现的次数DECLARE delimiterB INT DEFAULT 0; -- =====================================SET currIdx = LOCATE(',', paramA);-- ===== 如果 paramA 不包含 ',' IF currIdx = 0 THEN IF FIND_IN_SET(paramA, paramB) > 0 THEN RETURN 1;ELSERETURN 0;END IF;END IF;-- =====================================SET currIdx = LOCATE(',', paramB);-- ===== 如果 paramB 不包含 ','IF currIdx = 0 THEN IF FIND_IN_SET(paramB, paramA) > 0 THEN RETURN 1;ELSERETURN 0;END IF;END IF;-- =====================================-- ===== 统计参数A、参数B 逗号数-- SET delimiterA = (LENGTH(paramA) - LENGTH(REPLACE (paramA, ',' , ''))) / LENGTH(',');-- SET delimiterB = (LENGTH(paramB) - LENGTH(REPLACE (paramB, ',' , ''))) / LENGTH(',');SET delimiterA = LENGTH(paramA) - LENGTH(REPLACE (paramA, ',' , ''));SET delimiterB = LENGTH(paramB) - LENGTH(REPLACE (paramB, ',' , ''));-- ===== 交换参数值 减少循环次数IF delimiterA > delimiterB THEN SET tmpStr = paramA;SET paramA = paramB;SET paramB = tmpStr;END IF;SET currStr = paramA;SET currIdx = LOCATE(',', currStr);WHILE currIdx > 0 DOSET tmpStr = SUBSTRING_INDEX(currStr, ',', 1);SET currStr = SUBSTR(currStr, currIdx + 1);IF FIND_IN_SET(tmpStr, paramB) > 0 THEN RETURN 1;END IF;SET currIdx = LOCATE(',',currStr);END WHILE;IF FIND_IN_SET(currStr, paramB) > 0 THENRETURN 1;END IF;RETURN 0;END;

三、函数调用

SELECT FIND_IN_SET_INTERSECT('1', '1,2,3') AS `HAS_INTERSECT`; -- > 1SELECT FIND_IN_SET_INTERSECT('1,2,3', '0,13') AS `HAS_INTERSECT`; -- > 0SELECT FIND_IN_SET_INTERSECT('1,2,3', '2') AS `HAS_INTERSECT`; -- > 1SELECT FIND_IN_SET_INTERSECT('1,2,3,4,5,6,7,8', '8,9') AS `HAS_INTERSECT`; -- > 1