mysql levenshtein_数据库-Levenshtein距离的MySQL /模糊搜索的实现？

最新推荐文章于 2024-05-14 22:30:00 发布

央企小老头

最新推荐文章于 2024-05-14 22:30:00 发布

阅读量463

点赞数

文章标签： mysql levenshtein

本文链接：https://blog.csdn.net/weixin_34990449/article/details/113514607

版权

这篇博客讨论了如何在MySQL中实现Levenshtein距离，用于模糊搜索。用户提出了一个问题，希望能在搜索时获取与输入词差异在1内的数据。文章列举了多个答案，提供了不同的MySQL用户定义函数(UDF)实现和优化技巧，以提高基于Levenshtein距离的搜索效率。

摘要由CSDN通过智能技术生成

数据库-Levenshtein距离的MySQL /模糊搜索的实现？

我希望能够按以下方式搜索史密斯表，以获取1个方差内的所有数据。

数据：

O'Brien

Smithe

Dolan

Smuth

Wong

Smoth

Gunther

Smiht

我已经研究过使用Levenshtein距离，有人知道如何实现它吗？

9个解决方案

11 votes

为了使用levenshtein距离进行有效搜索，您需要高效的专业索引，例如bk树。不幸的是，我所知没有数据库系统(包括MySQL)实现bk-tree索引。如果您要查找全文搜索，而不是每行仅搜索一个术语，则情况将更加复杂。临时而言，我想不出以允许基于levenshtein距离进行搜索的方式进行全文索引的任何方式。

Nick Johnson answered 2020-07-27T04:18:00Z

7 votes

Levenshtein Distance函数有一个MySQL UDF实现

