Differenza tra LIKE e ~ in Postgres

Sono stato incaricato di “non disturbarmi con LIKE ” e di usare invece ~ . Cosa c’è di sbagliato in LIKE e in che modo ~ è diverso?

~ Ha un nome in questo contesto o la gente dice “usa l’operatore tilde”?

~ è l’operatore di espressioni regolari e ha le capacità implicite in questo. È ansible specificare una gamma completa di caratteri jolly e quantificatori di espressioni regolari; vedere la documentazione per i dettagli. È certamente più potente di LIKE e dovrebbe essere usato quando è necessario quel potere, ma servono a scopi diversi.

Non c’è nulla di sbagliato in LIKE e, IMO, non c’è motivo di favorirlo su di esso. Piuttosto il contrario. LIKE è lo standard SQL. Così è SIMILAR TO , ma non è ampiamente supportato. L’ ~ operator di PostgreSQL (o l’ operatore di corrispondenza delle espressioni regolari di posix ) non è lo standard SQL.

Per questo motivo, preferisco usare LIKE dove è abbastanza espressivo e uso solo ~ quando ho bisogno del potere delle espressioni regolari complete. Se mai avessi bisogno di effettuare il porting dei database è una cosa in meno che farà male. Ho avuto la tendenza ad usare SIMILAR TO quando LIKE non è abbastanza potente, ma dopo i commenti di Erwin penso che smetterò di farlo e useremo ~ quando LIKE non fa il lavoro.

Inoltre, PostgreSQL può utilizzare un indice b-tree per le ricerche di prefissi (ad esempio LIKE 'TEST%' ) con LIKE o SIMILAR TO se il database si trova nella locale C o l’indice ha text_pattern_ops . Contrariamente a quanto ho scritto in precedenza, Pg può anche usare un tale indice per una regex di posix ancorata a sinistra, ha solo bisogno di un esplicito ‘^ TEST. *’ In modo che la regex possa corrispondere solo dall’inizio. Il mio post precedente affermava erroneamente che ~ non poteva usare un indice per una ricerca prefisso. Con questa differenza eliminata, è davvero necessario decidere se si desidera attenersi alle funzionalità conformi agli standard, laddove ansible o meno.

Guarda questa demo SQLFiddle ; prendere nota dei diversi piani di esecuzione. Notare la differenza tra ~ '1234.*' e ~ '^1234.*' .

Dati di esempio forniti:

 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); 

nota che ~ usa un seqscan anche quando è sostanzialmente più costoso (artificialmente così dovuto a enable_seqscan ) perché non ha alternative, mentre LIKE usa l’indice. Tuttavia, un ~ corretto con un’ancora a sinistra usa anche l’indice:

 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) 

Esistono numerosi operatori di corrispondenza dei modelli in PostgreSQL . LIKE , SIMILAR TO e ~ sono trattati in questa pagina di manuale.

Se puoi, usa LIKE ( ~~ ), è il più veloce.
Se non puoi, usa un’espressione regolare ( ~ ), è più potente.
Mai utente SIMILAR TO È assolutamente inutile. Più in basso.

L’installazione del modulo aggiuntivo pg_trgm rende inoltre disponibile l’ operatore di similarità % .

Per rendere l’immagine completa, c’è anche la ricerca di testo con la propria infrastruttura.

Il supporto dell’indice è disponibile per ciascuno di questi operatori – a vari livelli. Vince regolarmente le prestazioni di altre opzioni. Ma c’è un sacco di margine nei dettagli, anche con gli indici.

Senza pg_trgm, esiste un supporto per gli indici per i modelli di ricerca ancorati a sinistra . Se il cluster del database viene eseguito con una locale non “C”, è necessario un indice con una class operatore speciale , come text_pattern_ops o varchar_pattern_ops . E sì, sono supportate anche le espressioni regolari di base ancorate a sinistra . Esempio:

 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 

SQL Fiddle.

Con pg_trgm installato, hai anche la possibilità di utilizzare indici GIN o GiST con le classi operatore gist_trgm_ops o gin_trgm_ops . Questi indici possono supportare qualsiasi espressione LIKE , non solo lasciata ancorato. Tuttavia, non esiste ancora il supporto per le espressioni regolari (diverse dalle basi di ancoraggio a sinistra). Alexander Korotkov e altri ci stanno lavorando:

Aggiornamento : il supporto per le regex arbitrarie è stato aggiunto in Postgres 9.3 e migliorato più volte da allora.

SIMILAR TO fa parte dello standard SQL, ma è una syntax molto strana, e l’unica ragione per cui PostgreSQL lo supporta è mantenere lo standard conforms. Internamente, ogni espressione SIMILAR TO viene riscritta con un’espressione regolare. Pertanto, per qualsiasi espressione SIMILAR TO , esiste almeno un’espressione regolare che esegue lo stesso lavoro più velocemente . Non uso mai SIMILAR TO Di Più:

  • Pattern matching con LIKE, SIMILAR TO o espressioni regolari in PostgreSQL

