我有一个像这样的表格:
UID(int) NUMBERS(blob)
----------------------
1 1,13,15,20
2 3,10,15,20
3 3,15
我想测试数字3和15是否在名为NUMBERS的Blob中。并且不能使用LIKE %%。
只有ID为2和3的行才能被选中...
这个也可以:
SELECT * FROM table WHERE 3 IN (NUMBERS) AND 15 IN (NUMBERS)
使用IN将查找逗号分隔的字符串,例如这两个:
WHERE banana IN ('apple', 'banana', 'coconut')
WHERE 3 IN (2,3,6,8,90)
foo IN ('1,2,3') ≡ foo IN (CAST('1,2,3' AS INT)) ≡ foo IN (1)
。我猜测使用 find_in_set
更加合理。 - drdaeman虽然不是最优美的解决方案,但它起作用:
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
请注意这是字符串比较,因此您可能还需要将输入参数转换为char类型。
您可以尝试以下方式:
SELECT * FROM table_name WHERE FIND_IN_SET('3', NUMBERS) AND FIND_IN_SET('15', NUMBERS)
这是一个与MySQL相关的扩展功能,旨在消除原生FIND_IN_SET()
的限制。新的扩展版本FIND_IN_SET_X()
提供了将一个列表与另一个列表进行比较的功能。
例如:
Also check if this is helpful to anyone
mysql> SELECT FIND_IN_SET_X('x,c','a,b,c,d'); -> 3
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 ;
请尝试以下方法:
SELECT UID
FROM TABLE
WHERE
concat(",",NUMBERS,",") like "%,1,%"
OR
concat(",",NUMBERS,",") like "%,15,%";
尝试这个查询:
SELECT UID FROM table WHERE NUMBERS REGEXP "[[:<:]](3|10)[[:>:]]"
[[:<:]],[[:>:]]:
这些标记表示单词边界,因此它们分别匹配单词的开头和结尾。
find_in_set_x
在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