用PHP搜索MySQL数据库的最好方法是什么?

Say if I had a table of books in a MySQL database and I wanted to search the 'title' field for keywords (input by the user in a search field); what's the best way of doing this in PHP? Is the MySQL LIKE command the most efficient way to search?

假设我在MySQL数据库中有一个图书表，我想在“title”字段中搜索关键字(由用户在搜索字段中输入);在PHP中，最好的方法是什么?像MySQL这样的命令是最有效的搜索方式吗?

6 个解决方案

#1

Yes, the most efficient way usually is searching in the database. To do that you have three alternatives:

是的，最有效的方法通常是在数据库中搜索。要做到这一点，你有三种选择:

LIKE, ILIKE to match exact substrings
比如，我想匹配精确的子字符串
RLIKE to match POSIX regexes
匹配POSIX正则表达式。
FULLTEXT indexes to match another three different kinds of search aimed at natural language processing
全文索引匹配另外三种针对自然语言处理的搜索

So it depends on what will you be actually searching for to decide what would the best be. For book titles I'd offer a LIKE search for exact substring match, useful when people know the book they're looking for and also a FULLTEXT search to help find titles similar to a word or phrase. I'd give them different names on the interface of course, probably something like exact for the substring search and similar for the fulltext search.

这取决于你要寻找什么来决定什么是最好的。对于图书标题，我将提供一个类似于搜索精确字幕匹配的搜索，当人们知道他们正在寻找的书的时候，这是很有用的;同时，我还提供了一个完整的文本搜索，以帮助查找与单词或短语类似的标题。我在界面上给它们取不同的名字，可能是类似于子字符串搜索的名字，类似于全文搜索的名字。

An example about fulltext: http://www.onlamp.com/pub/a/onlamp/2003/06/26/fulltext.html

关于全文的一个例子:http://www.onlamp.com/pub/a/onlamp/2003/06/26/fulltext.html。

#2

Here's a simple way you can break apart some keywords to build some clauses for filtering a column on those keywords, either ANDed or ORed together.

这里有一种简单的方法，您可以分解一些关键字来构建一些子句，以便在这些关键字上过滤一列，这些子句可以是用ANDed的，也可以是全称的。

$terms=explode(',', $_GET['keywords']);
$clauses=array();
foreach($terms as $term)
{
    //remove any chars you don't want to be searching - adjust to suit
    //your requirements
    $clean=trim(preg_replace('/[^a-z0-9]/i', '', $term));   
    if (!empty($clean))
    {
         //note use of mysql_escape_string - while not strictly required
         //in this example due to the preg_replace earlier, it's good
         //practice to sanitize your DB inputs in case you modify that
         //filter...
         $clauses[]="title like '%".mysql_escape_string($clean)."%'";
    }
}

if (!empty($clauses))
{   
    //concatenate the clauses together with AND or OR, depending on
    //your requirements
    $filter='('.implode(' AND ', $clauses).')';

    //build and execute the required SQL
    $sql="select * from foo where $filter";
}
else
{
    //no search term, do something else, find everything?
}

#3

Consider using sphinx. It's an open source full text engine that can consume your mysql database directly. It's far more scalable and flexible than hand coding LIKE statements (and far less susceptible to SQL injection)

考虑使用斯芬克斯。它是一个开源的全文引擎，可以直接使用mysql数据库。它比像语句这样的手工编码更具有可扩展性和灵活性(而且更不容易受到SQL注入的影响)

#4

You may also check soundex functions (soundex, sounds like) in mysql manual http://dev.mysql.com/doc/refman/5.0/en/string-functions.html#function_soundex Its functional to return these matches if for example strict checking (by LIKE or =) did not return any results.

您还可以在mysql手册http://dev.mysql.com/doc/refman/5.0/en/string-functions.html#function_soundex中检查soundex函数(soundex，听起来像)，如果严格检查(by like或=)没有返回任何结果，那么它的函数可以返回这些匹配。

#5

Paul Dixon's code example gets the main idea across well for the LIKE-based approach.

Paul Dixon的代码示例很好地理解了基于类的方法的主要思想。

I'll just add this usability idea: Provide an (AND | OR) radio button set in the interface, default to AND, then if a user's query results in zero (0) matches and contain at least two words, respond with an option to the effect:

我将添加这个可用性概念:在界面中提供一个(和|或)单选按钮，默认设置为，然后，如果用户的查询结果为零(0)匹配并包含至少两个单词，则响应一个选项:

"Sorry, No matches were found for your search phrase. Expand search to match on ANY word in your phrase?

“对不起，你的搜索词没有找到匹配的词。”扩展搜索以匹配你的短语中的任何单词?

Maybe there's a better way to word this, but the basic idea is to guide the person toward another query (that may be successful) without the user having to think in terms of the Boolean logic of AND and ORs.

也许有更好的表达方式，但是基本思想是引导用户使用另一个查询(可能会成功)，而不需要用户考虑AND和or的布尔逻辑。

#6

I think Like is the most efficient way if it's a word. Multi words may be split with explode function as said already. It may then be looped and used to search individually through the database. If same result is returned twice, it may be checked by reading the values into an array. If it already exists in the array, ignore it. Then with count function, you'll know where to stop while printing with a loop. Sorting may be done with similar_text function. The percentage is used to sort the array. That's the best.

我认为喜欢是最有效的方式如果它是一个词。如前所述，多个单词可以用爆炸功能进行拆分。然后，它可能被圈起来，用于单独通过数据库进行搜索。如果返回相同的结果两次，可以通过将值读入数组来检查。如果它已经存在于数组中，则忽略它。然后使用count函数，您将知道在使用循环打印时在何处停止。排序可以使用similar_text函数完成。百分比用于对数组进行排序。这是最好的。

#1