PostgreSQL: PostgreSQL高级过滤与模式匹配

Q: SIMILAR TO 和 POSIX 正则哪个更好？

SIMILAR TO 是 SQL 标准子集，可移植性好但功能有限。POSIX 正则（~ 操作符）功能完整，推荐在 PostgreSQL 项目中优先使用。

Q: ANY 和 IN 有什么区别？

col = ANY(array) 与 col IN (list) 功能等价，但 ANY 可以接受数组参数和子查询返回的数组，且支持 > ANY、< ALL 等非常规比较，IN 只支持等值。

Q: LIKE '%keyword%' 为什么走不到索引？

前缀通配符 %keyword 使 B-tree 索引失效，因为无法利用索引的排序特性。解决方案：pg_trgm GIN 索引、全文搜索（tsvector + tsquery）或专用搜索引擎。

最后更新：2026-08-26

1. 你将学到

使用 AND/OR/NOT 组合多个过滤条件
掌握 IN/BETWEEN/IS NULL 等范围与空值过滤
使用 LIKE/ILIKE 进行模式匹配（PostgreSQL 大小写不敏感特色）
使用 SIMILAR TO 和正则操作符 ~ / ~* / !~ / !~*
使用 ANY/ALL/EXISTS 子查询过滤

2. 故事

Alice 是电商平台的搜索功能开发者。用户在搜索框中输入关键词后，系统需要：

在商品名称和描述中进行模糊匹配
支持大小写不敏感搜索
允许高级用户使用正则表达式精确搜索
过滤掉已下架的商品
搜索结果需按相关性排序

Alice 需要掌握 PostgreSQL 提供的各种过滤与模式匹配工具来完成这个搜索系统。

3. Concept：AND / OR / NOT 组合条件

(1) 逻辑运算符优先级

运算符	优先级	说明
`NOT`	最高	取反
`AND`	中	两条件同时为真
`OR`	最低	任一条件为真

▶ 示例：AND 组合条件

SQL

SELECT product_name, unit_price, category
FROM products
WHERE category = 'Electronics'
  AND unit_price > 500
  AND is_active = true;

TEXT 📖 仅展示

 id | name     | value 
----+----------+-------
  1 | example  | 42
(1 row)

▶ 示例：OR 组合条件

SQL

SELECT product_name, unit_price, category
FROM products
WHERE category = 'Electronics'
   OR category = 'Books'
   OR category = 'Toys';

TEXT 📖 仅展示

 id | name     | value 
----+----------+-------
  1 | example  | 42
(1 row)

(2) 用括号控制逻辑

OR 优先级低于 AND，不加括号可能得到非预期结果。

写法	逻辑含义
`a AND b OR c`	`(a AND b) OR c`
`a AND (b OR c)`	`a AND (b OR c)` — 通常这才是你想要的

▶ 示例：括号改变逻辑

SQL

SELECT product_name, unit_price, category
FROM products
WHERE is_active = true
  AND (category = 'Electronics' OR category = 'Books');

TEXT 📖 仅展示

 id | name     | value 
----+----------+-------
  1 | example  | 42
(1 row)

(3) NOT 取反

NOT 可以否定任何布尔表达式，常与 IN、BETWEEN、LIKE 等搭配。

▶ 示例：NOT 否定条件

SQL

SELECT product_name, unit_price
FROM products
WHERE NOT category = 'Electronics'
  AND is_active = true;

TEXT 📖 仅展示

 id | name     | value 
----+----------+-------
  1 | example  | 42
(1 row)

4. Concept：IN / BETWEEN / IS NULL

(1) IN 与 NOT IN

IN 检查值是否在指定列表中，等价于多个 OR，但更简洁且性能更优。

写法	说明
`col IN (a, b, c)`	等价于 `col = a OR col = b OR col = c`
`col NOT IN (a, b, c)`	等价于 `col <> a AND col <> b AND col <> c`

▶ 示例：IN 过滤多分类

SQL

SELECT product_name, unit_price, category
FROM products
WHERE category IN ('Electronics', 'Books', 'Toys')
  AND is_active = true;

TEXT 📖 仅展示

 id | name     | value 
