如何搜索包含子字符串的行? [英] How to search for rows containing a substring?

问题描述

如果每次用户提交表单时，我都在ODBC数据库中存储HTML TEXTAREA，则SELECT语句是什么，以检索1)包含给定子字符串的所有行2)不包含(和)所有行搜索区分大小写吗?)

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

如果LIKE "%SUBSTRING%"变慢，最好将所有内容&用PHP整理出来吗?

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

推荐答案

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

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.

这取决于字段类型-通常不会将textarea保存为VARCHAR，而是保存为TEXT字段，因此可以使用

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 AGAINST operator.

要获取不匹配的列，只需将NOT放在诸如WHERE textcolumn NOT LIKE "%SUBSTRING%"的前面.

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

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

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.

我说，如果列字段具有索引，则执行WHERE field LIKE "%value%"的操作要比WHERE field LIKE "value%"慢，但这仍然比获取所有值和对应用程序进行过滤要快得多.两种情况:

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:

1/如果您执行SELECT field FROM table WHERE field LIKE "%value%"，MySQL将扫描整个表，并仅发送包含值"的字段.

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".

2/如果执行SELECT field FROM table，然后让您的应用程序(在您的情况下为PHP)仅过滤其中带有值"的行，则MySQL还将扫描整个表，但会将所有字段发送给PHP，然后必须做其他工作.这比案例#1慢得多.

2/ If you do SELECT field FROM table and 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.

解决方案:请务必使用WHERE子句，并使用EXPLAIN来查看性能.

Solution: Please do use the WHERE clause, and use EXPLAIN to see the performance.

这篇关于如何搜索包含子字符串的行?的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！