[HTTPS://GitHub.com/精密测角啦/leven石忒牛-MySQL-UDF]

它用C语言实现，比schnaader提到的“ MySQL Levenshtein距离查询”具有更好的性能。

Hongzheng answered 2020-07-27T04:18:28Z

5 votes

此处可以找到damerau-levenshtein距离的实现：Damerau-Levenshtein算法：具有换位的Levenshtein纯Levenshtein距离的改进在于考虑了字符交换。我在schnaader链接的评论中找到了它，谢谢！

Ponk answered 2020-07-27T04:18:49Z

4 votes

上面的levenshtein <= 1给出的功能不正确-例如，“ bed”和“ bid”给出的结果不正确。

在第一个答案中，我修改了上面给出的“ MySQL Levenshtein距离查询”，以接受一个“限制”，它将使速度加快一点。基本上，如果只关心Levenshtein <= 1，则将限制设置为“ 2”，该函数将返回确切的levenshtein距离(如果为0或1)；否则，它将返回确切的levenshtein距离。如果确切的levenshtein距离为2或更大，则为2。

此mod使其速度提高15％到50％-搜索单词越长，优势就越大(因为该算法可以更早地保释。)例如，对200,000个单词进行搜索以查找该单词距离1之内的所有匹配项 “咯咯地笑”，原件在我的笔记本电脑上需要3分47秒，而“极限”版本则为1:39。当然，它们对于任何实时使用都太慢了。

码：

DELIMITER $$

CREATE FUNCTION levenshtein_limit_n( s1 VARCHAR(255), s2 VARCHAR(255), n INT)

RETURNS INT

DETERMINISTIC

BEGIN

DECLARE s1_len, s2_len, i, j, c, c_temp, cost, c_min 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, c_min = 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 and c_min < n DO -- if actual levenshtein dist >= limit, don't bother computing it

SET s1_char = SUBSTRING(s1, i, 1), c = i, c_min = 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;

IF c < c_min THEN

SET c_min = c;

END IF;

END WHILE;

SET cv1 = cv0, i = i + 1;

END WHILE;

END IF;

IF i <= s1_len THEN -- we didn't finish, limit exceeded

SET c = c_min; -- actual distance is >= c_min (i.e., the smallest value in the last computed row of the matrix)

END IF;

RETURN c;

END$$

stopthe answered 2020-07-27T04:19:23Z

3 votes

你可以使用这个功能

CREATE FUNCTION `levenshtein`( s1 text, s2 text) RETURNS int(11)

DETERMINISTIC

BEGIN

DECLARE s1_len, s2_len, i, j, c, c_temp, cost INT;

DECLARE s1_char CHAR;

DECLARE cv0, cv1 text;

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

并以XX％的价格使用此功能

CREATE FUNCTION `levenshtein_ratio`( s1 text, s2 text ) RETURNS int(11)

DETERMINISTIC

BEGIN

DECLARE s1_len, s2_len, max_len INT;

SET s1_len = LENGTH(s1), s2_len = LENGTH(s2);

IF s1_len > s2_len THEN

SET max_len = s1_len;

ELSE

SET max_len = s2_len;

END IF;

RETURN ROUND((1 - LEVENSHTEIN(s1, s2) / max_len) * 100);

END

Alaa answered 2020-07-27T04:19:47Z

2 votes

根据Gonzalo Navarro和Ricardo Baeza-yates的论文，我正在基于Levenshtein或Damerau-Levenshtein(可能是后者)进行搜索，以对索引文本进行多次搜索：链接文本

建立后缀数组后(请参阅Wikipedia)，如果您对与搜索字符串最多不匹配k的字符串感兴趣，请将搜索字符串分成k + 1条；至少其中之一必须完好无损。通过对后缀数组进行二进制搜索来找到子字符串，然后将距离函数应用于每个匹配片段周围的补丁。

John Conde answered 2020-07-27T04:20:13Z

2 votes

如果您只想知道levenshtein距离是否最大为1，则可以使用以下MySQL函数。

CREATE FUNCTION `lv_leq_1` (

`s1` VARCHAR( 255 ) ,

`s2` VARCHAR( 255 )

) RETURNS TINYINT( 1 ) DETERMINISTIC

BEGIN

DECLARE s1_len, s2_len, i INT;

SET s1_len = CHAR_LENGTH(s1), s2_len = CHAR_LENGTH(s2), i = 1;

IF s1 = s2 THEN

RETURN TRUE;

ELSEIF ABS(s1_len - s2_len) > 1 THEN

RETURN FALSE;

ELSE

WHILE SUBSTRING(s1,s1_len - i,1) = SUBSTRING(s2,s2_len - i,1) DO

SET i = i + 1;

END WHILE;

RETURN SUBSTRING(s1,1,s1_len-i) = SUBSTRING(s2,1,s2_len-i) OR SUBSTRING(s1,1,s1_len-i) = SUBSTRING(s2,1,s2_len-i+1) OR SUBSTRING(s1,1,s1_len-i+1) = SUBSTRING(s2,1,s2_len-i);

END IF;

END

这基本上是递归描述levenshtein距离的一个步骤。如果距离最大为1，则该函数返回1，否则返回0。

由于此函数不能完全计算出leevenshtein距离，因此它要快得多。

您还可以修改此函数，以使levenshtein距离最大为2或3时，可以通过递归调用它来返回true。如果MySQL不支持递归调用，则可以复制此函数的稍作修改的版本两次，然后调用它们。但是，您不应使用递归函数来计算确切的levenshtein距离。

AbcAeffchen answered 2020-07-27T04:20:46Z

0 votes

我有一个特殊的k距离搜索案例，在MySQL中安装Damerau-Levenshtein UDF之后，发现查询花费了太长时间。我想出了以下解决方案：

我的搜索空间非常有限(9个字符串限于数字值)。

使用目标字段中每个字符位置的列创建一个新表(或将列追加到目标表中)。即。我的VARCHAR(9)最终是9个TINYINT列+ 1个与我的主表匹配的Id列(为每个列添加索引)。我添加了触发器以确保在更新主表时始终更新这些新列。

要执行k距离查询，请使用以下谓词：

(Column1 = s [0])+(Column2 = s [1])+(Column3 = s [2])+(Column4 = s [3])+ ...> = m

其中s是您的搜索字符串，m是所需的匹配字符数(在我的情况下，m = 9-d，其中d是我想返回的最大距离)。

经过测试后，我发现超过一百万行的查询平均花费4.6秒，不到一秒钟就返回了匹配的ID。类似地，第二次查询返回主表中匹配行的数据花费了不到一秒钟。 (将这两个查询合并为子查询或联接会大大延长执行时间，但我不确定为什么。)

尽管这不是Damerau-Levenshtein(不考虑替代)，但足以满足我的目的。

尽管对于较大的(长度)搜索空间，此解决方案可能无法很好地扩展，但在这种限制性情况下效果很好。

greg answered 2020-07-27T04:21:43Z

0 votes

根据Chella的答案和Ryan Ginstrom的文章，可以这样执行模糊搜索：

DELIMITER $$

CREATE FUNCTION fuzzy_substring( 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(0))), 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;

SET j = 1;

WHILE j <= s2_len DO

SET c_temp = CONV(HEX(SUBSTRING(cv1, j, 1)), 16, 10);

IF c > c_temp THEN

SET c = c_temp;

END IF;

SET j = j + 1;

END WHILE;

RETURN c;

END$$

DELIMITER ;

D. Savina answered 2020-07-27T04:22:03Z

央企小老头

关注

0
点赞
踩
0

收藏

觉得还不错? 一键收藏
0
评论
mysql levenshtein_数据库-Levenshtein距离的MySQL /模糊搜索的实现？

数据库-Levenshtein距离的MySQL /模糊搜索的实现？我希望能够按以下方式搜索史密斯表，以获取1个方差内的所有数据。数据：O'BrienSmitheDolanSmuthWongSmothGuntherSmiht我已经研究过使用Levenshtein距离，有人知道如何实现它吗？9个解决方案11 votes为了使用levenshtein距离进行有效搜索，您需要高效的专业索引，例如bk树。 ...
复制链接

扫一扫