----+----------+-------
  1 | example  | 42
(1 row)

(2) BETWEEN 范围过滤

BETWEEN 包含边界值，等价于 >= AND <=。

写法	等价
`col BETWEEN a AND b`	`col >= a AND col <= b`
`col NOT BETWEEN a AND b`	`col < a OR col > b`

▶ 示例：价格区间过滤

SQL

SELECT product_name, unit_price
FROM products
WHERE unit_price BETWEEN 100 AND 500
ORDER BY unit_price;

TEXT 📖 仅展示

 id | name     | value 
----+----------+-------
  1 | example  | 42
(1 row)

▶ 示例：日期范围过滤

SQL

SELECT order_id, customer_id, order_date, total_amount
FROM orders
WHERE order_date BETWEEN '2025-01-01' AND '2025-06-30'
  AND order_status = 'completed';

TEXT 📖 仅展示

 id | name     | value 
----+----------+-------
  1 | example  | 42
(1 row)

(3) IS NULL / IS NOT NULL

NULL 不等于任何值（包括自身），必须用 IS NULL 或 IS NOT NULL 判断。

写法	结果
`NULL = NULL`	`NULL`（不是 TRUE）
`NULL <> NULL`	`NULL`（不是 TRUE）
`col IS NULL`	正确的 NULL 判断
`col IS NOT NULL`	正确的非 NULL 判断

▶ 示例：查找缺少折扣的商品

SQL

SELECT product_name, unit_price, discount_rate
FROM products
WHERE discount_rate IS NULL
  AND is_active = true;

TEXT 📖 仅展示

 count 
-------
     5
(1 row)

(4) NOT IN 与 NULL 的陷阱

当 NOT IN 子列表中包含 NULL 时，整个表达式可能返回空结果集。

表达式	结果	原因
`3 NOT IN (1, 2, NULL)`	`NULL`	`3 <> NULL` 为 NULL，NULL AND ... 为 NULL
`3 IN (1, 2, NULL)`	`NULL`	`3 = NULL` 为 NULL，FALSE OR NULL 为 NULL

▶ 示例：安全使用 NOT IN

SQL

-- Dangerous: NULL in subquery can break NOT IN
SELECT product_name
FROM products
WHERE category NOT IN (
  SELECT category FROM categories WHERE is_active = false
);

-- Safe: use NOT EXISTS instead
SELECT p.product_name
FROM products p
WHERE NOT EXISTS (
  SELECT 1 FROM categories c
  WHERE c.category = p.category AND c.is_active = false
);

TEXT 📖 仅展示

 id | name     | value 
----+----------+-------
  1 | example  | 42
(1 row)

5. Concept：LIKE / ILIKE 模式匹配

(1) LIKE 通配符

通配符	含义	示例
`%`	匹配任意长度字符串（含空串）	`'Phone%'` 匹配 Phone、Phone Case
`_`	匹配单个字符	`'A_c'` 匹配 Arc、ABC

▶ 示例：LIKE 前缀匹配

SQL

SELECT product_name, unit_price
FROM products
WHERE product_name LIKE 'Wireless%'
  AND is_active = true;

TEXT 📖 仅展示

 id | name     | value 
----+----------+-------
  1 | example  | 42
(1 row)

▶ 示例：LIKE 包含匹配

SQL

SELECT product_name, unit_price
FROM products
WHERE product_name LIKE '%Battery%'
  AND is_active = true;

TEXT 📖 仅展示

 id | name     | value 
----+----------+-------
  1 | example  | 42
(1 row)

(2) ILIKE：大小写不敏感（PostgreSQL 特色）

ILIKE 是 PostgreSQL 扩展，忽略大小写进行模式匹配，等价于 LIKE + LOWER()。

操作符	大小写敏感	标准SQL
`LIKE`	敏感	是
`ILIKE`	不敏感	否（PG 独有）

▶ 示例：ILIKE 搜索

SQL

SELECT product_name, unit_price, description
FROM products
WHERE (product_name ILIKE '%iphone%'
   OR description ILIKE '%iphone%')
  AND is_active = true;

TEXT 📖 仅展示

 id | name     | value 
