MySQL find_in_set 具有多个搜索字符串
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/5015403/
Warning: these are provided under cc-by-sa 4.0 license. You are free to use/share it, But you must attribute it to the original authors (not me):
StackOverFlow
MySQL find_in_set with multiple search string
提问by Mukesh Chapagain
I find that find_in_setonly search by a single string :-
我发现find_in_set只搜索单个字符串：-
find_in_set('a', 'a,b,c,d')
In the above example, 'a' is the only string used for search.
在上面的示例中，'a' 是唯一用于搜索的字符串。
Is there any way to use find_in_set kind of functionality and search by multiple strings, like :-
有什么方法可以使用 find_in_set 类型的功能并通过多个字符串进行搜索，例如：-
find_in_set('a,b,c', 'a,b,c,d')
In the above example, I want to search by three strings 'a,b,c'.
在上面的示例中，我想按三个字符串 'a,b,c' 进行搜索。
One way I see is using OR
我看到的一种方法是使用OR
find_in_set('a', 'a,b,c,d') OR find_in_set('b', 'a,b,c,d') OR find_in_set('b', 'a,b,c,d')
Is there any other way than this?
除了这个还有别的办法吗？
回答by Pavel Perminov
there is no native function to do it, but you can achieve your aim using following trick
没有本地功能可以做到这一点，但您可以使用以下技巧来实现您的目标
WHERE CONCAT(",", `setcolumn`, ",") REGEXP ",(val1|val2|val3),"
回答by Dmitry Avtonomov
The MySQL function find_in_set()can search only for one string in a set of strings.
MySQL 函数find_in_set()只能搜索一组字符串中的一个字符串。
The first argument is a string, so there is no way to make it parse your comma separated string into strings (you can't use commas in SET elements at all!). The second argument is a SET, which in turn is represented by a comma separated string hence your wish to find_in_set('a,b,c', 'a,b,c,d')which works fine, but it surely can't find a string 'a,b,c'in any SET by definition - it contains commas.
第一个参数是一个字符串，因此无法将逗号分隔的字符串解析为字符串（您根本不能在 SET 元素中使用逗号！）。第二个参数是一个 SET，它又由逗号分隔的字符串表示，因此您希望find_in_set('a,b,c', 'a,b,c,d')它可以正常工作，但'a,b,c'根据定义它肯定无法在任何 SET 中找到字符串- 它包含逗号。
回答by user3740702
You can also use this custom function
您也可以使用此自定义功能
CREATE FUNCTION SPLIT_STR(
x VARCHAR(255),
delim VARCHAR(12),
pos INT
)
RETURNS VARCHAR(255)
RETURN REPLACE(SUBSTRING(SUBSTRING_INDEX(x, delim, pos),
LENGTH(SUBSTRING_INDEX(x, delim, pos -1)) + 1),
delim, '');
DELIMITER $$
CREATE FUNCTION `FIND_SET_EQUALS`(`s1` VARCHAR(200), `s2` VARCHAR(200))
RETURNS TINYINT(1)
LANGUAGE SQL
BEGIN
DECLARE a INT Default 0 ;
DECLARE isEquals TINYINT(1) Default 0 ;
DECLARE str VARCHAR(255);
IF s1 IS NOT NULL AND s2 IS NOT NULL THEN
simple_loop: LOOP
SET a=a+1;
SET str= SPLIT_STR(s2,",",a);
IF str='' THEN
LEAVE simple_loop;
END IF;
#Do check is in set
IF FIND_IN_SET(str, s1)=0 THEN
SET isEquals=0;
LEAVE simple_loop;
END IF;
SET isEquals=1;
END LOOP simple_loop;
END IF;
RETURN isEquals;
END;
$$
DELIMITER ;
SELECT FIND_SET_EQUALS('a,c,b', 'a,b,c')- 1
SELECT FIND_SET_EQUALS('a,c', 'a,b,c')- 0
SELECT FIND_SET_EQUALS(null, 'a,b,c')- 0
回答by Jon Gabrielson
You can also use the like command for instance:
例如，您还可以使用 like 命令：
where setcolumn like '%a,b%'
or
或者
where 'a,b,c,d' like '%b,c%'
which might work in some situations.
这在某些情况下可能有效。
回答by sandeep autade
you can use in to find match values from two values
您可以使用 in 从两个值中查找匹配值
SELECT * FROM table WHERE myvals in (a,b,c,d)
