php - 在mysql中搜索名称的最佳方法

Question

我想搜索名为“Martins Silva”的用户。我在 bool 模式中使用全文。

 MATCH(name,lastname) AGAINST('+martins +silva' IN BOOLEAN MODE)

“Martins Silva”的搜索结果返回

Orleans Silva De Martins (1)
Armistrong Oliveira Martins Da Silva (2)
Douglas Martins Vieira Da Silva (3)
Glauciene Silva Martins (4)
Jose Martins Silva (5)
...

问题是数据库中有一个名为“Martins Silva”的用户，但它只出现在这个结果集的第 540 位。

这是我阅读文档并查看排名计算方式后的预期结果。但是，它并不能帮助我解决这个问题。我也尝试使用 LIKE 进行搜索，但结果相同。

鉴于该结果集，对我来说最好的是:

Martins silva (540) -> because it is the exact phrase 
Jose Martins Silva (5) -> because it is the exact phrase that appears in a position first than in (2)
Armistrong Oliveira Martins Da Silva (2) -> distance between martins and silva is shorter than in (3)
Douglas Martins Vieira Da Silva (3)
Glauciene Silva Martins (4) -> lower priority when it is out of order
Orleans Silva De Martins (1)   

因此，我认为我可以使用一种算法来解决这个问题，该算法考虑了查询中单词的顺序或位置。

我尝试计算编辑距离，但对于大型数据库来说它真的很慢。

MySQL有办法解决这个问题吗？或者我必须使用 Apache Lucene 之类的东西？或者我做错了什么？此搜索是我网站上的主要内容，它必须运行良好。

非常感谢你们，伙计们!

Answer 1

在您的特定情况下，您需要实现一个 levenshtein 函数才能完成此操作。 match 根本无法以正确的方式进行。通过对 levenshtein 相关性 ASC 进行排序，您将从最相关到​​最不相关。

要添加到您的数据库的 levenshtein 函数:

DELIMITER $$
CREATE FUNCTION levenshtein( s1 VARCHAR(255), s2 VARCHAR(255) )
RETURNS INT
DETERMINISTIC
BEGIN
DECLARE s1_len, s2_len, i, j, c, c_temp, cost INT;
DECLARE s1_char CHAR;
-- max strlen=255
DECLARE cv0, cv1 VARBINARY(256);
SET s1_len = CHAR_LENGTH(s1), s2_len = CHAR_LENGTH(s2), cv1 = 0x00, j = 1, i = 1, c = 0;
IF s1 = s2 THEN
RETURN 0;
ELSEIF s1_len = 0 THEN
RETURN s2_len;
ELSEIF s2_len = 0 THEN
RETURN s1_len;
ELSE
WHILE j <= s2_len DO
SET cv1 = CONCAT(cv1, UNHEX(HEX(j))), j = j + 1;
END WHILE;
WHILE i <= s1_len DO
SET s1_char = SUBSTRING(s1, i, 1), c = i, cv0 = UNHEX(HEX(i)), j = 1;
WHILE j <= s2_len DO
SET c = c + 1;
IF s1_char = SUBSTRING(s2, j, 1) THEN
SET cost = 0; ELSE SET cost = 1;
END IF;
SET c_temp = CONV(HEX(SUBSTRING(cv1, j, 1)), 16, 10) + cost;
IF c > c_temp THEN SET c = c_temp; END IF;
SET c_temp = CONV(HEX(SUBSTRING(cv1, j+1, 1)), 16, 10) + 1;
IF c > c_temp THEN
SET c = c_temp;
END IF;
SET cv0 = CONCAT(cv0, UNHEX(HEX(c))), j = j + 1;
END WHILE;
SET cv1 = cv0, i = i + 1;
END WHILE;
END IF;
RETURN c;
END$$
DELIMITER ;

此模态查询将首先按最相关的姓氏排序，然后按姓名排序，并将最相关的显示为第一行，添加 LIMIT 1 以仅获得最相关的结果:

Select lastname, levenshtein(lastname,$var1) as relevance1,
name, levenshtein(name,$var2) as relevance2
FROM database
ORDER BY relevance 1 ASC, relevance 2 ASC