首页 / PostgreSQL 入门教程 / 范围与模式匹配

← 上一篇 下一篇 →
PostgreSQL 入门教程

范围与模式匹配

本教程共 50 篇 · 第 24 篇 · 更新于 2026-07-31 · 约 8 分钟阅读

PostgreSQLPostgreSQL 入门教程BETWEENIN 运算符LIKE 模糊匹配模式匹配

24. 范围与模式匹配

本节目标：学会用 BETWEEN、IN、LIKE 在 WHERE 后面做范围判断和模糊筛选，并能用 ESCAPE 转义通配符。

接上章讲的 WHERE 条件过滤，这一章专门讲三类最常用的筛选手法。它们都属于”按条件挑行”，写在 WHERE 后面。

咱们先把稍后要用的两张示例表建好。下面用的是 v18 推荐的 GENERATED AS IDENTITY 自增写法：

-- 用户表
CREATE TABLE users (
  id        INT GENERATED ALWAYS AS IDENTITY PRIMARY KEY,
  username  VARCHAR(50),
  email     VARCHAR(100),
  age       INT,
  created_at DATE
);

-- 订单表
CREATE TABLE orders (
  id        INT GENERATED ALWAYS AS IDENTITY PRIMARY KEY,
  user_id   INT,
  amount    NUMERIC(10,2),
  status    VARCHAR(20),
  created_at DATE
);

INSERT INTO users (username, email, age, created_at) VALUES
  ('alice',  'alice@example.com',  28, '2025-01-12'),
  ('bob',    'bob@example.com',    35, '2025-03-08'),
  ('charlie','charlie@example.com',19, '2025-05-20'),
  ('dave',   'dave@example.com',   42, '2025-07-01'),
  ('emma',   'emma@example.com',   31, '2025-09-15');

INSERT INTO orders (user_id, amount, status, created_at) VALUES
  (1, 99.00,  'paid',     '2025-02-01'),
  (2, 150.50, 'shipped',  '2025-04-10'),
  (3, 20.00,  'pending',  '2025-06-11'),
  (1, 300.00, 'paid',     '2025-08-22'),
  (4, 75.25,  'cancelled','2025-10-05');

BETWEEN…AND 取一段范围

BETWEEN...AND 用来判断某个值是不是落在”下限到上限”之间。它是闭区间，也就是包含两端的边界值。

-- 找出年龄在 20 到 40 岁之间的用户（含 20 和 40）
SELECT username, age
FROM users
WHERE age BETWEEN 20 AND 40
ORDER BY age;
Note

age BETWEEN 20 AND 40 完全等价于 age >= 20 AND age <= 40。用 BETWEEN 只是更好读。

日期也能用 BETWEEN，日期要写成 'YYYY-MM-DD' 的写法：

-- 找出 2025 年上半年注册的订单
SELECT id, user_id, created_at
FROM orders
WHERE created_at BETWEEN '2025-01-01' AND '2025-06-30';

想取”范围之外”，就在前面加 NOT：

-- 找出年龄不在 20 到 40 之间的用户
SELECT username, age
FROM users
WHERE age NOT BETWEEN 20 AND 40;

一个容易踩的坑：BETWEEN 遇到时间会”卡边”

如果你用 BETWEEN 去框一段”日期时间”（timestamp 类型），要特别小心边界。比如想取”2025 年全年的订单”：

-- 看起来没问题，但若 created_at 带了时分秒，会漏掉 12-31 当天多数订单
SELECT COUNT(*)
FROM orders
WHERE created_at BETWEEN '2025-01-01' AND '2025-12-31';

问题出在 '2025-12-31' 被当成”当天 0 点整”。12-31 下午下的单，时间比它大，就被挡在门外了。

Tip

框时间区间更稳妥的写法是”左闭右开”：上限用第二天的起点，并改用 >= 和 <。这种写法对 date 还是 timestamp 都适用，不会漏也不会多。

Note

本章示例里的 created_at 是 DATE 类型、不含时分秒，所以用 BETWEEN 取「整天」是安全的。上面说的「卡边」只发生在字段是 TIMESTAMP（带时刻）时——提前讲这个坑，是怕你以后碰到带时间的字段踩雷。

-- 正确框住整个 2025 年（含 12-31 当天任意时刻）
SELECT COUNT(*)
FROM orders
WHERE created_at >= '2025-01-01'
  AND created_at <  '2026-01-01';

IN / NOT IN 匹配一个值列表

有时候你想挑”等于 A 或等于 B 或等于 C”的行，写一串 OR 太啰嗦。IN 后面跟一个值列表，命中任意一个就算满足。

-- 只查 paid 和 shipped 两种状态的订单
SELECT id, user_id, status
FROM orders
WHERE status IN ('paid', 'shipped');
Tip

上面这条等价于 status = 'paid' OR status = 'shipped'。值一多，IN 的优势就越明显。

反过来，NOT IN 表示”不在列表里”：

-- 排除已取消和待支付的订单
SELECT id, user_id, status
FROM orders
WHERE status NOT IN ('cancelled', 'pending');
Warning

NOT IN 的列表里如果出现一个 NULL，结果可能不符合直觉：整条条件的判断会失效。所以列表值尽量用确定非空的内容。

IN 后面也能跟子查询

IN 后面不仅能写死的列表，还能跟一个子查询（子查询后面章节细讲）。比如”查所有下过单的用户”：

-- id 出现在 orders.user_id 里的那些用户
SELECT username
FROM users
WHERE id IN (SELECT user_id FROM orders);

另外，值一多（成百上千个）把列表直接写死在 IN 里，既难读又可能影响性能。生产里往往改用临时表或 JOIN 来替代。

LIKE / ILIKE 做模糊匹配

