php - MySQL 搜索多个字段并对结果进行排序

Question

我有一个结构如下的数据库:

用户表

ID |  firstname  | lastname   | email
________________________________________________
 1 |  Carl       | Andersson  | carl@site.se
 2 |  Carl       | Smith      | smith@site.se
 3 |  Eve        | Carlow     | carlow@site.se
 4 |  Randy      | Smith      | randy@site.se

我需要一个允许我搜索所有字段的搜索功能。我尝试过使用类似的语句

$query = "SELECT * FROM users WHERE firstname LIKE '%" . $KEYWORD . "%' OR lastname LIKE '%" . $KEYWORD . "%' OR email LIKE '%" . $KEYWORD . "%'"; 

以上方法有效，但结果列表未根据相关性正确排序。

示例 1

如果用户搜索“Carl Andersson”，则不会有匹配项，因为名字和姓氏存储在不同的字段中。

我也尝试过像这样使用 REGEXP:

$KEYWORD = str_replace (" ", "|", $KEYWORD); 
$query = "SELECT * FROM users WHERE firstname REGEXP '%" . $KEYWORD . "%' OR lastname REGEXP '%" . $KEYWORD . "%' OR email REGEXP '%" . $KEYWORD . "%'";

示例 2

如果用户搜索“Carl Smith”，用户 1、2、3 都会显示。但相关顺序会是错误的。 ID 1 将首先显示，因为该条目是第一个找到的条目。

问题:如何搜索所有字段而不丢失命中并保留相关顺序？

Answer 1

我使用 mysql 全文索引匹配解决了这个问题。

第 1 步:为相关字段添加全文索引

ALTER TABLE  users  
ADD FULLTEXT(firstname, lastname, email);

第 2 步:使用 MATCH() AGAINST() 进行搜索

$query = "SELECT productName, productline FROM products WHERE MATCH(firstname, lastname, email) AGAINST('Carl Smith')";

此方法根据相关性自动对命中进行排序。

陷阱

1. 要使此方法起作用，您的表需要是 myISAM 类型，而不是InnoDB
2. 搜索查询的长度必须至少为 4 个字符，否则不会返回任何匹配项。

解决方法

您可以使用查询扩展来搜索较短的关键字或使用 MATCH() AGAINST() IN BOOLEAN MODE。这允许您添加星号 (*) 将字符串填充到有效长度。

有关全文搜索的更多阅读:http://www.mysqltutorial.org/mysql-full-text-search.aspxmysql