MySQL 如何搜索包含子字符串的行？

How to search for rows containing a substring?

提问by Mawg says reinstate Monica

If I store an HTML TEXTAREA in my ODBC database each time the user submits a form, what's the SELECTstatement to retrieve 1) all rows which contain a given sub-string 2) all rows which don't (and is the search case sensitive?)

如果每次用户提交表单时我都在我的 ODBC 数据库中存储一个 HTML TEXTAREA，那么SELECT检索 1) 包含给定子字符串的所有行 2) 所有不包含的行（并且搜索是否区分大小写）的语句是什么？ )

---

---

Edit: if LIKE "%SUBSTRING%"is going to be slow, would it be better to get everything & sort it out in PHP?

编辑：如果LIKE "%SUBSTRING%"速度会很慢，最好在 PHP 中获取所有内容并对其进行整理？

回答by Konerak

Well, you can always try WHERE textcolumn LIKE "%SUBSTRING%"- but this is guaranteed to be pretty slow, as your query can't do an index match because you are looking for characters on the left side.

好吧，您可以随时尝试WHERE textcolumn LIKE "%SUBSTRING%"- 但这肯定会很慢，因为您的查询无法进行索引匹配，因为您正在寻找左侧的字符。

It depends on the field type - a textarea usually won't be saved as VARCHAR, but rather as (a kind of) TEXT field, so you can use the MATCH AGAINSToperator.

这取决于字段类型 - textarea 通常不会保存为 VARCHAR，而是保存为（一种）TEXT 字段，因此您可以使用MATCH AGAINST运算符。

To get the columns that don't match, simply put a NOT in front of the like: WHERE textcolumn NOT LIKE "%SUBSTRING%".

要获取不匹配的列，只需在 like: 前面放一个 NOT WHERE textcolumn NOT LIKE "%SUBSTRING%"。

Whether the search is case-sensitive or not depends on how you stock the data, especially what COLLATION you use. By default, the search will be case-insensitive.

搜索是否区分大小写取决于您如何存储数据，尤其是您使用的 COLLATION。默认情况下，搜索将不区分大小写。

Updated answer to reflect question update:

更新答案以反映问题更新：

I say that doing a WHERE field LIKE "%value%"is slower than WHERE field LIKE "value%"if the column field has an index, but this is still considerably faster than getting all values and having your application filter. Both scenario's:

我说执行 aWHERE field LIKE "%value%"比WHERE field LIKE "value%"如果列字段有索引要慢，但这仍然比获取所有值和使用应用程序过滤器快得多。两种情况的：

1/ If you do SELECT field FROM table WHERE field LIKE "%value%", MySQL will scan the entire table, and only send the fields containing "value".

1/ 如果这样做SELECT field FROM table WHERE field LIKE "%value%"，MySQL 将扫描整个表，并且只发送包含“值”的字段。

2/ If you do SELECT field FROM tableand then have your application (in your case PHP) filter only the rows with "value" in it, MySQL will also scan the entire table, but send all the fields to PHP, which then has to do additional work. This is much slower than case #1.

2/如果你这样做SELECT field FROM table然后让你的应用程序（在你的例子中是PHP）只过滤其中包含“值”的行，MySQL也会扫描整个表，但将所有字段发送到PHP，然后它必须做额外的工作. 这比案例#1 慢得多。

Solution: Please do use the WHEREclause, and use EXPLAINto see the performance.

解决方法：请务必使用该WHERE子句，并使用EXPLAIN以查看性能。

回答by Jaydee

Info on MySQL's full text search. This is restricted to MyISAM tables, so may not be suitable if you wantto use a different table type.

有关 MySQL 全文搜索的信息。这仅限于 MyISAM 表，因此如果您想使用不同的表类型，则可能不适合。

http://dev.mysql.com/doc/refman/5.0/en/fulltext-search.html

http://dev.mysql.com/doc/refman/5.0/en/fulltext-search.html

Even if WHERE textcolumn LIKE "%SUBSTRING%"is going to be slow, I think it is probably better to let the Database handle it rather than have PHP handle it. If it is possible to restrict searches by some other criteria (date range, user, etc) then you may find the substring search is OK (ish).

即使WHERE textcolumn LIKE "%SUBSTRING%"会很慢，我认为让数据库处理它而不是让 PHP 处理它可能更好。如果可以通过其他一些条件（日期范围、用户等）来限制搜索，那么您可能会发现子字符串搜索是可以的（ish）。

If you are searching for whole words, you could pull out all the individual words into a separate table and use that to restrict the substring search. (So when searching for "my search string" you look for the the longest word "search" only do the substring search on records containing the word "search")

如果您正在搜索整个单词，您可以将所有单个单词提取到一个单独的表中，并使用它来限制子字符串搜索。（因此，在搜索“我的搜索字符串”时，您会查找最长的单词“搜索”，仅对包含单词“搜索”的记录进行子字符串搜索）