我被告知“不要使用LIKE
”,而应该使用~
。那么LIKE
有什么问题,~
又有何不同之处?
在这个语境中,~
有一个名称吗?还是人们会说“使用波浪线运算符”?
我被告知“不要使用LIKE
”,而应该使用~
。那么LIKE
有什么问题,~
又有何不同之处?
在这个语境中,~
有一个名称吗?还是人们会说“使用波浪线运算符”?
~
是正则表达式运算符,具有相关的功能。您可以指定一整套正则表达式通配符和量词,详情请参阅文档。它确实比LIKE
更强大,并且应该在需要该功能时使用,但它们有不同的用途。
LIKE
时,通常优先考虑使用它而不是正则表达式。它更简单、更快速,使用合适的索引也更容易。正则表达式更强大,但速度较慢且非标准化。 - Erwin BrandstetterLIKE
、SIMILAR TO
和~
是PostgreSQL中的基本模式匹配操作符。
如果可以的话,请使用LIKE
(~~
),它是最快且最简单的。
如果不能使用,可以使用正则表达式(~
),它更强大。
千万不要使用,这没有意义。请参考下面的说明。SIMILAR TO
%
。@@
运算符(以及其他)。没有pg_trgm,只有对于左锚定搜索模式的索引支持。如果您的数据库集群使用非C语言环境(典型情况),您需要一个具有特殊操作符类的索引例如text_pattern_ops
或varchar_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
db<>fiddle here
安装 pg_trgm 后,可以使用操作符类 gist_trgm_ops
或 gin_trgm_ops
创建 GIN 或 GiST 索引。这些索引支持 任何 LIKE
表达式,而不仅仅是左锚定的表达式。并且,引用手册:
从 PostgreSQL 9.3 开始,这些索引类型还支持正则表达式匹配的索引搜索。
详细信息:
SIMILAR TO
是一个非常奇怪的结构。PostgreSQL只实现它是因为它在早期版本的SQL标准中被定义出来。在内部,每个SIMILAR TO
表达式都会被重写为一个正则表达式。因此,对于任何给定的SIMILAR TO
表达式,至少有一个正则表达式可以更快地完成相同的工作。我从来不使用SIMILAR TO
。
进一步阅读:
SIMILAR TO
,改用 REGEXP_LIKE
。但我不确定使用 SIMILAR TO
比起使用 ~
带来的可移植性是否值得,因为其他 DBMS(Oracle、SQL Server、MySQL)并不支持该标准。 - David Faber使用LIKE
没有任何问题,而且在我看来,没有理由支持~
。相反,情况恰好相反。LIKE
是SQL标准的。 SIMILAR TO
也是如此,但它没有得到广泛支持。 PostgreSQL的〜运算符
(或 posix正则表达式匹配运算符)不是SQL标准。
因此,我更喜欢在表达能力足够时使用LIKE
,并且仅在需要完整正则表达式功能时才使用~
。 如果我需要移植数据库,则会少一个问题。 当LIKE
不够强大时,我倾向于使用SIMILAR TO
,但在Erwin的评论之后,我认为我会停止这样做,并在LIKE
无法胜任时改用~
。
此外,如果数据库处于C
语言环境中或索引具有text_pattern_ops
,则PostgreSQL可以使用基于前缀的搜索(例如LIKE 'TEST%'
)与LIKE
或SIMILAR TO
使用b-tree索引。 与我先前所写的相反,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);
请注意,即使使用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 只是匹配字符串的一部分,可以在开头、结尾或中间进行匹配。
Tilde(~)是使用正则表达式进行匹配。
为了进一步解释这个概念,让我们创建一个表并插入一些值。
# create table users(id serial primary key, name character varying);
# insert into users (name) VALUES ('Alex'), ('Jon Snow'), ('Christopher'), ('Arya'),('Sandip Debnath'), ('Lakshmi'),('alex@gmail.com'),('@sandip5004'), ('lakshmi@gmail.com');
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
# select * from users where name like 'A%';
id | name
----+------
1 | Alex
4 | Arya
(2 rows)
正如您所看到的,'A%'
只会返回名称以大写字母 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
如您所见,'%a%'
只会得到名称中包含a
的值。
# select * from users where name like '%a';
id | name
----+------
4 | Arya
正如您所看到的,'%a'
只会获取名称以 a
结尾的值。
# select * from users where name ~* 't';
id | name
----+----------------
3 | Christopher
5 | Sandip Debnath
正如您所看到的,name ~* 't'
只会获取名称中有 t
的值。~
表示区分大小写,而 ~* 则表示不区分大小写。
# select * from users where name ~ 'T';
id | name
----+------
(0 rows)
以上查询结果为0行,因为 T
与任何条目都不匹配。
现在考虑一种情况,我们只需要获取电子邮件地址,但我们不知道邮件地址是什么,但我们知道电子邮件的模式,即会有一些字母或数字或_或.或-,然后是@,然后是一些字母或数字或-,然后是.,然后是com
、in
、org
等等,我们可以使用正则表达式创建该模式。
现在让我们尝试使用正则表达式获取结果。
# 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
#select * from users where name ~* '[a-z]+\s[a-z]+';
id | name
----+----------------
2 | Jon Snow
5 | Sandip Debnath
[a-z]+表示可以是从a到z的任何字母,+表示它可能出现1次或多次,\s表示在此之后会有一个空格,然后再次是一组可以出现1次或多次的字母。
希望这个详细的分析能够帮助您。
~~
运算符等同于LIKE
。 另一方面,~
会使用POSIX正则表达式进行匹配。
LIKE
那么容易搜索,例如我必须在谷歌上输入 tilde tilde
才能找到这里。 - Dorian我刚刚进行了一个简单的基准测试,以查看两个运算符之间的性能差异当没有涉及索引时:
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
LIKE
运算符的速度几乎是~
运算符的两倍。因此,如果速度至关重要,我会倾向于使用LIKE
,但要小心不要过早优化。~
给你更多的灵活性。EXPLAIN
计划: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)