如何搜索包含子字符串的行？
如果每次用户提交表单时我都在 ODBC 数据库中存储 HTML TEXTAREA，则用于检索 1) 包含给定子字符串的所有行 2) 不包含给定子字符串的所有行的 SELECT 语句是什么（搜索是否区分大小写？）
编辑：如果 LIKE "%SUBSTRING%" 会很慢，那么最好获取所有内容并获取所有内容。用 PHP 来解决吗？
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?)
Edit: if LIKE "%SUBSTRING%" is going to be slow, would it be better to get everything & sort it out in PHP?
如果你对这篇内容有疑问，欢迎到本站社区发帖提问 参与讨论，获取更多帮助，或者扫码二维码加入 Web 技术交流群。
绑定邮箱获取回复消息
由于您还没有绑定你的真实邮箱，如果其他用户或者作者回复了您的评论，将不能在第一时间通知您！
发布评论
评论（3）
好吧，你总是可以尝试
WHERE textcolumn LIKE "%SUBSTRING%"- 但这肯定会很慢，因为你的查询无法进行索引匹配，因为你正在寻找左侧的字符边。这取决于字段类型 - 文本区域通常不会保存为 VARCHAR，而是保存为（一种）TEXT 字段，因此您可以使用 匹配 运算符。
要获取不匹配的列，只需在类似的前面放置一个 NOT：
WHERE textcolumn NOT LIKE "%SUBSTRING%"。搜索是否区分大小写取决于您存储数据的方式，尤其是您使用的排序规则。默认情况下，搜索不区分大小写。
更新答案以反映问题更新：
我说如果列字段有一个
WHERE field LIKE "%value%"比WHERE field LIKE "value%"慢索引，但这仍然比获取所有值并进行应用程序过滤要快得多。两种情况：1/如果你执行
SELECT field FROM table WHERE field LIKE "%value%"，MySQL将扫描整个表，并且只发送包含“value”的字段。2/如果你执行
SELECT field FROM table，然后让你的应用程序（在你的例子中是PHP）仅过滤其中包含“值”的行，MySQL也会扫描整个表，但发送所有字段转换为 PHP，然后 PHP 必须做额外的工作。这比情况 1 慢得多。解决方案：请务必使用
WHERE子句，并使用EXPLAIN来查看性能。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.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.
To get the columns that don't match, simply put a NOT in front of the like:
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.
Updated answer to reflect question update:
I say that doing a
WHERE field LIKE "%value%"is slower thanWHERE 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/ 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/ 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.Solution: Please do use the
WHEREclause, and useEXPLAINto see the performance.有关 MySQL 全文搜索的信息。这仅限于 MyISAM 表，因此如果您想使用不同的表类型，可能不适合。
http://dev.mysql.com/doc/refman/ 5.0/en/fulltext-search.html
即使
WHERE textcolumn LIKE "%SUBSTRING%"会很慢，我认为让数据库处理它可能更好，而不是让 PHP 处理它。如果可以通过其他条件（日期范围、用户等）限制搜索，那么您可能会发现子字符串搜索是可以的（ish）。如果您要搜索整个单词，您可以将所有单个单词提取到一个单独的表中，并使用它来限制子字符串搜索。 （因此，当搜索“我的搜索字符串”时，您会查找最长的单词“搜索”，仅对包含单词“搜索”的记录进行子字符串搜索）
Info on MySQL's full text search. This is restricted to MyISAM tables, so may not be suitable if you want to use a different table type.
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).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")
我只是使用 SELECT ColumnName1, ColumnName2,.....WHERE LOCATE(subtr, ColumnNameX)<>0
获取具有子字符串的
ColumnNameX的行。将
<>替换为=以获取NOT包含子字符串的行。I simply use
SELECT ColumnName1, ColumnName2,.....WHERE LOCATE(subtr, ColumnNameX)<>0To get rows with
ColumnNameXhaving the substring.Replace
<>with=to get rowsNOThaving the substring.