MySQL 的 vsql_trgm 扩展

vsql_trgm 是 PostgreSQL pg_trgm 的移植版本。它把字符串拆成三字符片段，并按两个字符串共享多少片段来打分，因此拼错的搜索词仍能找到对应的行。当 LIKE 太严格而全文搜索太粗糙时，请使用它。


维护者	VillageSQL
源代码和完整文档	villagesql/vsql-trgm
许可证	GPL-2.0

安装

如果您使用安装脚本、Docker 镜像或发行版压缩包安装了 VillageSQL， vsql_trgm.veb 已经位于服务器的 lib/veb/ 目录中。用一条语句将它安装到服务器中：

INSTALL EXTENSION vsql_trgm;

确认它已存在：

SELECT EXTENSION_NAME
FROM INFORMATION_SCHEMA.EXTENSIONS
WHERE EXTENSION_NAME = 'vsql_trgm';

+----------------+
| EXTENSION_NAME |
+----------------+
| vsql_trgm      |
+----------------+

要自行构建，请按照存储库中的构建说明进行操作。

新增内容

它添加了三类函数。普通类比较两个完整的字符串。单词类在第二个字符串中查找匹配度最高的子串。严格单词类做同样的事，但按单词边界对齐。每一类都有自己的默认阈值。

函数	返回	作用
`trgm_show(s)`	文本	字符串的三元组，以 JSON 数组表示
`trgm_similarity(a, b)`	实数	两个字符串共有的三元组所占比例，0 到 1
`trgm_distance(a, b)`	实数	1 减去相似度
`trgm_similar(a, b)`	整数	相似度达到 0.3 时为 1
`trgm_similar_threshold(a, b, t)`	整数	达到您选择的阈值（0 到 1 之间）时为 1
`trgm_word_similarity(a, b)`	实数	`a` 与 `b` 的任意子串之间的最高相似度
`trgm_word_distance(a, b)`	实数	1 减去该值
`trgm_word_similar(a, b)`	整数	单词相似度达到 0.6 时为 1
`trgm_strict_word_similarity(a, b)`	实数	与单词形式相同，但按单词边界对齐
`trgm_strict_word_distance(a, b)`	实数	1 减去该值
`trgm_strict_word_similar(a, b)`	整数	严格单词相似度达到 0.5 时为 1

示例

建一个小的姓名表：

CREATE TABLE people (id INT PRIMARY KEY, name VARCHAR(32));

INSERT INTO people VALUES
    (1, 'Jonathan Smith'),
    (2, 'John Smyth'),
    (3, 'Joan Smithe'),
    (4, 'Peter Brown');

用一个没人输入过的拼写搜索它，并对结果排序：

SELECT name, ROUND(trgm_similarity(name, 'Jon Smith'), 3) AS score
FROM people
WHERE trgm_similar(name, 'Jon Smith') = 1
ORDER BY score DESC;

+----------------+-------+
| name           | score |
+----------------+-------+
| Jonathan Smith | 0.562 |
| Joan Smithe    | 0.467 |
| John Smyth     | 0.312 |
+----------------+-------+

Peter Brown 共享的三元组太少，达不到 0.3，因此始终不会出现。要查看比较的是什么，请直接索取三元组：

SELECT trgm_show('Smith') AS trigrams;

+---------------------------------------+
| trigrams                              |
+---------------------------------------+
| ["  s"," sm","ith","mit","smi","th "] |
+---------------------------------------+

填充的空格是有意为之。它们让字符串的开头和结尾也计入分数。

PostgreSQL 的 set_limit() 在这里没有对应物，因为 vsql_trgm 不保存会话状态。请改为把您想要的阈值传给 trgm_similar_threshold()。相似度过滤器也会读取每一行，因此在对大表运行之前请先测量。

另请参阅

MySQL 中的三元组相似度 — 阈值、排序，以及在大表上的代价
模糊字符串匹配 — 作为替代方案的语音编码和编辑距离
全文搜索 — 适合文档而不是姓名的工具
安装扩展 — INSTALL EXTENSION 如何工作，以及服务器在哪里查找包
可用扩展 — 完整目录
villagesql/vsql-trgm — 源代码、构建说明和已知限制

安装和运行扩展

扩展目录

构建扩展

安装

新增内容

示例

另请参阅

​安装

​新增内容

​示例

​另请参阅

安装

新增内容

示例

另请参阅