如何在A包含("a"或"b")的地方编写MySQL查询 [英] How to write MySQL query where A contains ( "a" or "b" )

问题描述

我必须使用此格式where A operand B. A是领域；我希望B是文本1"或文本2"，因此，如果A具有文本1文本文本"或文本2"之类的数据，则查询将具有结果.

I must use this format where A operand B. A is the field; I want B to be either "text 1" or "text 2", so if A has data like "text 1 texttext" or "texttext 2" , the query will have result.

但是我该怎么写呢? MySQL是否支持类似的东西

But how do I write this? Does MySQL support something like

where A contains ('text 1' OR 'text 2')? `

推荐答案

两个选项:

1. 使用LIKE关键字以及字符串中的百分号

1. Use the LIKE keyword, along with percent signs in the string

select * from table where field like '%a%' or field like '%b%'.

(注意:如果搜索字符串中包含百分号，则需要将其转义)

(note: If your search string contains percent signs, you'll need to escape them)

如果您要查找比示例中指定的字符串更复杂的组合，则可以使用正则表达式(regex):

If you're looking for more a complex combination of strings than you've specified in your example, you could regular expressions (regex):

有关如何使用它们的更多信息，请参见MySQL手册: http: //dev.mysql.com/doc/refman/5.1/zh-CN/regexp.html

See the MySQL manual for more on how to use them: http://dev.mysql.com/doc/refman/5.1/en/regexp.html

其中，使用LIKE是最常见的解决方案-它是标准SQL，并且是常用的.正则表达式不常用，但功能更强大.

Of these, using LIKE is the most usual solution -- it's standard SQL, and in common use. Regex is less commonly used but much more powerful.

请注意，无论使用哪种选项，都需要了解可能的性能影响.搜索像这样的子字符串将意味着查询将不得不扫描整个表.如果您有一个大表，这可能会使查询非常慢，并且没有大量索引可以帮助您.

Note that whichever option you go with, you need to be aware of possible performance implications. Searching for sub-strings like this will mean that the query will have to scan the entire table. If you have a large table, this could make for a very slow query, and no amount of indexing is going to help.

如果这对您来说是个问题，并且您需要一遍又一遍地搜索相同的内容，则可能需要执行类似的操作，例如向表中添加一个标志字段，以指定字符串字段包含相关子字符串.如果在插入更新记录时保持此标志字段为最新状态，则只需在要搜索时查询该标志即可.可以将其编入索引，并使您的查询快得多.是否值得您全力以赴取决于您，这取决于使用LIKE的性能有多糟糕.

If this is an issue for you, and you'r going to need to search for the same things over and over, you may prefer to do something like adding a flag field to the table which specifies that the string field contains the relevant sub-strings. If you keep this flag field up-to-date when you insert of update a record, you could simply query the flag when you want to search. This can be indexed, and would make your query much much quicker. Whether it's worth the effort to do that is up to you, it'll depend on how bad the performance is using LIKE.

这篇关于如何在A包含("a"或"b")的地方编写MySQL查询的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！