VillageSQL is a drop-in replacement for MySQL with extensions.
All examples on this page work on VillageSQL. Install Now →
本指南需要 VillageSQL 0.0.4 或更高版本。
LIKE、SOUNDEX、LEVENSHTEIN——可以完成基本匹配,但它们都无法给出一个可以用来排序或设定阈值的连续相似度分数。VillageSQL 的 vsql_trgm 扩展增加了基于三元组的相似度:一个介于 0 和 1 之间的数字,用来衡量两个字符串有多相似,因此您可以在一条查询中对结果排序并按相似度过滤。
三元组的工作原理
三元组是由 3 个连续字符组成的一组。vsql_trgm 将输入转为小写,按非字母数字字符拆分,在每个单词前面补两个空格、后面补一个空格,然后在每个补齐后的单词上滑动一个 3 字符的窗口。相似度是两个输入共有的三元组数量,除以两者合计的不同三元组数量——即它们并集的大小,而不是较大集合的大小。
"Cat" 和 "cat" 产生相同的三元组。它们还会按非字母数字字符拆分,因此每个单词会被独立补齐并生成三元组——"hello world" 分别从 " hello " 和 " world " 生成三元组,而不是从一个补齐后的整体字符串生成。不存在 "o w" 或 " wo" 这样的三元组。
相似度的范围从 0(没有共同之处)到 1(完全相同)。
开始使用
核心函数
trgm_similarity——连续分数
trgm_similar——阈值检查
如果相似度等于或高于 0.3(PostgreSQL 的默认值),返回 1,否则返回 0:trgm_distance——相似度的补数
即1 - similarity。当您希望最接近的结果排在最前面时,适合用于 ORDER BY:
在表中查找近似匹配
最常见的用法:查找与搜索词相似的行。LIKE '%adidas%' 会漏掉的情况。
单词相似度
trgm_word_similarity 查找查询字符串与目标中任意连续子串之间的最佳匹配。当搜索词可能是较长字符串中的一个单词时,这很有用。
trgm_strict_word_similarity 是一个更严格的变体,要求匹配与单词边界对齐。
查看三元组
trgm_show 以 JSON 数组的形式返回字符串排序后的三元组集合:
函数参考
已知限制
没有索引加速。 扩展 SDK 中提供了自定义索引类型的预览功能,但在每个服务器构建版本上,用于附加索引的 DDL 都会被拒绝(ERROR 3219: Extended Index feature not yet implemented),因此 WHERE trgm_similarity(col, 'term') > 0.3 需要全表扫描。对于大表,请先用 LIKE 或全文搜索预过滤以缩小候选集,然后再用 trgm_similarity 重新评分。
没有会话级阈值。 PostgreSQL 的 set_limit() / show_limit() 无法在 VEF 中实现(VEF 函数是无状态的)。请使用 trgm_similar_threshold(a, b, t) 显式传入阈值。
不返回数组。 trgm_show 返回的是 JSON 字符串,而不是原生数组类型。