L’operatore ~~ equivale a LIKE . ~ , d’altra parte, corrisponderà usando un’espressione regolare POSIX .

Ho appena fatto un semplice e veloce benchmark per esaminare la differenza di prestazioni tra i due operatori quando non sono coinvolti indici :

 postgres=# \timing Timing is on. postgres=# SELECT count(1) FROM (SELECT val from generate_series(1, 10000000) x(val) WHERE val::text LIKE '%5%') AS x; count ───────── 5217031 (1 row) Time: 5631.662 ms postgres=# SELECT count(1) FROM (SELECT val from generate_series(1, 10000000) x(val) WHERE val::text ~ '5') AS x; count ───────── 5217031 (1 row) Time: 10612.406 ms 

In questo esempio l’operatore LIKE è quasi due volte più veloce dell’operatore ~ . Quindi, se la velocità è essenziale, mi sporgerei verso LIKE , anche se stai attento a non ottimizzarlo prematuramente. ~ ti dà molta più flessibilità.

Per quelli di voi che sono interessati, ecco i piani di EXPLAIN per le domande di cui sopra:

 postgres=# EXPLAIN ANALYZE SELECT count(1) FROM (SELECT val from generate_series(1, 10000000) x(val) WHERE val::text LIKE '%5%') AS x; QUERY PLAN ────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────── Aggregate (cost=20.00..20.01 rows=1 width=0) (actual time=9967.748..9967.749 rows=1 loops=1) -> Function Scan on generate_series x (cost=0.00..17.50 rows=1000 width=0) (actual time=1732.084..7404.755 rows=5217031 loops=1) Filter: ((val)::text ~~ '%5%'::text) Rows Removed by Filter: 4782969 Total runtime: 9997.587 ms (5 rows) postgres=# EXPLAIN ANALYZE SELECT count(1) FROM (SELECT val from generate_series(1, 10000000) x(val) WHERE val::text ~ '5') AS x; QUERY PLAN ─────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────── Aggregate (cost=20.00..20.01 rows=1 width=0) (actual time=15118.061..15118.061 rows=1 loops=1) -> Function Scan on generate_series x (cost=0.00..17.50 rows=1000 width=0) (actual time=1724.591..12516.996 rows=5217031 loops=1) Filter: ((val)::text ~ '5'::text) Rows Removed by Filter: 4782969 Total runtime: 15147.950 ms (5 rows) 

Sì, sta per regex POSIX. Un’altra alternativa consiste nell’utilizzare l’approccio standard SQL alle espressioni regolari con l’operatore “SIMILE A”, sebbene fornisca un insieme più limitato di funzioni, potrebbe essere più facile da capire. Penso che questo sia un buon riferimento dallo scambio dba: https://dba.stackexchange.com/questions/10694/pattern-matching-with-like-similar-to-or-regular-expressions-in-postgresql

Come è giusto abbinare una parte della stringa all’inizio o alla fine o Middle And tilt (~) corrisponde alla regex

Per spiegarlo ulteriormente, creiamo una tabella e inseriamo alcuni valori

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

Ora inseriamo alcuni valori nella tabella

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

Ora il tuo tavolo dovrebbe assomigliare a questo

  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 

Caso LIKE

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

Come puoi vedere 'A%' fornirà solo i valori il cui nome inizia con il capitale A.

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

Come puoi vedere '%a%' ci otterrà solo i valori il cui nome ha a in mezzo al nome.

 # select * from users where name like '%a'; id | name ----+------ 4 | Arya 

Come puoi vedere '%a' fornirà solo i valori il cui nome termina con a .

Caso ~ (inclinazione)

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

Come puoi vedere il name ~* 't' ci otterrà solo i valori il cui nome ha t . ~ significa distinzione tra maiuscole e minuscole e ~ * indica maiuscole e minuscole

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

la query precedente ci ha fornito 0 righe poiché T non corrispondeva a nessuna voce

Consideriamo ora un caso in cui abbiamo solo bisogno di recuperare gli id ​​di e-mail e non sappiamo quali siano gli ID di posta, ma conosciamo il modello di e-mail, cioè ci sarà qualche lettera o numero o _ o. oppure – e poi @ e poi qualche altra lettera o numero o – quindi. quindi com o in or org etc e possiamo creare il pattern usando l’espressione regolare.

proviamo ora a recuperare i risultati usando l’espressione regolare

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

Allo stesso modo possiamo recuperare alcuni nomi che hanno uno spazio in mezzo

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

[az] + significa che può esserci qualsiasi lettera dalla a alla z e + significa che potrebbe verificarsi 1 o più volte e \ s significa che dopo ci sarà uno spazio tra e quindi di nuovo una serie di lettere che possono verificarsi 1 o più volte.

Spero che questa analisi dettagliata aiuti.