> ## Documentation Index
> Fetch the complete documentation index at: https://villagesql.com/docs/llms.txt
> Use this file to discover all available pages before exploring further.

# vsql_trgm extension for MySQL

> The vsql_trgm extension brings pg_trgm trigram similarity to MySQL, scoring how alike two strings are so a misspelled search term still finds its row.

`vsql_trgm` is a port of PostgreSQL's `pg_trgm`. It breaks a string into
three-character pieces and scores two strings by how many pieces they share, so
a misspelled search term still finds its row. Use it when `LIKE` is too strict
and full-text search is too coarse.

|                                   |                                                                 |
| --------------------------------- | --------------------------------------------------------------- |
| **Maintainer**                    | VillageSQL                                                      |
| **Source and full documentation** | [villagesql/vsql-trgm](https://github.com/villagesql/vsql-trgm) |
| **License**                       | GPL-2.0                                                         |

## Install

`vsql_trgm.veb` is already in the server's `lib/veb/` directory if you installed
VillageSQL with the install script, the Docker image, or a release tarball.
Install it into the server with one statement:

```sql theme={null}
INSTALL EXTENSION vsql_trgm;
```

Confirm it is there:

```sql theme={null}
SELECT EXTENSION_NAME
FROM INFORMATION_SCHEMA.EXTENSIONS
WHERE EXTENSION_NAME = 'vsql_trgm';
```

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

To build it yourself, follow the build instructions in the
[repository](https://github.com/villagesql/vsql-trgm).

## What it adds

It adds three families of function. The plain family compares two whole
strings. The
word family finds the best-matching substring of the second string. The strict
word family does the same but aligns on word boundaries. Each family has its
own default threshold.

| Function                            | Returns | What it does                                                 |
| ----------------------------------- | ------- | ------------------------------------------------------------ |
| `trgm_show(s)`                      | text    | The trigrams of a string, as a JSON array                    |
| `trgm_similarity(a, b)`             | real    | The share of trigrams the two strings have in common, 0 to 1 |
| `trgm_distance(a, b)`               | real    | 1 minus the similarity                                       |
| `trgm_similar(a, b)`                | int     | 1 when the similarity reaches 0.3                            |
| `trgm_similar_threshold(a, b, t)`   | int     | 1 when it reaches a threshold you choose, between 0 and 1    |
| `trgm_word_similarity(a, b)`        | real    | The best similarity between `a` and any substring of `b`     |
| `trgm_word_distance(a, b)`          | real    | 1 minus that                                                 |
| `trgm_word_similar(a, b)`           | int     | 1 when the word similarity reaches 0.6                       |
| `trgm_strict_word_similarity(a, b)` | real    | The same as the word form, aligned on word boundaries        |
| `trgm_strict_word_distance(a, b)`   | real    | 1 minus that                                                 |
| `trgm_strict_word_similar(a, b)`    | int     | 1 when the strict word similarity reaches 0.5                |

## Example

Build a small name table:

```sql theme={null}
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');
```

Search it for a spelling nobody typed, and rank what comes back:

```sql theme={null}
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.6 |
| Joan Smithe    | 0.583 |
| John Smyth     | 0.455 |
+----------------+-------+
```

Peter Brown shares too few trigrams to reach 0.3, so it never appears. To see
what is being compared, ask for the trigrams themselves:

```sql theme={null}
SELECT trgm_show('Smith') AS trigrams;
```

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

The padding spaces are deliberate. They let the start and end of a string count
toward the score.

<Note>
  PostgreSQL's `set_limit()` has no counterpart here, because a VEF function
  keeps no session state. Pass the threshold you want to
  `trgm_similar_threshold()` instead. A similarity filter also reads every row,
  so measure it before running one against a large table.
</Note>

## See also

* [Trigram similarity in MySQL](/docs/guides/trigram-similarity) — thresholds, ranking, and what it costs on a large table
* [Fuzzy string matching](/docs/guides/fuzzy-string-matching) — phonetic codes and edit distance as alternatives
* [Full-text search](/docs/guides/full-text-search) — the right tool for documents rather than names
* [Install extensions](/docs/mysql-9.7/stable/install) — how `INSTALL EXTENSION` works and where the server looks for a bundle
* [Available extensions](/docs/mysql-9.7/stable/extensions) — the full catalog
* [villagesql/vsql-trgm](https://github.com/villagesql/vsql-trgm) — source, build instructions, and the known limitations