Postgres中LIKE和~的区别

Question

我被告知“不要使用LIKE”，而应该使用~。那么LIKE有什么问题，~又有何不同之处？

在这个语境中，~有一个名称吗？还是人们会说“使用波浪线运算符”？

- Celeritas

---

由谁指导的，出于兴趣吗？我很好奇。 - Craig Ringer

@CraigRinger 课程讲师。 - Celeritas

明白了，谢谢。我只是想问“在哪里”而不是“谁”，抱歉。 - Craig Ringer

愚蠢的建议。 ~ 是一个正则表达式运算符，LIKE 不是。 ~ 更强大，但 LIKE 的性能（显着）更高。 - Shayne

Answer 1

~是正则表达式运算符，具有相关的功能。您可以指定一整套正则表达式通配符和量词，详情请参阅文档。它确实比LIKE更强大，并且应该在需要该功能时使用，但它们有不同的用途。

- asthasr

---

这是一个很糟糕的建议。每当可以使用SQL标准的LIKE时，通常优先考虑使用它而不是正则表达式。它更简单、更快速，使用合适的索引也更容易。正则表达式更强大，但速度较慢且非标准化。 - Erwin Brandstetter

这就是为什么我说，“在适当的时候使用，但它们有不同的用途。” - asthasr

如果可以选择，就应该优先使用 LIKE 操作符，而不是“应该优先使用 IN 而非 LIKE 操作符”，这是错误的表述。当你有选择余地时，请使用 LIKE 操作符。当你没有选择时，无论如何都没有选择。 - Erwin Brandstetter

你看过有人试图把模式匹配嵌入到使用多个LIKE子句的查询中吗？这在其他某些系统中是必要的，但Postgres提供了正则表达式运算符。也许我应该说：“应该优先使用正则表达式运算符，而不是多个LIKE子句。” - asthasr

由于这个答案不再让我感到错误，因此取消了我的负评。 - Erwin Brandstetter

Answer 2

概览

LIKE、SIMILAR TO和~是PostgreSQL中的基本模式匹配操作符。

如果可以的话，请使用LIKE（~~），它是最快且最简单的。
如果不能使用，可以使用正则表达式（~），它更强大。
千万不要使用SIMILAR TO，这没有意义。请参考下面的说明。

安装附加模块pg_trgm会添加高级索引选项和相似性运算符%。
还有文本搜索及其自己的基础设施和@@运算符（以及其他）。

这些运算符都可以获得索引支持，但程度各异。它们通常比其他选项的性能更出色。但即使在索引方面，也有很大的灵活性。

索引支持

没有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 开始，这些索引类型还支持正则表达式匹配的索引搜索。

详细信息：

• PostgreSQL LIKE 查询性能变化

---

SIMILAR TO是一个非常奇怪的结构。PostgreSQL只实现它是因为它在早期版本的SQL标准中被定义出来。在内部，每个SIMILAR TO表达式都会被重写为一个正则表达式。因此，对于任何给定的SIMILAR TO表达式，至少有一个正则表达式可以更快地完成相同的工作。我从来不使用SIMILAR TO。

进一步阅读：

• 使用LIKE、SIMILAR TO或正则表达式进行模式匹配

- Erwin Brandstetter

---

最近我读到，下一个 ANSI SQL 标准版本可能会取消 SIMILAR TO，改用 REGEXP_LIKE。但我不确定使用 SIMILAR TO 比起使用 ~ 带来的可移植性是否值得，因为其他 DBMS（Oracle、SQL Server、MySQL）并不支持该标准。 - David Faber

@DavidFaber：我完全同意。 - Erwin Brandstetter

Answer 3

使用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)

- Craig Ringer

---

就个人而言，我不同意“SIMILAR TO”的使用。我在我的答案中写了更多相关内容。此外，值得一提的是，即使对于正则表达式运算符“~”，也支持非常常见的左锚定搜索模式。 - Erwin Brandstetter

@ErwinBrandstetter 啊，我忘记把它向左锚定了。我会更正我的帖子。 - Craig Ringer

Answer 4

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'),('[e-mail]'),('@sandip5004'), ('[e-mail]');

现在你的表格应该看起来像这样。

 id |       name        
----+-------------------
  1 | Alex
  2 | Jon Snow
  3 | Christopher
  4 | Arya
  5 | Sandip Debnath
  6 | Lakshmi
  7 | [e-mail]
  8 | [e-mail]
  9 | @sandip5004

Case LIKE

# 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 | [e-mail]
  8 | [e-mail]

如您所见，'%a%'只会得到名称中包含a的值。

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

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

正如您所看到的，'%a' 只会获取名称以 a 结尾的值。

Case ~（倾斜）

# 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 | [e-mail]
  8 | [e-mail]

同样地，我们可以获取一些在名称中间带有空格的名字。

#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次或多次的字母。

希望这个详细的分析能够帮助您。

- Sandip Debnath

Answer 5

~~运算符等同于LIKE。 另一方面，~会使用POSIX正则表达式进行匹配。

- user1675187

---

只是个人意见（因此是评论），但我不喜欢“~~”运算符，因为它只适用于Postgres，并且没有真正的好处会失去SQL的可移植性。 - asthasr

抱歉可能我表达不太清楚，我完全同意你的观点。我的意思是，波浪号（ ~ ）和 LIKE 并不具有相同的功能。 - user1675187

@syrion 而且它不像 LIKE 那么容易搜索，例如我必须在谷歌上输入 tilde tilde 才能找到这里。 - Dorian

Answer 6

我刚刚进行了一个简单的基准测试，以查看两个运算符之间的性能差异当没有涉及索引时：

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)

- Alex

---

哇，这是一个巨大的差异。很棒的例子！ - Jordan Parmer

你能提供执行计划以查看Delta的解释是否与Craig Ringer上面展示的解释一致吗？ - eharik

很好的建议，@eharik。已完成。 - Alex

所以，对于像你在这里呈现的无索引测试数据集，你实际上只是确认了Erwin所说的。也就是说，~~比~运算符更快。 - eharik

Answer 7

是的，它代表POSIX正则表达式。另一种选择是使用SQL标准方法中的“SIMILAR TO”运算符进行正则表达式匹配，尽管它提供了更有限的功能集，但可能更易于理解。 我认为这是来自dba exchange的一个很好的参考： https://dba.stackexchange.com/questions/10694/pattern-matching-with-like-similar-to-or-regular-expressions-in-postgresql

- Rene Romero Benavides