在PostgreSQL中，使用POSIX正则表达式相比于使用LIKE和ILIKE有什么优势？

Question

我正在构建SQL查询，想知道使用posix正则表达式（如~，~*，！~，！~*）或LIKE和ILIKE对这些查询的性能有何影响。使用这些中的任何一种是否会影响SQL查询的速度和性能？如果是，又是如何影响的？哪种更适合使用？

- john igneel

Answer 1

正则表达式/like运算符需要在数据库中发生某些事情，因此它们肯定会以某种方式影响性能...但它们都有一个特定的工作。 LIKE提供了一个简单的语法, 但没有太多的功能。根据另一个SO答案，LIKE功能非常专业化，因此与等效的正则表达式相比可能更具性能优势。

哪个更适合使用？

并非所有文本都可以通过LIKE进行匹配，因此在这些情况下，您将不得不使用正则表达式。但是，如果LIKE已经足够，那么链接的答案建议使用它会更好。

如果您关心特定查询，请使用postgres的EXPLAIN ANALYZE查看postgres实际要执行什么操作。

- beerbajay

Answer 2

根据我的研究，POSIX正则表达式比使用LIKE和ILIKE子句更适用，因为具有以下一些优点：

1. 速度
2. 简单的子句查询

以下是一些posix正则表达式用法的示例：

〜波浪号表示区分大小写

POSIX: SELECT record FROM mytable WHERE record ~ 'a';
LIKEi: SELECT record FROM mytable WHERE record like '%a%'; 

~* 表示不区分大小写

POSIX: SELECT record FROM mytable WHERE record ~* 'a';
LIKEi: SELECT record FROM mytable WHERE record ilike '%A%';

!~ 排除/不包含（区分大小写）

POSIX: SELECT record FROM mytable WHERE record !~ 'a';
LIKEi: SELECT record FROM mytable WHERE record not like '%a%';

!~* 排除/不包含（不区分大小写）

POSIX: SELECT record FROM mytable WHERE record !~ 'a';
LIKEi: SELECT record FROM mytable WHERE record not ilike '%a%';

可以在这里找到关于 POSIX 正则表达式、LIKE 和 ILIKE 的其他用法。

- john igneel

Answer 3

在Ohn Igneel的例子基础上进行变化

create temporary table test(
c1 varchar
);
insert into test values ('Andrea'), ('Luis'), ('Thomas'), ('Maria');

-- ~ tilde for case sensitive
SELECT c1 from test WHERE c1 ~ 'A';
SELECT c1 from test WHERE c1 like '%A%'; 
|c1    |
|------|
|Andrea|
-- ~* for case insensitive
SELECT c1 from test WHERE c1 ~* 'a';
SELECT c1 from test WHERE c1 ilike '%A%';
|c1    |
|------|
|Andrea|
|Thomas|
|Maria |
-- !~ exclude/not (case sensitive)
SELECT c1 from test WHERE c1 !~ 'm';
SELECT c1 from test WHERE c1 not like '%m%';
|c1    |
|------|
|Andrea|
|Luis  |
|Maria |
-- !~* exclude/not (case insensitive)
SELECT c1 from test WHERE c1 !~* 'M';
SELECT c1 from test WHERE c1 not ilike '%M%';
|c1    |
|------|
|Andrea|
|Luis  | 

- Luis