Postgres中LIKE和〜之间的区别

Question

我被指示“不要打扰LIKE”，而要使用~。什么是错的LIKE又是怎样的~不同？~在这种情况下是否有名称，或者人们说“使用波浪号运算符”？

Answer 1

~是正则表达式运算符，并具有其隐含的功能。您可以指定完整的正则表达式通配符和量词；有关详细信息，请参见文档。它肯定比强大LIKE，并且在需要此功能时应使用，但它们具有不同的用途。

Answer 2

LIKEIMO 没有错，也没有理由~对此表示赞同。相反。LIKE是SQL标准的。也是SIMILAR TO，但并未得到广泛支持。PostgreSQL ~ operator（或posix正则表达式匹配运算符）不是SQL标准。

因此，我更喜欢LIKE在表达能力足够的地方使用，并且仅~在需要完整正则表达式的功能时才使用。如果我需要移植数据库，那将是一件痛苦的事。我倾向于使用SIMILAR TOwhen LIKE不够强大的功能，但是在Erwin发表评论后，我想我会停止这样做，而~在LIKE不工作时使用。

此外，PostgreSQL的可以使用前缀搜索（例如B-tree索引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)