mysql查询在逗号分隔的字符串中查找值

vsmadaxz  于 2021-06-24  发布在  Mysql
关注(0)|答案(11)|浏览(270)

我有一个领域 COLORS (varchar(50)) 在我的table上 SHIRTS 包含逗号分隔字符串的,例如 1,2,5,12,15, . 代表可用颜色的每个数字。
运行查询时 select * from shirts where colors like '%1%' 为了得到所有的红衬衫(color=1),我还得到了颜色为灰色(=12)和橙色(=15)的衬衫。
我应该如何重写查询,以便is只选择颜色1而不是所有包含数字1的颜色?

cuxqih21

cuxqih211#

在本例中,查找集是您的朋友

select * from shirts where FIND_IN_SET(1,colors)
pexxcrt2

pexxcrt22#

1对于mysql:

SELECT FIND_IN_SET(5, columnname) AS result 
FROM table

2.对于postgres sql:

SELECT * 
FROM TABLENAME f
WHERE 'searchvalue' = ANY (string_to_array(COLUMNNAME, ','))

例子

select * 
from customer f
where '11' = ANY (string_to_array(customerids, ','))
yyyllmsg

yyyllmsg3#

您可以通过以下函数来实现这一点。
运行以下查询以创建函数。

DELIMITER ||
CREATE FUNCTION `TOTAL_OCCURANCE`(`commastring` TEXT, `findme`     VARCHAR(255)) RETURNS int(11)
NO SQL
-- SANI: First param is for comma separated string and 2nd for string to find.
return ROUND (   
    (
        LENGTH(commastring)
        - LENGTH( REPLACE ( commastring, findme, "") ) 
    ) / LENGTH(findme)        
);

像这样调用这个函数

msyql> select TOTAL_OCCURANCE('A,B,C,A,D,X,B,AB', 'A');
4sup72z8

4sup72z84#

经典的方法是在左右两边加逗号:

select * from shirts where CONCAT(',', colors, ',') like '%,1,%'

但在\集中查找\也有效:

select * from shirts where find_in_set('1',colors) <> 0
ao218c7q

ao218c7q5#

select * from shirts where find_in_set('1',colors) <> 0

对我有用

neekobn8

neekobn86#

并非所有答案都正确,请尝试以下方法:

select * from shirts where 1 IN (colors);
lg40wkob

lg40wkob7#

这肯定管用,我也试过了:

lwdba@localhost (DB test) :: DROP TABLE IF EXISTS shirts;
Query OK, 0 rows affected (0.08 sec)

lwdba@localhost (DB test) :: CREATE TABLE shirts
    -> (<BR>
    -> id INT NOT NULL AUTO_INCREMENT PRIMARY KEY,
    -> ticketnumber INT,
    -> colors VARCHAR(30)
    -> );<BR>
Query OK, 0 rows affected (0.19 sec)

lwdba@localhost (DB test) :: INSERT INTO shirts (ticketnumber,colors) VALUES
    -> (32423,'1,2,5,12,15'),
    -> (32424,'1,5,12,15,30'),
    -> (32425,'2,5,11,15,28'),
    -> (32426,'1,2,7,12,15'),
    -> (32427,'2,4,8,12,15');
Query OK, 5 rows affected (0.06 sec)
Records: 5  Duplicates: 0  Warnings: 0

lwdba@localhost (DB test) :: SELECT * FROM shirts WHERE LOCATE(CONCAT(',', 1 ,','),CONCAT(',',colors,',')) > 0;
+----+--------------+--------------+
| id | ticketnumber | colors       |
+----+--------------+--------------+
|  1 |        32423 | 1,2,5,12,15  |
|  2 |        32424 | 1,5,12,15,30 |
|  4 |        32426 | 1,2,7,12,15  |
+----+--------------+--------------+
3 rows in set (0.00 sec)

试试看!!!

du7egjpx

du7egjpx8#

如果颜色集或多或少是固定的,那么最有效也是最可读的方法就是在应用程序中使用字符串常量,然后使用mysql的 SET 键入 FIND_IN_SET('red',colors) 在您的查询中。当使用 SET 键入find\ in\ u set,mysql使用一个整数存储所有值,并使用二进制 "and" 检查是否存在值的操作,这比扫描逗号分隔的字符串更有效。
SET('red','blue','green') , 'red' 内部存储为 1 , 'blue' 内部存储为 2 以及 'green' 内部存储为 4 . 价值 'red,blue' 将存储为 3 ( 1|2 )以及 'red,green' 作为 5 ( 1|4 ).

rbpvctlc

rbpvctlc9#

如果您使用的是mysql,那么有一个方法regexp可以使用。。。
http://dev.mysql.com/doc/refman/5.1/en/regexp.html#operator_regexp
所以你可以使用:

SELECT * FROM `shirts` WHERE `colors` REGEXP '\b1\b'
wb1gzix0

wb1gzix010#

看看mysql的find\ in\ u set函数。

SELECT * 
    FROM shirts 
    WHERE FIND_IN_SET('1',colors) > 0
tpxzln5u

tpxzln5u11#

实际上,您应该修复数据库架构,以便有三个表:

shirt: shirt_id, shirt_name
color: color_id, color_name
shirtcolor: shirt_id, color_id

然后,如果要查找所有红色的衬衫,可以执行如下查询:

SELECT *
FROM shirt, color
WHERE color.color_name = 'red'
  AND shirt.shirt_id = shirtcolor.shirt_id
  AND color.color_id = shirtcolor.color_id

相关问题