Postgres에서 LIKE와 ~의 차이점
나는 "귀찮게하지 말고 LIKE" ~대신 사용 하라는 지시를 받았습니다 . 무엇이 잘못 LIKE되었으며 어떻게 ~다른가요?
~이 문맥에서 이름이 있습니까? 아니면 사람들이 "물결 연산자 사용"이라고 말합니까?
~정규식 연산자이며 그에 의해 암시되는 기능을 가지고 있습니다. 전체 범위의 정규식 와일드 카드 및 수량자를 지정할 수 있습니다. 자세한 내용 은 설명서 를 참조하십시오. 확실히보다 강력하며 LIKE그 힘이 필요할 때 사용해야하지만 다른 용도로 사용됩니다.
LIKEIMO 에는 아무런 문제가 없으며 ~그것을 선호 할 이유가 없습니다 . 오히려 그 반대입니다. LIKESQL 표준입니다. 도 마찬가지 SIMILAR TO지만 널리 지원되지는 않습니다. PostgreSQL ~ operator(또는 posix 정규 표현식 일치 연산자 )은 SQL 표준이 아닙니다.
그렇기 때문에 나는 LIKE충분히 표현력이있는 곳 에서 사용하는 것을 선호하고 ~완전한 정규식의 힘이 필요할 때만 사용 합니다. 데이터베이스를 이식해야한다면 상처를 입을 일이 하나 적습니다. 나는 충분히 강력하지 않을 SIMILAR TO때 사용하는 경향이 LIKE있지만 Erwin의 의견 후에 나는 그 일을 그만두고 일을 하지 않을 ~때 사용할 것이라고 생각 LIKE합니다.
또한, PostgreSQL는 접두사 검색 (예를 들면위한 B- 트리 인덱스를 사용할 수 LIKE 'TEST%'있음) LIKE또는 SIMILAR TO데이터베이스가있는 경우 C로케일 또는 인덱스가 있습니다을 text_pattern_ops. 이전에 작성한 것과 달리 Pg는 왼쪽 고정 posix 정규식에 대해 이러한 인덱스를 사용할 수도 있습니다. 명시 적 '^ TEST. *'만 있으면 정규식이 처음부터 만 일치 할 수 있습니다. 내 게시물은 이전 ~에 접두사 검색에 색인을 사용할 수 없다고 잘못 설명했습니다 . 이러한 차이가 없어지면 가능한 경우 표준 준수 기능을 고수할지 여부가 결정됩니다.
이 데모 보기 SQLFiddle ; 다른 실행 계획에 유의하십시오. ~ '1234.*'과 의 차이점에 유의하십시오 ~ '^1234.*'.
주어진 샘플 데이터 :
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);
참고 ~이 (인해 너무 인위적 실질적으로 더 비싼에도 때 seqscan를 사용하는 enable_seqscan것이 대안이 없기 때문에 잠시,) LIKE사용하는 인덱스입니다. 그러나 ~왼쪽 앵커 로 수정 된 경우에도 색인이 사용됩니다.
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)
개요
LIKE, SIMILAR TO및 ~기본이다 PostgreSQL의 패턴에 일치하는 사업자 .
가능하면 LIKE( ~~)를 사용하면 가장 빠르고 간단합니다.
할 수 없다면 정규 표현식 ( ~)을 사용하면 더 강력합니다.
절대 사용자
. 무의미합니다. 아래를 참조하십시오.
SIMILAR TO
추가 모듈 pg_trgm을 설치하면 고급 색인 옵션과 유사성 연산자가 추가% 됩니다.
또한 자체 인프라와 운영자 (특히)가 있는 텍스트 검색 이 있습니다 .@@
이러한 각 연산자에 대해 다양한 정도의 인덱스 지원을 사용할 수 있습니다. 정기적으로 다른 옵션의 성능을 능가합니다. 그러나 인덱스를 사용하더라도 세부 사항에 많은 여지가 있습니다.
인덱스 지원
pg_trgm 이 없으면 왼쪽 고정 검색 패턴에대한 인덱스 지원 만있습니다. 데이터베이스 클러스터가 C가 아닌 로케일 (일반적인 경우)로 실행되는 경우또는같은 특수 연산자 클래스 가있는 인덱스 가 필요합니다. 기본 왼쪽 고정 정규식도 이에 의해 지원됩니다. 예:text_pattern_opsvarchar_pattern_ops
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
함께 pg_trgm가 설치 GIN 또는 GIST 인덱스 연산자 클래스를 가능gist_trgm_ops하거나gin_trgm_ops. 이러한 인덱스는 고정 된 왼쪽뿐 아니라 모든 LIKE 표현식을지원 합니다 . 그리고 매뉴얼 인용 :
PostgreSQL 9.3부터 이러한 인덱스 유형은 정규식 일치에 대한 인덱스 검색도 지원합니다.
세부:
SIMILAR TO매우 이상한 구조입니다. PostgreSQL은 SQL 표준의 초기 버전에서 정의 되었기 때문에이를 구현합니다. 내부적으로 모든 SIMILAR TO표현식은 정규 표현식으로 다시 작성됩니다. 따라서 주어진 SIMILAR TO표현식에 대해 동일한 작업을 더 빠르게 수행하는 정규 표현식이 하나 이상 있습니다. 나는 결코 사용하지 SIMILAR TO.
추가 읽기 :
~~오퍼레이터는 동일하다 LIKE. ~반면 에는 POSIX 정규 표현식을 사용하여 일치합니다 .
인덱스가없는 경우 두 연산자 간의 성능 차이를 확인하기 위해 빠르고 간단한 벤치 마크를 수행 했습니다 .
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 this example the LIKE operator is almost twice as fast as the ~ operator. So if speed is of the essence I would lean towards LIKE, though be careful not to optimize prematurely. ~ gives you a lot more flexibility.
For those of you who are interested, here are EXPLAIN plans for the above queries:
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)
Yes, it stands for POSIX regex. Another alternative is to use the SQL standard approach to regular expressions with the "SIMILAR TO" operator,though it provides a more limited set of features,might be easier to understand. I think this is a good reference from dba exchange: https://dba.stackexchange.com/questions/10694/pattern-matching-with-like-similar-to-or-regular-expressions-in-postgresql
Like is just matching a part of the string either at the beginning or End or Middle And tilt (~) is matching with regex
To explain this further let's create a table and insert some values
# create table users(id serial primary key, name character varying);
Now let's insert some values in the table
# insert into users (name) VALUES ('Alex'), ('Jon Snow'), ('Christopher'), ('Arya'),('Sandip Debnath'), ('Lakshmi'),('alex@gmail.com'),('@sandip5004'), ('lakshmi@gmail.com');
Now your table should look like this
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
Case LIKE
# select * from users where name like 'A%';
id | name
----+------
1 | Alex
4 | Arya
(2 rows)
As you can see 'A%' will only get us the values whose name starts with capital 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
As you can see '%a%' will only get us the values whose name has a in between the name.
# select * from users where name like '%a';
id | name
----+------
4 | Arya
As you can see '%a' will only get us the values whose name ends with a.
Case ~ (tilt)
# select * from users where name ~* 't';
id | name
----+----------------
3 | Christopher
5 | Sandip Debnath
As you can see name ~* 't' will only get us the values whose name has t. ~ means case sensitive and ~* means case insensitive so
# select * from users where name ~ 'T';
id | name
----+------
(0 rows)
the above query gave us 0 rows as T was not matching with any entries
Now let's consider a case where we only need to fetch the email ids and we don't know what the mail ids have, but we know the pattern of email i.e there will be some letter or number or _ or . or - and then @ and then some more letter or number or - then . then com or in or org etc and we can create the pattern using regular expression.
now let's try to fetch results using regular expression
# 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
Similarly we can fetch some names which has a space in between
#select * from users where name ~* '[a-z]+\s[a-z]+';
id | name
----+----------------
2 | Jon Snow
5 | Sandip Debnath
[a-z]+ means there can be any letter from a to z and + means it might occur 1 or more times and \s means after that there will be a space in between and then again a set of letters which can occur 1 or more times.
Hope this detailed analysis helps.
ReferenceURL : https://stackoverflow.com/questions/12452395/difference-between-like-and-in-postgres
'IT TIP' 카테고리의 다른 글
| Scala 리터럴 식별자 (백틱)에 대한 설명이 필요합니다. (0) | 2021.01.10 |
|---|---|
| 자바 스크립트의 array.sort () 메서드를 확장하여 다른 매개 변수를 수락하는 방법은 무엇입니까? (0) | 2021.01.10 |
| intent.setType (type)의 가능한 인 텐트 유형은 무엇입니까? (0) | 2021.01.10 |
| 이미지 버튼이 프로그래밍 방식으로 변경됩니까? (0) | 2021.01.10 |
| 속성 변경을 '감시'할 수 있습니까? (0) | 2021.01.10 |