mysql 检查数字是否在逗号分隔的列表中
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/2674011/
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 check if numbers are in a comma separated list
提问by Tillebeck
I have a table like this:
我有一张这样的表：
UID(int) NUMBERS(blob)
----------------------
1 1,13,15,20
2 3,10,15,20
3 3,15
And I would like to test if 3 and 15 are in the blob called NUMBERS. And can see the LIKE %% cannot be used
我想测试 3 和 15 是否在名为 NUMBERS 的 blob 中。并且可以看到 LIKE %% 不能用
Only row with ID 2 and three scoulb be selected...
仅选择 ID 为 2 和三个 scoulb 的行...
回答by Tillebeck
This one also works:
这个也有效：
SELECT * FROM table WHERE 3 IN (NUMBERS) AND 15 IN (NUMBERS)
using the IN will look into a comma separated string eg. these two
使用 IN 将查看逗号分隔的字符串，例如。这两个
WHERE banana IN ('apple', 'banana', 'coconut')
WHERE 3 IN (2,3,6,8,90)
回答by Bj?rn
Not the most pretty solution, but it works:
不是最漂亮的解决方案，但它有效：
select
UID
from
YOUR_TABLE
where
find_in_set('3', cast(NUMBERS as char)) > 0
and
find_in_set('15', cast(NUMBERS as char)) > 0
Note that it's string comparison, so you may need to cast your input parameters to char as well.
请注意，它是字符串比较，因此您可能还需要将输入参数转换为 char。
回答by Md. Maruf Hossain
You Can Try As Like Following :
您可以尝试如下：
SELECT * FROM table_name WHERE FIND_IN_SET('3', NUMBERS) AND FIND_IN_SET('15', NUMBERS)
回答by Nilesh
Also check if this is helpful to anyone
还要检查这是否对任何人有帮助
An Extended function to eliminate the limitation of native FIND_IN_SET()in MySQL, this new extended version FIND_IN_SET_X()provides feature to compare one list with another list.
一个扩展功能，以消除FIND_IN_SET()MySQL 中原生的限制，这个新的扩展版本FIND_IN_SET_X()提供了将一个列表与另一个列表进行比较的功能。
i.e.
IE
mysql> SELECT FIND_IN_SET_X('x,c','a,b,c,d'); -> 3
回答by Rodolfo Souza
The function complete for you
为您完成的功能
DELIMITER $$
CREATE FUNCTION `FIND_IN_SET_X`(inputList TEXT,targetList TEXT) RETURNS INT(11)
DETERMINISTIC
BEGIN
DECLARE limitCount INT DEFAULT 0 ;
DECLARE counter INT DEFAULT 0 ;
DECLARE res INT DEFAULT 0 ;
DECLARE temp TEXT ;
SET limitCount = 1 + LENGTH(inputList) - LENGTH(REPLACE(inputList, ',', '')) ;
simple_loop :
LOOP
SET counter = counter + 1 ;
SET temp = SUBSTRING_INDEX(SUBSTRING_INDEX(inputList, ',', counter),',',- 1) ;
SET res = FIND_IN_SET(temp, targetList) ;
IF res > 0
THEN LEAVE simple_loop ;
END IF ;
IF counter = limitCount
THEN LEAVE simple_loop ;
END IF ;
END LOOP simple_loop ;
RETURN res ;
END$$
DELIMITER ;
回答by Pradeep P
Try This query :
试试这个查询：
SELECT UID FROM table WHERE NUMBERS REGEXP "[[:<:]](3|10)[[:>:]]"
回答by user5127939
find_in_set_x
find_in_set_x
create a new function in mysql and paste in the following (not my work by the way)
在 mysql 中创建一个新函数并粘贴以下内容（顺便说一下，不是我的工作）
BEGIN
DECLARE limitCount INT DEFAULT 0;
DECLARE counter INT DEFAULT 0;
DECLARE res INT DEFAULT 0;
DECLARE temp TEXT;
SET limitCount = 1 + LENGTH(inputList) - LENGTH(REPLACE(inputList, ',',''));
simple_loop:LOOP
SET counter = counter + 1;
SET temp = SUBSTRING_INDEX(SUBSTRING_INDEX(inputList,',',counter),',',-1);
SET res = FIND_IN_SET(temp,targetList);
IF res > 0 THEN LEAVE simple_loop; END IF;
IF counter = limitCount THEN LEAVE simple_loop; END IF;
END LOOP simple_loop;
RETURN res;
END
