如何检查VARCHAR列中是否存在值 - mysql [英] How to check to see if a value exist in a VARCHAR column - mysql
问题描述
我想知道在使用SELECT语句时是否可以检查VARCHAR列中的值是否退出。
例如：
VARCHAR Column（Items）=Item1，Item2，Item3，Item4
（项目用逗号（，）分隔。
我想知道SELECT语句中是否存在Item3。
我尝试了什么： < br $> b $ b
我还在研究这个话题而没有找到太多信息。
I would like to know if it is at all possible to check if a value exit in a VARCHAR column when using the SELECT statement.
e.g:
VARCHAR Column (Items) = "Item1, Item2, Item3, Item4"
(Items are separated by a comma (,).
I would like to know if "Item3" exist during a SELECT statement.
What I have tried:
I am still researching this topic and not finding much information.
推荐答案
是的......你可以......但是......
您可以使用LIKE：
Yes...you can...but...
You can use LIKE:
SELECT * FROM MyTable WHERE myColumn LIKE '%Item3%'
但..这不是个好主意。
问题是你的数据存储不当 - 你不应该在一列中存储列表。相反，您应该有一个单独的表，它单独存储列表项，外键返回到另一个表中的行。这样，每个列表项都很容易找到，你可以通过一个简单的JOIN来检索它们。
在VARCHAR列中存储逗号分隔列表是一个可以使用的PITA而且总是比它解决的麻烦更多。例如，如何从示例中删除Item2，并添加Item5代替？如果你正确存储它，它是微不足道的！
But...that's not a good idea.
The problem is that your data is badly stored - you shouldn't store "lists" in a single column. Instead, you should have a separate table which stores the list items separately, with a foreign key back to the row in your other table. That way, each list item is easily found, and you can retrieve them all with a simple JOIN.
Storing comma separated lists in VARCHAR columns is a PITA to work with and always gives more trouble than it solves. For example, how do you remove "Item2" from your example, and add "Item5" instead? If you store it properly, it's trivial!
当然，但有警告：
你可以这样做：
Sure, but with caveats:
You could do it like this:
DECLARE @text NVARCHAR(MAX);
SET @text = 'Item3'
SELECT t.*
FROM table t
WHERE t.Items like '%'+@text+'%'
那是'好'但远非伟大。它将识别Item3和Item35。
您可以尝试在多部分选择中使用逗号：
That is 'ok' but far from great. It will identify Item3 AND Item35.
You could try using the commas in a multipart select:
DECLARE @text NVARCHAR(MAX);
SET @text = 'Item3'
SELECT t.*
FROM table t
WHERE t.Items like @text+',%'
OR t.Items like ', %'+@text+',%'
OR t.Items like ', %'+@text
OR t.Items = @text
这使用逗号来检查文本是否是项目，在集合的开头或结尾，或者它是唯一的项目。
然而，这里有警告，文本库搜索可能非常慢，特别是如果它们很大或者你有很多项目要找。 可能更好地将csv列转换为具有表格函数的表格，例如链接在这里的表格：
将CSV分隔的字符串转换为SQL SERVER中的表列 [ ^ ]
这也可能很慢。第一个喜欢查询和这个查询的组合可能更有效。
我的长期建议是不存储csv在表格列中。它应该足够简单，以便在子表中存储csv值。这更有效，特别是如果Items不是任何一条记录的唯一。但是，我知道有很多原因导致您无法在此阶段重新设计数据库。
编辑：你应该在这个问题上阅读OriginalGriff的观点
我希望有所帮助^ _ ^
Andy
This uses the comma to check if the text is an item, at the start or end of a collection or if it is the only item.
However, and here comes the caveat, text bases searches can be very slow, especially if they are large or you have many items to find. It may be better to convert the csv columns into tables with a tabular function like the one linked here:
Convert a CSV delimited string to table column in SQL SERVER[^]
This could also be slow too. A combination of the first "like" query and this one may be more efficient.
My long term advice is to not store csv in a table column. It should be simple enough to store csv values in a child table. This is far more efficient especially if the Items are not unique to any one record. However, I am aware of many reasons why you cannot redesign the db at this stage.
EDIT: You should read OriginalGriff's points on this issue
I hope that helps ^_^
Andy
这篇关于如何检查VARCHAR列中是否存在值 - mysql的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！