----+----------+-------
  1 | example  | 42
(1 row)

(3) 转义通配符

当需要搜索字面上的 % 或 _ 时，使用 ESCAPE 指定转义字符。

▶ 示例：搜索含百分号的描述

SQL

SELECT product_name, description
FROM products
WHERE description LIKE '%100\%%' ESCAPE '\';

TEXT 📖 仅展示

 id | name     | value 
----+----------+-------
  1 | example  | 42
(1 row)

6. Concept：正则表达式匹配

(1) 正则操作符一览

PostgreSQL 提供四种正则匹配操作符，以及 SIMILAR TO 语法。

操作符	大小写敏感	说明
`~`	敏感	匹配正则
`~*`	不敏感	匹配正则（忽略大小写）
`!~`	敏感	不匹配正则
`!~*`	不敏感	不匹配正则（忽略大小写）
`SIMILAR TO`	敏感	SQL 标准正则子集（支持 `%` `_` `

▶ 示例：~ 正则匹配

SQL

SELECT product_name, unit_price
FROM products
WHERE product_name ~ '^(Wireless|Bluetooth)'
  AND is_active = true;

TEXT 📖 仅展示

 id | name     | value 
----+----------+-------
  1 | example  | 42
(1 row)

▶ 示例：~* 大小写不敏感正则

SQL

SELECT product_name, unit_price
FROM products
WHERE product_name ~* 'iphone\s*(1[0-9])?'
  AND is_active = true;

TEXT 📖 仅展示

 id | name     | value 
----+----------+-------
  1 | example  | 42
(1 row)

(2) SIMILAR TO

SIMILAR TO 介于 LIKE 和 POSIX 正则之间，支持 |、[]、*、+ 等，但使用 SQL 风格的通配符 % 和 _。

特性	LIKE	SIMILAR TO	POSIX ~
`%` 通配	支持	支持	不支持（用 `.*`）
`_` 通配	支持	支持	不支持（用 `.`）
`	` 或	不支持	支持
`[]` 字符类	不支持	支持	支持
`+*{m,n}` 量词	不支持	部分支持	完整支持

▶ 示例：SIMILAR TO 匹配

SQL

SELECT product_name, unit_price
FROM products
WHERE product_name SIMILAR TO '(Wireless|Bluetooth)%'
  AND is_active = true;

TEXT 📖 仅展示

 id | name     | value 
----+----------+-------
  1 | example  | 42
(1 row)

▶ 示例：!~ 排除正则匹配

SQL

SELECT product_name, unit_price
FROM products
WHERE product_name !~ '(Refurbished|Used)'
  AND is_active = true;

TEXT 📖 仅展示

 id | name     | value 
----+----------+-------
  1 | example  | 42
(1 row)

7. Concept：ANY / ALL / EXISTS 子查询过滤

(1) ANY 与 ALL

操作符	含义	等价写法
`col = ANY(array)`	等于数组中任意一个值	`col IN (...)`
`col > ANY(array)`	大于数组中至少一个值	—
`col > ALL(array)`	大于数组中所有值	—

▶ 示例：ANY 等价于 IN

SQL

SELECT product_name, unit_price
FROM products
WHERE category = ANY(ARRAY['Electronics', 'Books', 'Toys'])
  AND is_active = true;

TEXT 📖 仅展示

 id | name     | value 
----+----------+-------
  1 | example  | 42
(1 row)

▶ 示例：ALL 比较子查询

SQL

SELECT product_name, unit_price, category
FROM products p
WHERE unit_price > ALL (
  SELECT unit_price FROM products
  WHERE category = 'Books' AND is_active = true
);

TEXT 📖 仅展示

 id | name     | value 
----+----------+-------
  1 | example  | 42
(1 row)

(2) EXISTS 子查询

EXISTS 检查子查询是否返回行，不关心具体值，只关心有无结果。比 IN 更安全（无 NULL 陷阱），且在关联子查询中性能通常更优。

用法	说明
`EXISTS (subquery)`	子查询有行返回则为 TRUE
`NOT EXISTS (subquery)`	子查询无行返回则为 TRUE

▶ 示例：EXISTS 查找有订单的客户

SQL

SELECT customer_id, customer_name
FROM customers c
WHERE EXISTS (
  SELECT 1 FROM orders o
  WHERE o.customer_id = c.customer_id
    AND o.order_date >= CURRENT_DATE - INTERVAL '30 days'
);

TEXT 📖 仅展示

 id | name     | value 
----+----------+-------
  1 | example  | 42
(1 row)

(3) IN vs EXISTS 选择指南

场景	推荐	原因
子查询结果集小	`IN`	优化器先执行子查询，构建小集合
外层表小，子查询表大	`EXISTS`	优化器对外层每行执行子查询，尽早终止
子查询可能含 NULL	`EXISTS`	避免 NOT IN 的 NULL 陷阱
独立子查询（不关联外层）	`IN`	可物化子查询结果

8. 流程图：模式匹配选择决策

flowchart TD
    A[需要模式匹配?] --> B{大小写敏感?}
    B -->|敏感| C{需要正则?}
    B -->|不敏感| D{需要正则?}
    C -->|简单通配| E[LIKE]
    C -->|需要 OR/字符类| F[SIMILAR TO]
    C -->|完整正则| G["~ 操作符"]
    D -->|简单通配| H[ILIKE]
    D -->|完整正则| I["~* 操作符"]
    E --> J[返回结果]
    F --> J
    G --> J
    H --> J
    I --> J

    style A fill:#e1f5fe
    style J fill:#c8e6c9

9. 综合示例

Alice 实现了电商搜索功能的核心查询逻辑。

SQL

-- Step 1: Basic keyword search with ILIKE (case-insensitive)
SELECT product_id, product_name, unit_price, description
FROM products
WHERE is_active = true
  AND (product_name ILIKE '%keyboard%' OR description ILIKE '%keyboard%')
ORDER BY unit_price ASC;

-- Step 2: Advanced regex search for power users
SELECT product_id, product_name, unit_price, description
FROM products
WHERE is_active = true
  AND product_name ~* '(mechanical|wireless)\s*keyboard'
  AND unit_price BETWEEN 50 AND 300
ORDER BY unit_price DESC;

-- Step 3: Search products in specific categories with NULL handling
SELECT product_id, product_name, unit_price, category
FROM products
WHERE category IN ('Electronics', 'Computer Accessories', 'Gaming')
  AND is_active = true
  AND discount_rate IS NOT NULL
  AND unit_price BETWEEN 20 AND 500
ORDER BY unit_price DESC NULLS LAST;

-- Step 4: Products that have at least one completed order (EXISTS)
SELECT p.product_id, p.product_name, p.unit_price
FROM products p
WHERE p.is_active = true
  AND EXISTS (
    SELECT 1 FROM order_items oi
    JOIN orders o ON o.order_id = oi.order_id
    WHERE oi.product_id = p.product_id
      AND o.order_status = 'completed'
      AND o.order_date >= CURRENT_DATE - INTERVAL '90 days'
  )
  AND NOT EXISTS (
    SELECT 1 FROM product_flags pf
    WHERE pf.product_id = p.product_id
      AND pf.flag_type = 'recalled'
  )
ORDER BY p.unit_price DESC
FETCH FIRST 50 ROWS ONLY;

❓ 常见问题

Q LIKE 和 ILIKE 性能差别大吗？

A ILIKE 因为需要忽略大小写，无法利用标准 B-tree 索引。大数据量时建议用 PostgreSQL 的 pg_trgm 扩展创建 GIN 索引来加速 ILIKE 查询。

Q NOT IN 子查询返回 NULL 怎么办？

A 子查询结果中如果存在 NULL，NOT IN 整体可能返回空结果。解决办法：1) 用 NOT EXISTS 替代；2) 在子查询中加 WHERE col IS NOT NULL 过滤掉 NULL。

Q BETWEEN 能用于 TIMESTAMP 吗？

A 可以，但注意 BETWEEN 包含边界。对 TIMESTAMP 来说，BETWEEN '2025-01-01' AND '2025-01-31' 不包含 1 月 31 日的时分秒部分。推荐用 >= AND < 替代。

Q SIMILAR TO 和 POSIX 正则哪个更好？

A SIMILAR TO 是 SQL 标准子集，可移植性好但功能有限。POSIX 正则（~ 操作符）功能完整，推荐在 PostgreSQL 项目中优先使用。

Q ANY 和 IN 有什么区别？

A col = ANY(array) 与 col IN (list) 功能等价，但 ANY 可以接受数组参数和子查询返回的数组，且支持 > ANY、< ALL 等非常规比较，IN 只支持等值。

Q LIKE '%keyword%' 为什么走不到索引？

A 前缀通配符 %keyword 使 B-tree 索引失效，因为无法利用索引的排序特性。解决方案：pg_trgm GIN 索引、全文搜索（tsvector + tsquery）或专用搜索引擎。

📖 小节

AND/OR/NOT 组合条件时注意优先级，用括号显式控制逻辑
IN 适合多值等值匹配，BETWEEN 适合范围过滤，IS NULL 处理空值
LIKE 做简单通配匹配，ILIKE 忽略大小写（PostgreSQL 独有）
POSIX 正则 ~ / ~* 提供完整正则能力，SIMILAR TO 介于 LIKE 和正则之间
EXISTS 比 IN 更安全（无 NULL 陷阱），关联子查询中通常性能更优
ANY/ALL 提供数组/子查询的量化比较能力

📝 作业

⭐ 编写查询，从 products 表中查找 category 为 Electronics 或 Books 且 is_active = true 的商品，按 unit_price 降序排列。
⭐⭐ 编写查询，用 ILIKE 在 product_name 和 description 中搜索用户输入的关键词（如 "wireless mouse"），同时过滤 unit_price BETWEEN 20 AND 200，排除 discount_rate IS NULL 的商品。
⭐⭐⭐ 编写查询，用 EXISTS 查找最近 90 天内产生过订单的客户，同时用 NOT EXISTS 排除被标记为 "suspended" 的客户，结果按客户注册时间降序排列，分页展示前 20 条。

Web-Tutorial 技术团队

由多位开发者共同维护的编程教程平台。每篇教程由对应领域的开发者编写和审核，确保内容准确可靠。如发现任何问题，欢迎向我们反馈。

关于我们

PostgreSQL: PostgreSQL高级过滤与模式匹配

1. 你将学到

2. 故事

3. Concept：AND / OR / NOT 组合条件

(1) 逻辑运算符优先级

▶ 示例：AND 组合条件

▶ 示例：OR 组合条件

(2) 用括号控制逻辑

▶ 示例：括号改变逻辑

(3) NOT 取反

▶ 示例：NOT 否定条件

4. Concept：IN / BETWEEN / IS NULL

(1) IN 与 NOT IN

▶ 示例：IN 过滤多分类

(2) BETWEEN 范围过滤

▶ 示例：价格区间过滤

▶ 示例：日期范围过滤

(3) IS NULL / IS NOT NULL

▶ 示例：查找缺少折扣的商品

(4) NOT IN 与 NULL 的陷阱

▶ 示例：安全使用 NOT IN

5. Concept：LIKE / ILIKE 模式匹配

(1) LIKE 通配符

▶ 示例：LIKE 前缀匹配

▶ 示例：LIKE 包含匹配

(2) ILIKE：大小写不敏感（PostgreSQL 特色）

▶ 示例：ILIKE 搜索

(3) 转义通配符

▶ 示例：搜索含百分号的描述

6. Concept：正则表达式匹配

(1) 正则操作符一览

▶ 示例：~ 正则匹配

▶ 示例：~* 大小写不敏感正则

(2) SIMILAR TO

▶ 示例：SIMILAR TO 匹配

▶ 示例：!~ 排除正则匹配

7. Concept：ANY / ALL / EXISTS 子查询过滤

(1) ANY 与 ALL

▶ 示例：ANY 等价于 IN

▶ 示例：ALL 比较子查询

(2) EXISTS 子查询

▶ 示例：EXISTS 查找有订单的客户

(3) IN vs EXISTS 选择指南

8. 流程图：模式匹配选择决策

9. 综合示例

❓ 常见问题

📖 小节

📝 作业

🙏 帮我们做得更好