搜索给定字符串的数据库的好算法

Question

我正在开发一个web应用程序(PHP + MySQL)，用户可以通过输入一些搜索字符串来搜索其他用户。

我需要将用户的输入字符串匹配到DB中“User”表的2列(username和fullName)，并返回最相关的(20或50)匹配。最理想的是，我还需要考虑拼写错误。

我怎么才能接近这个？我不想在这里重新发明轮子。

Answer 1

你可以用MySQL全文搜索来做

请看一下这，这，这的文章。

我想解释一下关于布尔全文搜索的事情，但是我建议你也通过使用查询扩展的全文搜索。

让我们看看在dev.mysql.com上给出的示例表：

mysql> select * from articles;
+----+-----------------------+------------------------------------------+
| id | title                 | body                                     |
+----+-----------------------+------------------------------------------+
|  1 | PostgreSQL Tutorial   | DBMS stands for DataBase ...             |
|  2 | How To Use MySQL Well | After you went through a ...             |
|  3 | Optimizing MySQL      | In this tutorial we will show ...        |
|  4 | 1001 MySQL Tricks     | 1. Never run mysqld as root. 2. ...      |
|  5 | MySQL vs. YourSQL     | In the following database comparison ... |
|  6 | MySQL Security        | When configured properly, MySQL ...      |
+----+-----------------------+------------------------------------------+

mysql> SELECT * FROM articles WHERE MATCH (title,body)
     AGAINST ('"database comparison"' IN BOOLEAN MODE);

+----+-------------------+------------------------------------------+
| id | title             | body                                     |
+----+-------------------+------------------------------------------+
|  5 | MySQL vs. YourSQL | In the following database comparison ... |
+----+-------------------+------------------------------------------+

当下列文字被引用时，命令很重要：

mysql> SELECT * FROM articles WHERE MATCH (title,body)
     AGAINST ('"comparison database"' IN BOOLEAN MODE);

Empty set (0.01 sec)

当我们删除引号时，它将搜索行，其中包含单词“数据库”或“比较”：

mysql> SELECT * FROM articles WHERE MATCH (title,body)
     AGAINST ('database comparison' IN BOOLEAN MODE);

+----+---------------------+------------------------------------------+
| id | title               | body                                     |
+----+---------------------+------------------------------------------+
|  1 | PostgreSQL Tutorial | DBMS stands for DataBase ...             |
|  5 | MySQL vs. YourSQL   | In the following database comparison ... |
+----+---------------------+------------------------------------------+

现在秩序已经不重要了：

mysql> SELECT * FROM articles WHERE MATCH (title,body)
     AGAINST ('comparison database' IN BOOLEAN MODE);

+----+---------------------+------------------------------------------+
| id | title               | body                                     |
+----+---------------------+------------------------------------------+
|  1 | PostgreSQL Tutorial | DBMS stands for DataBase ...             |
|  5 | MySQL vs. YourSQL   | In the following database comparison ... |
+----+---------------------+------------------------------------------+

如果我们想获得包含单词"PostgreSQL“或短语”数据库比较“的行，我们应该使用以下请求：

mysql> SELECT * FROM articles WHERE MATCH (title,body)
     AGAINST ('PostgreSQL "database comparison"' IN BOOLEAN MODE);

+----+---------------------+------------------------------------------+
| id | title               | body                                     |
+----+---------------------+------------------------------------------+
|  1 | PostgreSQL Tutorial | DBMS stands for DataBase ...             |
|  5 | MySQL vs. YourSQL   | In the following database comparison ... |
+----+---------------------+------------------------------------------+

小提琴试着

确保您正在搜索的单词不在停止词组列表中，这些单词被忽略了。

(很明显，像'is'，‘is’，‘is 停止词’这样的词被忽略了)

为了在布尔模式下增强结果的排序，可以使用以下查询：

(假设用户的输入字符串中总共有2个单词)。

SELECT column_names, MATCH (text) AGAINST ('word1 word2')
AS col1 FROM table1
WHERE MATCH (text) AGAINST ('+word1 +word2' in boolean mode) 
order by col1 desc;

(如果用户的输入字符串中有3个单词)那么..。

SELECT column_names, MATCH (text) AGAINST ('word1 word2 word3')
AS col1 FROM table1
WHERE MATCH (text) AGAINST ('+word1 +word2 +word3' in boolean mode) 
order by col1 desc;

使用first MATCH() ，我们得到了非布尔搜索模式(更有特色的)的得分。second MATCH() 确保我们真正只得到我们想要的结果(包含所有三个单词)。

Answer 2

我会用两列做一个类似的搜索

我的查询应该是这样的

选择* from ( select * from user where (用户名类似于‘%{input}%’或lastName类似于‘%{input}%’)和rownum =20

您需要编写一个自定义清理算法，以清除输入的拼写错误。