Pular para o conteúdo principal

VillageSQL is a drop-in replacement for MySQL with extensions.

All examples on this page work on VillageSQL. Install Now →
Este guia requer o VillageSQL 0.0.4 ou posterior.
As funções de string internas do MySQL — LIKE, SOUNDEX, LEVENSHTEIN — cobrem a correspondência básica, mas nenhuma delas fornece uma pontuação contínua de similaridade que você possa usar para ordenar ou aplicar um limiar. A extensão vsql_trgm do VillageSQL adiciona similaridade baseada em trigramas: um número entre 0 e 1 que mede o quanto duas strings se parecem, para que você possa classificar resultados e filtrar por similaridade em uma única consulta.

Como os trigramas funcionam

Um trigrama é um grupo de 3 caracteres consecutivos. A vsql_trgm converte a entrada para minúsculas, divide-a nos caracteres não alfanuméricos, preenche cada palavra com dois espaços à esquerda e um espaço à direita e, em seguida, desliza uma janela de 3 caracteres por cada palavra preenchida. A similaridade é o número de trigramas que as duas entradas compartilham, dividido pelo número de trigramas distintos entre ambas — o tamanho da união, não o tamanho do conjunto maior.
Duas coisas a saber sobre strings com várias palavras: elas são convertidas para minúsculas antes do processamento, portanto "Cat" e "cat" produzem os mesmos trigramas. Elas também são divididas nos caracteres não alfanuméricos, portanto cada palavra é preenchida e dividida em trigramas de forma independente — "hello world" produz trigramas a partir de " hello " e " world " separadamente, não a partir de uma única string preenchida. Não existe o trigrama "o w" nem " wo". A similaridade varia de 0 (nada em comum) a 1 (idênticas).

Primeiros passos

Funções principais

trgm_similarity — pontuação contínua

Todas as funções retornam NULL quando qualquer argumento é NULL.

trgm_similar — verificação de limiar

Retorna 1 se a similaridade for igual ou superior a 0.3 (o padrão do PostgreSQL), e 0 caso contrário:
Para um limiar personalizado:

trgm_distance — complemento da similaridade

1 - similaridade. Útil em ORDER BY quando você quer os mais próximos primeiro:

Encontrando correspondências aproximadas em uma tabela

O uso mais comum: encontrar linhas que se assemelham a um termo de busca.
Isso lida com erros de digitação, grafias alternativas e correspondências parciais — casos em que LIKE '%adidas%' falharia.

Similaridade de palavras

A trgm_word_similarity encontra a melhor correspondência entre uma string de consulta e qualquer substring contígua do alvo. Isso é útil quando o termo de busca pode ser uma palavra dentro de uma string mais longa.
A trgm_strict_word_similarity é uma variante mais rigorosa que exige que a correspondência se alinhe com os limites das palavras.

Inspecionando trigramas

A trgm_show retorna o conjunto ordenado de trigramas de uma string como um array JSON:
Isso é útil para depurar por que duas strings recebem a pontuação que recebem.

Referência de funções

Limitações conhecidas

Sem aceleração por índice. Uma capability Preview para tipos de índice personalizados é distribuída com o SDK de extensões, mas o DDL que anexaria um índice é recusado em todas as compilações (ERROR 3219: Extended Index feature not yet implemented), portanto WHERE trgm_similarity(col, 'term') > 0.3 requer uma varredura completa da tabela. Para tabelas grandes, faça uma pré-filtragem com LIKE ou com busca de texto completo para reduzir o conjunto de candidatos e, em seguida, recalcule a pontuação com trgm_similarity. Sem limiar em nível de sessão. As funções set_limit() / show_limit() do PostgreSQL não podem ser implementadas no VEF (as funções do VEF não mantêm estado). Use trgm_similar_threshold(a, b, t) para passar o limiar explicitamente. Sem retorno de array. A trgm_show retorna uma string JSON em vez de um tipo de array nativo.

Veja também