当你只记得部分信息，比如”用户名里带 li 两个字母”，就用 LIKE 做模式匹配。模式里有两个通配符（wildcard）：

  • %：匹配任意长度的字符（包括零个）。
  • _：只匹配一个任意字符。
-- 用户名以 a 开头的用户
SELECT username
FROM users
WHERE username LIKE 'a%';

-- 用户名第二个字母是 m 的用户（_ 占一个位置）
SELECT username
FROM users
WHERE username LIKE '_m%';

ILIKE 和 LIKE 用法一样，但它不区分大小写。在 LIKE 眼里 'Alice' 和 'alice' 是不同的，换成 ILIKE 就都能命中：

-- 不区分大小写地匹配以 A 开头的用户名
SELECT username
FROM users
WHERE username ILIKE 'a%';

NOT LIKE / NOT ILIKE 则用来排除匹配上的行。

几种常见写法

  • 开头匹配：'a%'（以 a 开头）
  • 结尾匹配：'%e'（以 e 结尾）
  • 包含匹配：'%li%'（中间含 li）
-- 用户名里包含字母 l 的
SELECT username
FROM users
WHERE username LIKE '%l%';

-- 用户名以 e 结尾的
SELECT username
FROM users
WHERE username LIKE '%e';

性能提醒：前导通配符吃不到索引

LIKE 若以常量开头（如 'a%'），PostgreSQL 可以利用该列上的 B-tree 索引加速。但一旦通配符打头（如 '%li%'、'%e'），数据库只能逐行扫描，数据量大时会明显变慢。

Note

这点对 ILIKE 也成立。而且 ILIKE 不区分大小写，默认还用不了普通 B-tree 索引，需要专门的文本索引（后面索引章节会讲）才能真正提速。所以模糊搜索别滥用，能加前缀条件就尽量加。

ESCAPE 转义通配符

如果数据本身含有 % 或 _，而你想把它们当成普通字符来搜，就得用 ESCAPE 指定一个转义符。

-- 假设某列值里含字面量百分号，比如想精确搜 "10%"
-- 用 $ 作转义符，让第二个 % 变成普通字符
SELECT *
FROM orders
WHERE status LIKE '%10$%%' ESCAPE '$';

规则很简单：转义符后面的 % 和 _ 不再当通配符，而是字面量。

Tip

转义符可以随便选（$、#、\ 都行），只要它不出现在你要搜的正常字符里即可，并在 ESCAPE 后指明。

把多种条件叠在一起用

实际查询里，这三种手法经常一起上。比如”找出 2025 年上半年、状态是 paid 或 shipped、用户名以 a 开头”的订单：

SELECT o.id, u.username, o.status, o.created_at
FROM orders o
JOIN users u ON u.id = o.user_id
WHERE o.created_at BETWEEN '2025-01-01' AND '2025-06-30'
  AND o.status IN ('paid', 'shipped')
  AND u.username LIKE 'a%'
ORDER BY o.created_at;

这条把范围、值列表、模糊匹配三种手法都用上了。条件之间默认是 AND（并且）关系，全部满足才返回。

Tip

条件一多，建议每行写一个 AND，写清楚也方便排查。上面出现的 JOIN 语法后面章节会细讲，这里先混个眼熟。

NOT LIKE 反过来排除

NOT LIKE / NOT ILIKE 用来排掉匹配上的行。比如想看用户名里不含字母 l 的用户：

-- 用户名中不含 l 的
SELECT username
FROM users
WHERE username NOT LIKE '%l%';

顺手记一下：BETWEEN 也能用于文本

BETWEEN 不止能用数字和日期，文本也能用，按字符顺序判断。比如 username BETWEEN 'a' AND 'c' 会匹配字母顺序落在 a 到 c 之间的名字。只是实际里用得少，知道有这回事即可。

选型小抄

  • 精确等于某几个值 → IN
  • 落在一段连续区间 → BETWEEN
  • 只记得片段 → LIKE
  • 以上都不合适，再考虑后面的全文检索

常见错误

用这一类条件，新手最容易犯几个错，我逐个说一下。

第一，把 BETWEEN 当成”开区间”。它其实是闭区间，两端都包含。要排除边界，得自己写 > a AND < b，不能指望 BETWEEN 自动让出边界。

第二，用 BETWEEN 框时间戳却只写到”天”。比如写成 BETWEEN '2025-01-01' AND '2025-12-31'，数据库会把后者理解成 12-31 零点整，当天其余时间的订单全被漏掉。稳妥做法是用 >= 起始 加 < 次日 的”左闭右开”。

第三，在 NOT IN 的列表里放了 NULL。一旦列表出现 NULL，整条条件会失效、结果变成空。列表值要保证非空，或改用 NOT EXISTS。

第四，通配符记反。% 是任意长度（含零个），_ 是恰好一个字符。想匹配”任意字符”却用了 _，结果只命中单字符，搜不到更长的内容。

第五，忘记 LIKE 区分大小写。数据里存的是 Alice，你搜 'a%' 就命不中。需要忽略大小写，直接用 ILIKE。

小结

  • BETWEEN a AND b 取闭区间，含两端；框时间区间建议用 >= 和 < 的”左闭右开”写法避免漏边界。
  • IN (...) 命中列表任一值；NOT IN 反之，列表里别放 NULL。
  • LIKE 用 %（任意长度）和 _（单个字符）做模糊匹配；ILIKE 不区分大小写。
  • 前导通配符（% 开头）会让 LIKE 用不上索引，数据量大时要当心。
  • 遇字面量 %/_ 用 ESCAPE 转义。
上一篇
WHERE 条件过滤
下一篇
DISTINCT 去重与 LIMIT/OFFSET 分页