需求:A表中column1字段存储字符串,字符串为多个元素根据逗号拼接而成,需搜索出包含某元素的数据
建立存储过程:
CREATE DEFINER=`roots`@`%` FUNCTION `NewProc`(str1 text,str2 text) RETURNS text CHARSET utf8mb4 BEGIN #传入两个逗号分割的字符串,判断第二个字符串是否包含第一个字符串split之后的单个 DECLARE CURRENTINDEX INT;#当前下标 DECLARE CURRENTSTR text; DECLARE result int; set result = 0; set CURRENTINDEX = 0; set CURRENTSTR = ‘‘; IF str1 IS NOT NULL AND str1 != ‘‘ THEN SET CURRENTINDEX = LOCATE(‘,‘,str1); WHILE CURRENTINDEX > 0 DO SET CURRENTSTR = substring(str1,1,CURRENTINDEX-1); if FIND_IN_SET(CURRENTSTR,str2) THEN set result = 1; end if; SET str1 = substring(str1,CURRENTINDEX+1); SET CURRENTINDEX = LOCATE(‘,‘,str1); END WHILE; #只传一个 和 最后无逗号的情况 IF LENGTH(str1) > 0 THEN if FIND_IN_SET(str1,str2) THEN set result = 1; end if; END IF; END IF; RETURN result; END
使用:select * from table_A where NewProc(‘param‘, column1);