Différence entre LIKE et ~ dans Postgres

Question

On m'a dit de "ne pas m'embêter avec". LIKE " et utiliser ~ à la place. Quel est le problème avec LIKE et comment ~ différent ?

Fait ~ a un nom dans ce contexte ou les gens disent-ils "u

Answer 1

~ est l'opérateur d'expression régulière, et possède les capacités impliquées par celui-ci. Vous pouvez spécifier une gamme complète de caractères génériques et de quantificateurs d'expression régulière ; voir la documentation pour les détails. Il est certainement plus puissant que LIKE et doivent être utilisés lorsque cette puissance est nécessaire, mais ils servent des objectifs différents.

Answer 2

Vue d'ensemble

LIKE , SIMILAR TO et ~ sont les éléments de base opérateurs de filtrage dans PostgreSQL .

Si vous le pouvez, utilisez LIKE ( ~~ ), c'est le plus rapide et le plus simple.
Si vous ne le pouvez pas, utilisez une expression régulière ( ~ ), il est plus puissant.
Jamais utilisateur SIMILAR TO . C'est sans intérêt. Voir ci-dessous.

Installation du module supplémentaire pg_trgm ajoute des options d'indexation avancées et le opérateur de similarité % .
Et il y a aussi recherche de texte avec sa propre infrastructure et le @@ opérateur (entre autres).

Le soutien indiciel est disponible pour chacun de ces opérateurs - à un degré variable. Il surpasse régulièrement les performances des autres options. Mais il y a beaucoup de marge de manœuvre dans les détails, même avec les index.

Support d'index

Sans pg_trgm il n'y a qu'un support d'index pour ancré à gauche les modèles de recherche. Si votre cluster de base de données fonctionne avec une locale non-C (cas typique), vous avez besoin d'un index avec une classe d'opérateur spéciale pour cela, comme text_pattern_ops o varchar_pattern_ops . Les expressions régulières de base ancrées à gauche sont également prises en charge par ce système. Exemple :

CREATE TABLE tbl(string text);

INSERT INTO  tbl(string)
SELECT x::text FROM generate_series(1, 10000) x;

CREATE INDEX tbl_string_text_pattern_idx ON tbl(string text_pattern_ops);

SELECT * FROM tbl WHERE string ~ '^1234';  -- left anchored pattern

_db<>fidèle aquí_

Avec pg_trgm installés, les index GIN ou GiST sont possibles avec les classes d'opérateurs gist_trgm_ops o gin_trgm_ops . Ces index prennent en charge tout LIKE expression, et non pas simplement laissée ancrée. Et, en citant le manuel :

À partir de PostgreSQL 9.3, ces types d'index supportent également les recherches d'index pour les correspondances par expression régulière.

Détails :

• Variations des performances des requêtes LIKE de PostgreSQL

---

SIMILAR TO est une construction très étrange. PostgreSQL ne l'implémente que parce qu'elle a été définie dans les premières versions du standard SQL. En interne, chaque SIMILAR TO est réécrite avec une expression régulière. Par conséquent, pour toute SIMILAR TO il y a au moins une expression régulière qui fait le même travail. plus rapide . I jamais utiliser SIMILAR TO .

Pour en savoir plus :

• Correspondance de motifs avec LIKE, SIMILAR TO ou expressions régulières dans PostgreSQL

Answer 3

