用CSV搜索用MySQL搜索
问题描述:
我知道这可以使用FIND_IN_SET完成。但我需要一些与众不同的东西。用CSV搜索用MySQL搜索
我的一个字段包含由逗号分隔的6位数字。其他字符串最多有5个值,用逗号分隔。
所以我想获得匹配值的计数。
像:
Database value: 1,2,3,4,5,6
Search Value: 1,2,3,4
这会给4.
Database value: 1,2,3,4,5,6
Search Value: 1,2,3,4,6
这会给5.
Database value: 1,2,3,4,5,6
Search Value: 1,4
这会给2.
我使用有InnoDB表。
答
如果你不使用InnoDB表,我会建议在一些描述中使用full text search。但是你正在使用InnoDB!
这就是说我会走下user defined function的路线,特别是存储的功能。幸运的是,我已经从previous post上得到了一个。我调整了它,以便您可以通过任何分隔符。既然它是一个函数,你可以在查询中使用它,就像其他任何MySQL函数一样。
希望它有帮助。
DROP FUNCTION IF EXISTS `CompareStrings`;
DELIMITER $$
CREATE FUNCTION `CompareStrings`(str1 VARCHAR(255),str2 VARCHAR(255), delimiter VARCHAR(1)) RETURNS double
READS SQL DATA
BEGIN
DECLARE cur_position INT DEFAULT 1 ;
DECLARE remainder TEXT;
DECLARE cur_string VARCHAR(255);
DECLARE delimiter_length TINYINT UNSIGNED;
DECLARE total INT;
DECLARE result DOUBLE DEFAULT 0;
DECLARE string2 VARCHAR(255);
SET remainder = str1;
SET string2 = concat(delimiter,trim(str2),delimiter);
SET delimiter_length = CHAR_LENGTH(delimiter);
SET cur_position = 1;
WHILE CHAR_LENGTH(remainder) > 0 AND cur_position > 0 DO
SET cur_position = INSTR(remainder, delimiter);
IF cur_position = 0 THEN
SET cur_string = remainder;
ELSE
SET cur_string = concat(delimiter,LEFT(remainder, cur_position - 1),delimiter);
END IF;
IF TRIM(cur_string) != '' THEN
set result = result + (select instr(string2,cur_string) > 0);
END IF;
SET remainder = SUBSTRING(remainder, cur_position + delimiter_length);
END WHILE;
RETURN result;
END$$
DELIMITER ;
然后,您可以运行类似:
select CompareStrings('1,2,3,4,5,6','1,2,3,4',',')
,你会得到4:
select CompareStrings('1,2,3,4,5,6','1,2,3,4,6',',')
,你会得到5
答
我已创建1功能和1是从MySQL网站找到的:
该函数给出了定义分隔符的字符串中给定位置的值。
DELIMITER $$
CREATE FUNCTION `SplitString`(x varchar(255), delim varchar(12), pos int) RETURNS varchar(255) CHARSET latin1
return replace(substring(substring_index(x, delim, pos), length(substring_index(x, delim, pos - 1)) + 1), delim, '')
该函数使用上述函数并服务于我的目的。
DELIMITER $$
CREATE FUNCTION `get_matched`(my_num varchar(100), to_match_num VARCHAR(100)) RETURNS int(11)
BEGIN
DECLARE temp, total INT;
SET total=0;
SET temp = IF(FIND_IN_SET(SplitString(my_num,',',1), to_match_num)>0,1,0);
SET total = total+ temp;
SET temp = IF(FIND_IN_SET(SplitString(my_num,',',2), to_match_num)>0,1,0);
SET total = total+ temp;
SET temp = IF(FIND_IN_SET(SplitString(my_num,',',3), to_match_num)>0,1,0);
SET total = total+ temp;
SET temp = IF(FIND_IN_SET(SplitString(my_num,',',4), to_match_num)>0,1,0);
SET total = total+ temp;
SET temp = IF(FIND_IN_SET(SplitString(my_num,',',5), to_match_num)>0,1,0);
SET total = total+ temp;
SET temp = IF(FIND_IN_SET(SplitString(my_num,',',6), to_match_num)>0,1,0);
SET total = total+temp;
RETURN total;
END