問題描述
我的表 SHIRTS 中有一個(gè)字段 COLORS (varchar(50))，它包含一個(gè)逗號(hào)分隔的字符串，例如 1,2,5，12,15，.每個(gè)數(shù)字代表可用的顏色.
I have a field COLORS (varchar(50)) in a my table SHIRTS that contains a comma delimited string such as 1,2,5,12,15,. Each number representing the available colors.
當(dāng)運(yùn)行查詢 select * from shirts where colours like '%1%' 以獲取所有紅色襯衫(顏色 = 1)時(shí)，我還會(huì)得到顏色為灰色( = 12) 和橙色 (=15).
When running the query select * from shirts where colors like '%1%' to get all the red shirts (color=1), I also get the shirts whose color is grey (=12) and orange (=15).
我應(yīng)該如何重寫查詢，以便僅選擇顏色 1 而不是所有包含數(shù)字 1 的顏色?
How should I rewrite the query so that is selects ONLY the color 1 and not all colors containing the number 1?
推薦答案
經(jīng)典的方法是在左右添加逗號(hào):
The classic way would be to add commas to the left and right:
select * from shirts where CONCAT(',', colors, ',') like '%,1,%'
但是find_in_set也有效:
select * from shirts where find_in_set('1',colors) <> 0
這篇關(guān)于MySQL 查詢?cè)诙禾?hào)分隔的字符串中查找值的文章就介紹到這了，希望我們推薦的答案對(duì)大家有所幫助，也希望大家多多支持html5模板網(wǎng)！