Il n'y a rien de mal à LIKE et, IMO, aucune raison de favoriser ~ sur elle. C'est plutôt le contraire. LIKE est conforme à la norme SQL. Il en est de même pour SIMILAR TO mais il n'est pas largement supporté. L'interface de PostgreSQL ~ operator (ou opérateur de correspondance d'expression régulière posix ) n'est pas la norme SQL.

Pour cette raison, je préfère utiliser LIKE où c'est assez expressif et j'utilise seulement ~ quand j'ai besoin de la puissance des expressions régulières complètes. Si j'ai un jour besoin de porter des bases de données, c'est une chose de moins qui fera mal. J'ai eu tendance à utiliser SIMILAR TO quand LIKE n'est pas assez puissant, mais après les commentaires d'Erwin, je pense que je vais arrêter de le faire et utiliser ~ quand LIKE ne fait pas l'affaire.

De même, PostgreSQL peut utiliser un index b-tree pour les recherches de préfixes (ex. LIKE 'TEST%' ) avec LIKE o SIMILAR TO si la base de données est dans le C ou l'index a text_pattern_ops . C ~ ne pouvait pas utiliser un index pour une recherche par préfixe. Une fois cette différence éliminée, c'est à vous de décider si vous voulez vous en tenir à des fonctionnalités conformes aux normes lorsque cela est possible ou non.

Voir cette démo SQLFiddle ; notez les différents plans d'exécution. Notez la différence entre ~ '1234.*' et ~ '^1234.*' .

Compte tenu des données de l'échantillon :

create table test (
   blah text
);
insert into test (blah)  select x::text from generate_series(1,10000) x;
create index test_blah_txtpat_idx ON test(blah text_pattern_ops);

notez que ~ utilise un seqscan même s'il est nettement plus coûteux (artificiellement en raison de l'utilisation d'un seqscan). enable_seqscan ) parce qu'il n'a pas d'alternative, alors que LIKE utilise l'index. Cependant, un indice corrigé ~ avec une ancre gauche utilise aussi l'index :

regress=# SET enable_seqscan = 'f';
SET
regress=# explain select 1 from test where blah ~ '12.*';
                                QUERY PLAN                                 
---------------------------------------------------------------------------
 Seq Scan on test  (cost=10000000000.00..10000000118.69 rows=2122 width=0)
   Filter: (blah ~ '12.*'::text)
(2 rows)
regress=# explain select 1 from test where blah like '12%';
                                     QUERY PLAN                                     
------------------------------------------------------------------------------------
 Bitmap Heap Scan on test  (cost=4.55..46.76 rows=29 width=0)
   Filter: (blah ~~ '12%'::text)
   ->  Bitmap Index Scan on test_blah_txtpat_idx  (cost=0.00..4.54 rows=29 width=0)
         Index Cond: ((blah ~>=~ '12'::text) AND (blah ~<~ '13'::text))
(4 rows)
regress=# explain select 1 from test where blah ~ '^12.*';
                                     QUERY PLAN                                      
-------------------------------------------------------------------------------------
 Bitmap Heap Scan on test  (cost=5.28..51.53 rows=101 width=0)
   Filter: (blah ~ '^12.*'::text)
   ->  Bitmap Index Scan on test_blah_txtpat_idx  (cost=0.00..5.25 rows=100 width=0)
         Index Cond: ((blah ~>=~ '12'::text) AND (blah ~<~ '13'::text))
(4 rows)

Answer 4

Like correspond simplement à une partie de la chaîne, soit au début, soit à la fin, soit au milieu. Et le basculement (~) correspond à l'utilisation d'une expression rationnelle.

Pour expliquer cela plus en détail, créons un tableau et insérons quelques valeurs.

# create table users(id serial primary key, name character varying);

Maintenant, insérons quelques valeurs dans la table

# insert into users (name) VALUES ('Alex'), ('Jon Snow'), ('Christopher'), ('Arya'),('Sandip Debnath'), ('Lakshmi'),('alex@gmail.com'),('@sandip5004'), ('lakshmi@gmail.com');

Votre tableau devrait maintenant ressembler à ceci

 id |       name        
----+-------------------
  1 | Alex
  2 | Jon Snow
  3 | Christopher
  4 | Arya
  5 | Sandip Debnath
  6 | Lakshmi
  7 | alex@gmail.com
  8 | lakshmi@gmail.com
  9 | @sandip5004

Cas LIKE

# select * from users where name like 'A%';
 id | name 
----+------
  1 | Alex
  4 | Arya
(2 rows)

Comme vous pouvez le constater 'A%' ne nous donnera que les valeurs dont le nom commence par un A majuscule.

# select * from users where name like '%a%';
 id |       name        
----+-------------------
  4 | Arya
  5 | Sandip Debnath
  6 | Lakshmi
  7 | alex@gmail.com
  8 | lakshmi@gmail.com

Comme vous pouvez le constater '%a%' ne nous donnera que les valeurs dont le nom a a entre le nom.

# select * from users where name like '%a';

 id | name 
----+------
  4 | Arya

Comme vous pouvez le constater '%a' ne nous donnera que les valeurs dont le nom se termine par a .

Cas ~ (inclinaison)

# select * from users where name ~* 't';
 id |      name      
----+----------------
  3 | Christopher
  5 | Sandip Debnath

Comme vous pouvez le constater name ~* 't' ne nous donnera que les valeurs dont le nom a t . ~ signifie sensible à la casse et ~* signifie insensible à la casse donc

# select * from users where name ~ 'T';
 id | name 
----+------
(0 rows)

la requête ci-dessus nous a donné 0 rangs comme T ne correspondait à aucune entrée

Considérons maintenant un cas où nous avons seulement besoin de récupérer les identifiants des emails et nous ne savons pas ce que les identifiants des emails contiennent, mais nous connaissons le modèle de l'email, c'est-à-dire qu'il y aura une lettre ou un chiffre ou _ ou . ou - et ensuite @ et ensuite une autre lettre ou un autre chiffre ou - puis . puis . com o in o org etc et nous pouvons créer le modèle en utilisant une expression régulière.

essayons maintenant de récupérer les résultats en utilisant une expression régulière

# select * from users where name ~* '[a-z0-9\.\-\_]+@[a-z0-9\-]+\.[a-z]{2,5}';
 id |       name        
----+-------------------
  7 | alex@gmail.com
  8 | lakshmi@gmail.com

De même, nous pouvons récupérer des noms qui ont un espace entre eux.

#select * from users where name ~* '[a-z]+\s[a-z]+';
 id |      name      
----+----------------
  2 | Jon Snow
  5 | Sandip Debnath

[a-z]+ signifie qu'il peut y avoir n'importe quelle lettre de a à z et + signifie qu'il peut apparaître 1 ou plusieurs fois et \s signifie qu'après cela, il y aura un espace entre les deux, puis à nouveau une série de lettres qui peuvent apparaître une ou plusieurs fois.

J'espère que cette analyse détaillée vous aidera.

Answer 5

En ~~ est équivalent à LIKE . ~ d'autre part, correspondra en utilisant une expression régulière POSIX .