MySQL 用 vsql_trgm 拡張機能

INSTALL EXTENSION vsql_trgm;

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

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

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 |
+----------------+-------+

SELECT trgm_show('Smith') AS trigrams;

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