COLORS (varchar(50))
中有一个字段SHIRTS
,其中包含逗号分隔的字符串,例如1,2,5,12,15,
。每个数字代表可用的颜色。运行查询
select * from shirts where colors like '%1%'
以获取所有红色衬衫(颜色= 1)时,我还获得了颜色为灰色(= 12)和橙色(= 15)的衬衫。 />我该如何重写查询,以便仅选择颜色1而不是所有包含数字1的颜色?#1 楼
经典的方法是在左侧和右侧添加逗号:select * from shirts where CONCAT(',', colors, ',') like '%,1,%'
,但是find_in_set也有效:
select * from shirts where find_in_set('1',colors) <> 0
评论
我尝试过find_in_set,但是无论输入的颜色值如何,它都返回相同的结果...有什么建议吗?
–bikey77
2011-2-17在19:19
@ bikey77:也许是问题所在,文档说:如果第一个参数包含逗号(“,”)字符,则此函数无法正常工作。
–安道尔
2011-2-17在19:27
我的错,由于相同的虚拟值,这是一个逻辑错误。它工作正常。谢谢!
–bikey77
2011-2-17在19:37
@Andomar在我找到你的答案之前,我并没有在IN上苦苦挣扎,但是你的工作就像一个魅力……非常感谢。
– PHP导师
2012年10月19日20:35
由于Find_in_set不使用索引,因此对性能有影响
–甘兰·沙希德(Kamran Shahid)
2015年5月29日在9:15
#2 楼
在这种情况下,FIND_IN_SET是您的朋友select * from shirts where FIND_IN_SET(1,colors)
评论
find_in_set对于大型表来说太慢了
– Jeff_Alieffson
2013年12月20日18:00
#3 楼
看一下MySQL的FIND_IN_SET函数。SELECT *
FROM shirts
WHERE FIND_IN_SET('1',colors) > 0
评论
当心:在集合中查找不使用表上的索引。
– edigu
17-2-25在13:43
#4 楼
这肯定可以工作,我实际上已经尝试过: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)
试一试!!!
评论
嘿@rolandomysqldba,我测试了您的查询,它可以正常运行,但是我需要对其进行一些更改。假设我要在列中获取所有颜色值为1,2的衬衫。
– Ahsan Saeed
1月24日4:32
#5 楼
如果颜色的设置或多或少是固定的,那么最有效,也最易读的方法是在应用中使用字符串常量,然后在查询中使用MySQL的SET
类型和FIND_IN_SET('red',colors)
。当使用带有FIND_IN_SET的SET
类型时,MySQL使用一个整数来存储所有值,并使用二进制"and"
操作来检查值的存在,这比扫描逗号分隔的字符串更有效。在
SET('red','blue','green')
中,'red'
将在内部存储为1
,'blue'
将在内部存储为2
,而'green'
将在内部存储为4
。值'red,blue'
将被存储为3
(1|2
),而'red,green'
将被存储为5
(1|4
)。#6 楼
如果您使用的是MySQL,则可以使用REGEXP方法...http://dev.mysql.com/doc/refman/5.1/en/regexp.html#operator_regexp
那么您将使用:
SELECT * FROM `shirts` WHERE `colors` REGEXP '\b1\b'
评论
尽管我很确定这是我的错,但无法弄清楚这一点。谢谢密尔队友。
–bikey77
2011-2-17在19:36
#7 楼
实际上,您应该修复数据库架构,以便拥有三个表: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
评论
@Blindy:仅当您假设OP对数据库模式具有编辑权限时,这才是正确的;有时间重新设计数据库,迁移数据并重构所有客户端;并且此查询的复杂度降低超过了其余应用程序的复杂度增长。
–安道尔
2011-2-17在19:30
@Andomar,然后当他遇到行检索的大小限制并且他的“记录”将被剪裁时,真正的乐趣就会开始!
–盲目的
2011-2-17在21:11
@Blindy:您错过了重点;我并不是说他有最好的解决方案,只是不是每个人都可以自由地根据自己的喜好重新设计环境
–安道尔
2011-2-17在21:16
我同意@Andomar
–亚当B
13-10-9在23:29
#8 楼
select * from shirts where find_in_set('1',colors) <> 0
为我工作
#9 楼
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, ','))
#10 楼
您可以通过以下函数来实现。运行以下查询以创建函数。
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');
#11 楼
所有答案都不完全正确,请尝试以下操作:select * from shirts where 1 IN (colors);
评论
我想您可以通过正则表达式来做到这一点,但是更好的解决方案是将衬衫颜色分为一个单独的表(颜色),并使用一个连接表(shirt_colors),使用颜色/衬衫的ID来链接它们。 >我不敢相信有6个答案,没有一个提到MySQL的SET数据类型。.
检查一下:stackoverflow.com/questions/12559876/…