mysql - Mysql全文搜索,自然语言模式:按“亲密度”排序
问题描述
我正在使用 MYSQL 的全文搜索功能(在 Mysql 5.6.33 中)。
如果我在 NATURAL LANGUAGE 模式下进行 MATCH,对于带有一个字符拼写错误的邮政编码,我会得到一些不错的结果,包括带有“正确”邮政编码的结果,但它们不在顶部附近。
例如,有 10 所学校的邮政编码为"BN2 1TL"
。我故意将其拼错"BN2 1TM"
并进行如下搜索:
SELECT record_id, address_string,
MATCH (address_string) AGAINST ("BN2 1TM" IN NATURAL LANGUAGE MODE) AS score
FROM schools
WHERE MATCH (address_string) AGAINST ("BN2 1TM" IN NATURAL LANGUAGE MODE) > 0
ORDER BY score DESC;
仔细观察,这是因为搜索已经回购了所有在其列中具有"BN2"
或的结果,并且它们都具有完全相同的分数,因此有效地按随机顺序排列。."1TM"
address_string
这是完全合理的行为,但如果我能得到考虑“接近度”的分数,那就太好了,这意味着,对于 的搜索"BN2 1TM"
,"BN2 1TL"
得分将高于"BN2 3PQ"
. 有没有办法做到这一点?
编辑:我记得这种接近在技术上称为“Levenshtein 距离”,它是对Levenshtein 算法的参考,用于确定将一个字符串转换为另一个字符串需要多少次替换。所以我想我的问题可能是“我可以得到 MYSQL FULLTEXT NATURAL LANGUAGE MODE 评分以考虑 Levenshtein 距离”吗?
解决方案
首先,MySQL 全文在开放式搜索方面不如 Lucene 等专用系统好。
有一种称为Levenshtein distance的算法,它计算字符转换的数量——距离——将一个字符串更改为另一个字符串。
因此,将“BN2 1TM”更改为“BN2 1MT”(换位)的距离为 2。将其更改为“BN2 1TX”的距离为 1。
除非它们几乎完全相同,否则 Levenshtein 距离对于短语并不是非常有用。将“Apache Sphinx”更改为“MySQL FULLTEXT”会给出 14 的距离,即较长字符串的长度。但它对于邮政编码、零件编号和其他简短的结构化单词很有用。
您可以尝试这样的方法来首先获得最接近的值。
SELECT city, county, postcode
FROM table
ORDER BY levenshtein(postcode, 'BN2 1MT') ASC
然后,您只需要一个存储函数来计算 Levenshtein 距离。(这不是 FULLTEXT 内置的。)
从这个来源,这里有一个这样的存储函数。但请注意,它并不快,而且它不能使用索引。因此,如果您可以在执行此操作之前缩小搜索范围,您将获得更好的性能。
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 ;
推荐阅读
- c++ - clang-tidy - 忽略第三方标头代码
- raspberry-pi - Ublox NEO-M6 和 Raspberry Pi3
- android - 如何在 react-native-charts-wrapper 中的图表上设置点选择?
- python - 在 django 中集成“直接消息”功能的最佳方法是什么?
- git - 在 Git 中以交互方式变基时的“标签”是什么
- binary - 符号大小和二的恭维
- bash - jq如何获取对象中的最后一个条目
- botium-box - “botium-cli 仿真器控制台”失败并显示“错误:缺少功能‘CONTAINERMODE’或‘BOTIUMGRIDURL’”
- python - 从循环中的多个字符串中提取数字
- python - 根据列的列表条目中的值删除行