模式匹配（LIKE / SIMILAR TO / POSIX 正则）

LIKE 是 SQL 标准里最简单的模糊匹配，SIMILAR TO 补上了正则表达能力，而 POSIX 正则（~ 系列）则是 PostgreSQL 里最强大的文本匹配武器。本节讲清三者的语法边界、各自的定位取舍，以及「为什么 LIKE '%abc' 用不上索引」这个几乎人人踩过的性能坑。

前置知识

阅读本节前，建议先了解：逻辑运算符（AND / OR / NOT）

基础概念

术语对照

中文	English	说明
模式匹配	Pattern Matching	用通配符或正则描述一组字符串
通配符	Wildcard	% 匹配任意长度，_ 匹配单个字符
转义符	Escape Character	让通配符退化为普通字符，默认反斜杠
大小写不敏感	Case-Insensitive	ILIKE、~* 具备该特性
SQL 正则	SIMILAR TO	介于 LIKE 与 POSIX 之间的中间形态
POSIX 正则	POSIX Regular Expression	最完整的正则实现，~ 系列运算符
前缀锚定	Prefix Anchoring	模式以固定字符串开头，可用 B-tree 索引
三元组索引	Trigram Index	pg_trgm 提供的 GIN/GiST 索引，支持任意位置匹配
字符类	Character Class	[abc]、[a-z]、\d 等匹配单字符的集合
量词	Quantifier	*、+、?、{n,m} 控制重复次数

三种匹配方式的分工

PostgreSQL 提供了三层能力，能力递增、性能递减，选择时先问自己「够用就停」：

方式	运算符	表达力	典型场景	可否走索引
LIKE	LIKE / ILIKE / NOT LIKE	只有 % 与 _	前缀、后缀、包含子串	仅前缀 abc%
SIMILAR TO	SIMILAR TO	SQL 标准正则（弱）	简单可选分支、定长模式	几乎不可
POSIX 正则	~ / ~* / !~ / !~*	完整正则	复杂校验、提取、替换	仅前缀 ^abc

-- 三种方式做同一件事：找出名称含「线」的商品
SELECT sku, name FROM products WHERE name LIKE '%线%';
SELECT sku, name FROM products WHERE name SIMILAR TO '%线%';
SELECT sku, name FROM products WHERE name ~ '线';

三条语句结果相同，但执行代价差得很远：LIKE 走的是专用快速路径，POSIX 正则要启动正则引擎。能用 LIKE 就别上正则。

示例数据

后续示例使用 demo 库中的 products 表：

CREATE TABLE products (
  sku        varchar(20)  PRIMARY KEY,
  name       varchar(50)  NOT NULL,
  category   varchar(20)  NOT NULL,
  price      numeric(10,2) NOT NULL,
  stock      integer      NOT NULL,
  contact    text,                        -- 用于演示转义与正则提取
  created_at timestamptz  NOT NULL DEFAULT now()
);

INSERT INTO products (sku, name, category, price, stock, contact) VALUES
  ('SKU-1001', '显示器支架',   'peripherals',  120.00, 120, 'a_b@shop.com'),
  ('SKU-1002', 'USB-C 线缆',   'peripherals',   30.00, 300, 'sales+usb@shop.com'),
  ('SKU-1003', '机械键盘',     'input',        299.00,  80, 'kb_team@shop.com'),
  ('SKU-1004', '无线鼠标',     'input',         89.00, 200, 'mouse@shop.com'),
  ('SKU-1005', '显示器',       'display',     1200.00,  25, 'display@shop.com'),
  ('SKU-1006', 'HDMI 线缆',    'peripherals',   59.00, 500, 'hdmi@shop.com'),
  ('SKU-1007', '扩展坞',       'peripherals',  399.00,  60, 'dock@shop.com'),
  ('SKU-1008', '投影仪',       'display',     2999.00,  10, 'projector@shop.com'),
  ('SKU-1009', '摄像头 1080P', 'input',        199.00, 150, 'cam_1080@shop.com'),
  ('SKU-1010', '音箱',         'audio',        449.00,   0, 'audio@shop.com');

    sku    |     name      |  category   |  price  | stock |      contact
-----------+---------------+-------------+---------+-------+--------------------
 SKU-1001  | 显示器支架    | peripherals |  120.00 |   120 | a_b@shop.com
 SKU-1002  | USB-C 线缆    | peripherals |   30.00 |   300 | sales+usb@shop.com
 SKU-1003  | 机械键盘      | input       |  299.00 |    80 | kb_team@shop.com
 SKU-1004  | 无线鼠标      | input       |   89.00 |   200 | mouse@shop.com
 SKU-1005  | 显示器        | display     | 1200.00 |    25 | display@shop.com
 SKU-1006  | HDMI 线缆     | peripherals |   59.00 |   500 | hdmi@shop.com
 SKU-1007  | 扩展坞        | peripherals |  399.00 |    60 | dock@shop.com
 SKU-1008  | 投影仪        | display     | 2999.00 |    10 | projector@shop.com
 SKU-1009  | 摄像头 1080P  | input       |  199.00 |   150 | cam_1080@shop.com
 SKU-1010  | 音箱          | audio       |  449.00 |     0 | audio@shop.com
(10 rows)

语法与用法

运算符一览

运算符	含义	大小写	示例
LIKE	模式匹配（% / _）	敏感	'Abc' LIKE 'A%' → true
NOT LIKE	反向匹配	敏感	'Abc' NOT LIKE 'a%' → true
ILIKE	模式匹配	不敏感	'Abc' ILIKE 'a%' → true
NOT ILIKE	反向匹配	不敏感	'Abc' NOT ILIKE 'a%' → false
SIMILAR TO	SQL 正则	敏感	'abc' SIMILAR TO '(a|b)bc'
~	POSIX 正则匹配	敏感	'abc' ~ '^a'
~*	POSIX 正则匹配	不敏感	'ABC' ~* '^a'
!~	POSIX 正则不匹配	敏感	'abc' !~ '^b'
!~*	POSIX 正则不匹配	不敏感	'ABC' !~* '^b'

-- 最简可用示例：已建 products 表并插入数据后
SELECT sku, name
FROM products
WHERE name LIKE '%线缆%';          -- 包含子串

    sku    |    name
-----------+-------------
 SKU-1002  | USB-C 线缆
 SKU-1006  | HDMI 线缆
(2 rows)

LIKE 的通配符语义

'abc'     LIKE 'abc'      → true    无通配符，等价于 =
'abc'     LIKE 'a%'       → true    % 匹配任意长度（含空串）
'abc'     LIKE '%c'       → true    后缀匹配
'abc'     LIKE '%b%'      → true    包含匹配
'abc'     LIKE '_bc'      → true    _ 恰好匹配一个字符
'abc'     LIKE 'a_'       → false   _ 只匹配一个，'bc' 是两个
'abc'     LIKE 'a__'      → true    两个 _ 匹配 bc
'abc'     LIKE 'a%c'      → true    % 可匹配空串，故 a + '' + c
'abcdef'  LIKE 'a%d%f'    → true    多个 % 可以共存
'abc'     LIKE 'ABC'      → false   LIKE 大小写敏感
'abc'     ILIKE 'ABC'     → true    ILIKE 不敏感

-- 一次性验证上表的关键几行
SELECT 'abc' LIKE 'a%'    AS prefix,
       'abc' LIKE '_bc'   AS one_char,
       'abc' LIKE 'a_'    AS too_short,
       'abc' LIKE 'A%'    AS case_sensitive,
       'abc' ILIKE 'A%'   AS case_insensitive;

 prefix | one_char | too_short | case_sensitive | case_insensitive
--------+----------+-----------+----------------+------------------
 t      | t        | f         | f              | t
(1 row)

% 不匹配 NULL

NULL LIKE '%' 返回 NULL 而不是 true。空字符串 '' 才匹配 '%'。判定「非空」不要用 col LIKE '%'，它同时漏掉 NULL。

SELECT NULL::text LIKE '%' AS null_like, '' LIKE '%' AS empty_like;

 null_like | empty_like
-----------+------------
           | t
(1 row)

详细说明

ESCAPE：让通配符退化为普通字符

当业务数据里真的含 % 或 _ 时（如 discount_50%、a_b），必须转义，否则会被当成通配符：

-- 目标：匹配 contact 中以 "a_b" 开头的值
-- 错误！_ 被当作「任意单字符」，会额外命中 aXb
SELECT sku, contact FROM products WHERE contact LIKE 'a_b%';

-- 正确：显式指定转义符
SELECT sku, contact FROM products WHERE contact LIKE 'a\_b%' ESCAPE '\';

-- 也可以自定义转义符，避免与数据里的反斜杠冲突
SELECT sku, contact FROM products WHERE contact LIKE 'a#_b%' ESCAPE '#';

    sku    |   contact
-----------+--------------
 SKU-1001  | a_b@shop.com
(1 row)

ESCAPE 的规则很简洁：紧跟在转义符后的 %、_、转义符本身，都失去特殊含义。

模式	无 ESCAPE 时含义	有 ESCAPE '\' 时含义
a_b	a + 任意单字符 + b	字面量 a_b
a\%b	a + 反斜杠 + 任意串 + b	a + 字面 % + b
a\\b	a + 双反斜杠 + b	a + 字面 \ + b
\%	反斜杠 + 任意串	字面 %

说明：standard_conforming_strings = on（默认）下，SQL 字符串里的 \ 是普通字符，因此 '\' 是合法的单反斜杠。若该参数为 off，需要写成 E'\\'。

用 position() 或 strpos() 做字面量查找可以完全避开转义问题：

-- 需要对「纯字面量」做包含判断时，strpos 更直观
SELECT sku, contact FROM products WHERE strpos(contact, 'a_b') > 0;

SIMILAR TO：SQL 标准的正则子集

SIMILAR TO 的语法介于 LIKE 与完整正则之间：它把 % / _ 保留为「任意串 / 任意单字符」，同时引入了 |、*、+、?、{}、()、[] 等正则元字符。注意：SIMILAR TO 是整串匹配，不自动锚定行为等同于 LIKE，而不是 POSIX 的「子串查找」。

-- 目标：SKU 是 SKU-1001 ~ SKU-1004
SELECT sku FROM products
WHERE sku SIMILAR TO 'SKU-100[1-4]'
ORDER BY sku;

    sku
-----------
 SKU-1001
 SKU-1002
 SKU-1003
 SKU-1004
(4 rows)

SIMILAR TO 与 LIKE、POSIX 的元字符差异是最大的记忆负担，集中对照一次：

功能	LIKE	SIMILAR TO	POSIX 正则
任意长度	%	%	.*
单个字符	_	_	.
重复 0 次或多次	不支持	*	*
重复 1 次或多次	不支持	+	+
可选	不支持	?	?
或	不支持	|	|
精确次数	不支持	{n,m}	{n,m}
字符类	不支持	[...]	[...]
匹配语义	整串	整串	子串

-- 用 SIMILAR TO 表达「可选分支」是它最常见的价值
SELECT 'color' SIMILAR TO '(colou?r|colour)' AS m1,     -- u 可选
       'ABC-12' SIMILAR TO '[A-Z]{3}-[0-9]{2}' AS m2,   -- 定长段
       'ab' SIMILAR TO 'ab' AS m3,
       'xaby' SIMILAR TO 'ab' AS m4;                     -- 整串匹配，故 false

 m1 | m2 | m3 | m4
----+----+----+----
 t  | t  | t  | f
(1 row)

SIMILAR TO 的两大局限

1. 不能匹配子串：必须有 % 包裹才能做「包含」语义，写 'xaby' SIMILAR TO 'ab' 是 false。
2. 不支持所有正则特性：没有 \d、\w、非贪婪量词、前瞻断言等。复杂需求必须换 POSIX。

正因为「既不如 LIKE 简单、又不如 POSIX 强大」，实践中 SIMILAR TO 用得很少。新代码建议直接用 POSIX 正则。

POSIX 正则：匹配、提取、替换、切分

POSIX 正则在 PostgreSQL 里有四个层面的用法，各自对应一组函数。

第一层：布尔匹配（运算符）

-- ~ 匹配、~* 不区分大小写匹配、!~ 不匹配、!~* 不区分大小写不匹配
SELECT name,
       name ~ '^显示器'        AS starts_display,   -- 以「显示器」开头
       name ~* '^usb'          AS ci_usb,           -- 不区分大小写
       name !~ '\d'            AS no_digit,         -- 不含数字
       name ~ '\d{3,4}P'       AS has_resolution    -- 含 3~4 位数字 + P
FROM products
ORDER BY sku;

     name      | starts_display | ci_usb | no_digit | has_resolution
---------------+----------------+--------+----------+----------------
 显示器支架    | t              | f      | t        | f
 USB-C 线缆    | f              | t      | t        | f
 机械键盘      | f              | f      | t        | f
 无线鼠标      | f              | f      | t        | f
 显示器        | t              | f      | t        | f
 HDMI 线缆     | f              | f      | t        | f
 扩展坞        | f              | f      | t        | f
 投影仪        | f              | f      | t        | f
 摄像头 1080P  | f              | f      | f        | t
 音箱          | f              | f      | t        | f
(10 rows)

第二层：提取

函数	返回	说明
regexp_match(s, p)	text[] 或 NULL	只返回第一个匹配的子串数组
regexp_matches(s, p)	setof text[]	返回所有匹配，需在 SELECT 列表中展开
regexp_substr(s, p)	text	返回第一个匹配的子串（PostgreSQL 15+，语义更直观）

-- regexp_match 返回数组，第 1 个元素是整体匹配，后续是捕获组
SELECT regexp_match('contact: 010-12345678', '(\d{3})-(\d{8})') AS matched;

          matched
----------------------------
 {010-12345678,010,12345678}
(1 row)

-- regexp_matches 返回集合，要用 LATERAL 展开成多行
SELECT name, m[1] AS digits
FROM products
CROSS JOIN LATERAL regexp_matches(name, '\d+') AS m
ORDER BY name;

     name      | digits
---------------+--------
 摄像头 1080P  | 1080
(1 row)

-- PostgreSQL 15+：regexp_substr 直接返回文本，比取数组元素更清爽
SELECT name, regexp_substr(name, '\d+') AS first_num
FROM products
WHERE name ~ '\d'
ORDER BY name;

     name      | first_num
---------------+-----------
 摄像头 1080P  | 1080
(1 row)

第三层：替换

-- regexp_replace(s, pattern, replacement [, flags])
SELECT regexp_replace('2026-09-24', '-', '/')          AS only_first,   -- 默认只替换第一处
       regexp_replace('2026-09-24', '-', '/', 'g')      AS replace_all,   -- g = 全局
       regexp_replace('abc123', '\d', '', 'g')          AS strip_digits,
       regexp_replace('hello world', '\s+', '_', 'g')   AS collapse_space;

 only_first | replace_all | strip_digits | collapse_space
------------+-------------+--------------+-----------------
 2026/09-24 | 2026/09/24  | abc          | hello_world
(1 row)

regexp_replace 的替换串里可以用 \1~\9 引用捕获组，实现「重排」：

-- 把 "2026-09-24" 改写成 "24/09/2026"
SELECT regexp_replace('2026-09-24', '(\d{4})-(\d{2})-(\d{2})', '\3/\2/\1') AS reordered;

        reordered
--------------------------
 24/09/2026
(1 row)

第四层：切分与统计

-- regexp_split_to_array：切分成数组
SELECT regexp_split_to_array('a,b;;c', '[,;]+') AS as_array;

    as_array
----------------
 {a,b,c}
(1 row)

-- regexp_split_to_table：切分成多行，常配合聚合使用
SELECT regexp_split_to_table('apple,banana,cherry', ',') AS fruit;

  fruit
---------
 apple
 banana
 cherry
(3 rows)

-- PostgreSQL 15+：regexp_count / regexp_instr / regexp_like / regexp_substr
SELECT regexp_count('a1b2c3', '\d')               AS digit_count,     -- 出现次数
       regexp_instr('a1b2c3', '\d')               AS first_digit_pos, -- 首个匹配起始位置
       regexp_like('ABC', '^a', 'i')              AS ci_like,         -- 带 flags 的布尔判断
       regexp_substr('a1b2c3', '\d')              AS first_substr;

 digit_count | first_digit_pos | ci_like | first_substr
-------------+-----------------+---------+--------------
           3 |               2 | t       | 1
(1 row)

正则量词与字符类速查

语法	含义	示例
.	任意单个字符	'a.c' 匹配 abc
*	前一项 0 次或多次	'ab*c' 匹配 ac、abc
+	前一项 1 次或多次	'ab+c' 匹配 abc，不匹配 ac
?	前一项 0 次或 1 次	'ab?c' 匹配 ac、abc
{n}	恰好 n 次	'\d{4}' 匹配 4 位数字
{n,}	至少 n 次	'\d{2,}' 匹配 2 位以上
{n,m}	n 到 m 次	'\d{3,4}'
^	字符串开头（或行的开头，带 m 标志）	'^abc'
$	字符串结尾	'abc$'
\d	数字，等价 [[:digit:]]	'\d+'
\w	单词字符（字母、数字、下划线）	'\w+'
\s	空白字符	'\s+'
\D \W \S	上述的反集	'\D+'
[...]	字符类	'[a-z]'
[^...]	排除型字符类	'[^0-9]'
\b	单词边界（PostgreSQL 14+ 支持 \b）	'\mword\M' 旧写法
(...)	捕获组	'(\d+)-(\d+)'
(?:...)	非捕获组	'(?:ab)+'
|	或	'cat|dog'

PostgreSQL 正则的「非贪婪」写法

POSIX 标准没有非贪婪量词，但 PostgreSQL 扩展支持 *?、+?、??、{n,m}?：

SELECT regexp_match('<b>bold</b>', '<b>(.*)</b>')   AS greedy,      -- 贪婪，吃掉中间的 </b>
       regexp_match('<b>bold</b>', '<b>(.*?)</b>')  AS non_greedy;  -- 非贪婪

       greedy        |     non_greedy
---------------------+--------------------
 {bold</b>,bold</b>} | {bold,bold}
(1 row)

大小写敏感与 citext

LIKE 与 ~ 默认大小写敏感。除了用 ILIKE / ~*，另一种彻底方案是 citext 类型：

-- 标准方案一：用 ILIKE（不改变存储，但无法走普通索引）
SELECT sku, contact FROM products WHERE contact ILIKE 'A_B@SHOP.COM';

-- 方案二：用 citext 扩展，让所有比较天然不敏感
CREATE EXTENSION IF NOT EXISTS citext;

CREATE TABLE users (
  id    integer PRIMARY KEY,
  email citext NOT NULL UNIQUE          -- 大小写不敏感，且唯一约束也不敏感
);

INSERT INTO users (id, email) VALUES (1, 'Alice@Shop.com');

-- 下面的查询能命中，且无需 ILIKE
SELECT * FROM users WHERE email = 'alice@shop.com';

 id |      email
----+-----------------
  1 | Alice@Shop.com
(1 row)

citext 的代价是底层仍是 text 存储，比较时需要 lower()，索引必须用表达式索引或 citext_ops：

-- citext 列上的索引（用默认 citext_ops 运算符类即可实现大小写不敏感查找）
CREATE INDEX idx_users_email_ci ON users (email);

-- text 列想实现不敏感唯一，用表达式唯一索引
CREATE TABLE users_text (
  id    integer PRIMARY KEY,
  email text NOT NULL
);
CREATE UNIQUE INDEX uq_users_text_email_lower ON users_text (lower(email));

性能警示：为什么 LIKE '%abc' 用不上索引

这是本节最重要的一节。核心原因：B-tree 索引按值的字典序组织，只能回答「以某前缀开头的范围」，无法定位「中间含有某子串」。

-- 建普通 B-tree 索引
CREATE INDEX idx_products_name ON products (name);
-- 中文/非 C locale 下，前缀匹配还需要 text_pattern_ops 运算符类
CREATE INDEX idx_products_name_pat ON products (name text_pattern_ops);

条件写法	可否用 B-tree 索引	原因
name LIKE '显示器%'	可以（需 text_pattern_ops 或 C locale）	前缀确定，转化为范围扫描
name LIKE '%线缆'	不可以	前缀未知，无法定位起点
name LIKE '%线%'	不可以	同上
name ~ '^显示器'	可以（需 text_pattern_ops）	^ 锚定前缀
name ~ '线缆$'	不可以	无前缀锚定
name SIMILAR TO '显示器%'	一般不可以	优化器不总能识别前缀
lower(name) = 'abc'	不可以（除非建表达式索引）	列被函数包裹
name ILIKE 'abc%'	一般不可以	大小写不敏感无法直接映射到索引序

-- 前缀锚定的三种写法对比
EXPLAIN (ANALYZE, COSTS OFF)
SELECT sku FROM products WHERE name LIKE '显示器%';   -- 前缀：可用索引

                                QUERY PLAN
--------------------------------------------------------------------------
 Index Scan using idx_products_name_pat on products (actual time=0.015..0.019 rows=2 loops=1)
   Index Cond: (((name)::text ~>=~ '显示器'::text) AND ((name)::text ~<~~ '显示器'::text))
   Filter: ((name)::text ~~ '显示器%'::text)
 Planning Time: 0.180 ms
 Execution Time: 0.045 ms
(5 rows)

-- 非前缀：只能全表扫描
EXPLAIN (ANALYZE, COSTS OFF)
SELECT sku FROM products WHERE name LIKE '%线缆';     -- 非前缀：退化为 Seq Scan

                        QUERY PLAN
-----------------------------------------------------------
 Seq Scan on products (actual time=0.011..0.014 rows=2 loops=1)
   Filter: ((name)::text ~~ '%线缆'::text)
   Rows Removed by Filter: 8
 Planning Time: 0.120 ms
 Execution Time=0.038 ms
(5 rows)

注意中间那条 Index Cond：PostgreSQL 把 LIKE 'x%' 智能改写成 >= 'x' AND < 'y' 这样的范围条件，~>=~ / ~<~ 就是 text_pattern_ops 下的比较运算符。小表上优化器仍可能选 Seq Scan，数据量大时才会切换到索引扫描。

替代方案：pg_trgm 三元组索引

需要「任意位置包含」还要求性能时，正解是 pg_trgm（Trigram，三元组）扩展，它把字符串切成连续三字符片段建立 GIN/GiST 索引：

CREATE EXTENSION IF NOT EXISTS pg_trgm;

-- GIN 索引支持 LIKE / ILIKE / ~ / ~* 的中缀匹配
CREATE INDEX idx_products_name_trgm ON products USING gin (name gin_trgm_ops);

-- 现在这条「包含」查询可以用索引了
EXPLAIN (ANALYZE, COSTS OFF)
SELECT sku, name FROM products WHERE name LIKE '%线缆%';

                                   QUERY PLAN
----------------------------------------------------------------------------------
 Bitmap Heap Scan on products (actual time=0.021..0.026 rows=2 loops=1)
   Recheck Cond: ((name)::text ~~ '%线缆%'::text)
   Heap Blocks: exact=1
   ->  Bitmap Index Scan on idx_products_name_trgm (actual time=0.013..0.013 rows=2 loops=1)
         Index Cond: ((name)::text ~~ '%线缆%'::text)
 Planning Time: 0.310 ms
 Execution Time: 0.062 ms
(6 rows)

pg_trgm 还有两个实用函数——相似度与相似度排序：

-- similarity 返回 0~1 的相似度；% 运算符表示「相似度超过阈值」
SELECT similarity('显示器', '显示器支架') AS sim,
       '显示器' % '显示器支架'           AS above_threshold;

-- 用 GIN 索引做模糊排序（常用于「你是不是想找……」）
SELECT name, similarity(name, '显示') AS sim
FROM products
WHERE name % '显示'
ORDER BY sim DESC
LIMIT 3;

   sim    | above_threshold
----------+-----------------
 0.428571 | t
(1 row)

pg_trgm 的取舍

• 优点：支持任意位置匹配，还能做拼写纠错、相似度排序。
• 缺点：索引体积通常是数据本身的数倍；写入代价高；对仅 1~2 个字符的关键词（如 LIKE '%a%'）三元组退化为全表扫描。
• 因此：高频前缀查询用 B-tree，低频包含查询用 pg_trgm，二者可以共存。

实战示例

需求：为一套商品后台实现「模糊搜索 + 格式校验 + 联系方式清洗」三个功能。步骤 1~5。

步骤 1：建表并造数据

DROP TABLE IF EXISTS products;

CREATE TABLE products (
  sku        varchar(20)  PRIMARY KEY,
  name       varchar(50)  NOT NULL,
  category   varchar(20)  NOT NULL,
  price      numeric(10,2) NOT NULL,
  stock      integer      NOT NULL,
  contact    text,
  created_at timestamptz  NOT NULL DEFAULT now()
);

INSERT INTO products (sku, name, category, price, stock, contact) VALUES
  ('SKU-1001', '显示器支架',   'peripherals',  120.00, 120, 'a_b@shop.com'),
  ('SKU-1002', 'USB-C 线缆',   'peripherals',   30.00, 300, 'sales+usb@shop.com'),
  ('SKU-1003', '机械键盘',     'input',        299.00,  80, 'kb_team@shop.com'),
  ('SKU-1004', '无线鼠标',     'input',         89.00, 200, 'mouse@shop.com'),
  ('SKU-1005', '显示器',       'display',     1200.00,  25, 'display@shop.com'),
  ('SKU-1006', 'HDMI 线缆',    'peripherals',   59.00, 500, 'hdmi@shop.com'),
  ('SKU-1007', '扩展坞',       'peripherals',  399.00,  60, 'dock@shop.com'),
  ('SKU-1008', '投影仪',       'display',     2999.00,  10, 'projector@shop.com'),
  ('SKU-1009', '摄像头 1080P', 'input',        199.00, 150, 'cam_1080@shop.com'),
  ('SKU-1010', '音箱',         'audio',        449.00,   0, 'audio@shop.com');

DROP TABLE
CREATE TABLE
INSERT 0 10

步骤 2：关键词搜索（转义用户输入）

用户在搜索框输入 % 或 _ 时必须被转义，否则会变成通配符，甚至造成「输入 % 返回全部数据」的问题：

-- 模拟用户输入 '%线缆'
-- 错误！未转义的 % 会被当成通配符
SELECT sku, name FROM products WHERE name LIKE '%' || '%线缆' || '%';

-- 正确：把输入中的 % 与 _ 转义，再做包含匹配
SELECT sku, name
FROM products
WHERE name LIKE '%' || replace(replace('%线缆', '\', '\\'), '%', '\%') || '%' ESCAPE '\';

    sku    |    name
-----------+-------------
 SKU-1002  | USB-C 线缆
 SKU-1006  | HDMI 线缆
(2 rows)

步骤 3：用正则校验数据格式

-- 校验：SKU 必须是 SKU- 加 4 位数字；联系方式必须是合法邮箱
SELECT sku,
       name,
       sku ~ '^SKU-\d{4}$'                          AS sku_valid,
       contact ~ '^[A-Za-z0-9._%+-]+@[A-Za-z0-9.-]+\.[A-Za-z]{2,}$' AS email_valid
FROM products
ORDER BY sku;

    sku    |     name      | sku_valid | email_valid
-----------+---------------+-----------+-------------
 SKU-1001  | 显示器支架    | t         | t
 SKU-1002  | USB-C 线缆    | t         | t
 SKU-1003  | 机械键盘      | t         | t
 SKU-1004  | 无线鼠标      | t         | t
 SKU-1005  | 显示器        | t         | t
 SKU-1006  | HDMI 线缆     | t         | t
 SKU-1007  | 扩展坞        | t         | t
 SKU-1008  | 投影仪        | t         | t
 SKU-1009  | 摄像头 1080P  | t         | t
 SKU-1010  | 音箱          | t         | t
(10 rows)

用正则表达式做 CHECK 约束，把校验前移到写入阶段：

ALTER TABLE products
  ADD CONSTRAINT ck_products_sku_format
  CHECK (sku ~ '^SKU-[0-9]{4}$');

-- 尝试插入非法 SKU —— 会被约束拦下
INSERT INTO products (sku, name, category, price, stock)
VALUES ('BAD-1', '非法商品', 'test', 1.00, 1);

ERROR:  new row for relation "products" violates check constraint "ck_products_sku_format"
DETAIL:  Failing row contains (BAD-1, 非法商品, test, 1.00, 1, null, 2026-09-24 10:00:00+08).

步骤 4：联系方式清洗与提取

-- 提取邮箱本地部分（@ 之前）、域名（@ 之后）
SELECT contact,
       regexp_replace(contact, '@.*$', '')          AS local_part,
       regexp_replace(contact, '^.*@', '')          AS domain
FROM products
ORDER BY contact
LIMIT 4;

        contact        |  local_part  |    domain
-----------------------+--------------+---------------
 a_b@shop.com          | a_b          | shop.com
 audio@shop.com        | audio        | shop.com
 cam_1080@shop.com     | cam_1080     | shop.com
 display@shop.com      | display      | shop.com
(4 rows)

-- 把下划线统一改为点号（业务规范），并统计改动行数
UPDATE products
SET contact = regexp_replace(contact, '_', '.', 'g')
WHERE contact ~ '_';

SELECT sku, contact FROM products WHERE contact ~ '\.' AND sku IN ('SKU-1001', 'SKU-1003', 'SKU-1009')
ORDER BY sku;

    sku    |     contact
-----------+------------------
 SKU-1001  | a.b@shop.com
 SKU-1003  | kb.team@shop.com
 SKU-1009  | cam.1080@shop.com
(3 rows)

步骤 5：拆分多值字段并聚合回原表

-- 场景：把 "peripherals,input" 这类逗号分隔标签拆成多行
SELECT sku, name, regexp_split_to_table(tags, ',') AS tag
FROM (
  SELECT sku, name,
         CASE category
           WHEN 'peripherals' THEN '周边,配件'
           WHEN 'input'       THEN '输入设备'
           WHEN 'display'     THEN '显示输出'
           ELSE '其他'
         END AS tags
  FROM products
) AS t
ORDER BY sku, tag;

    sku    |     name      |   tag
-----------+---------------+-----------
 SKU-1001  | 显示器支架    | 周边
 SKU-1001  | 显示器支架    | 配件
 SKU-1002  | USB-C 线缆    | 周边
 SKU-1002  | USB-C 线缆    | 配件
 SKU-1003  | 机械键盘      | 输入设备
 SKU-1004  | 无线鼠标      | 输入设备
 SKU-1005  | 显示器        | 显示输出
 SKU-1006  | HDMI 线缆     | 周边
 SKU-1006  | HDMI 线缆     | 配件
 SKU-1007  | 扩展坞        | 周边
 SKU-1007  | 扩展坞        | 配件
 SKU-1008  | 投影仪        | 显示输出
 SKU-1009  | 摄像头 1080P  | 输入设备
 SKU-1010  | 音箱          | 其他
(14 rows)

注意事项

• LIKE 的 % 匹配任意长度，但 _ 只匹配恰好一个字符。 用 _ 做「长度判断」时必须精确数个数。

-- 错误！'a_' 只匹配两个字符
SELECT 'abc' LIKE 'a_';      -- false
-- 正确：三个字符
SELECT 'abc' LIKE 'a__';     -- true

• NULL 参与任何模式匹配都返回 NULL。 判空必须用 IS NULL，不要用 LIKE '%'。

-- 错误！漏掉所有 NULL 行
SELECT * FROM products WHERE name LIKE '%';
-- 正确
SELECT * FROM products WHERE name IS NOT NULL;

• 模式中的 % / _ 是通配符，来自外部输入时务必转义。 用户输入单个 % 会匹配全表，是常见的「越权读全量」隐患。

-- 正确：转义 + 显式 ESCAPE
WHERE name LIKE '%' || replace(replace(:kw, '\', '\\'), '%', '\%') || '%' ESCAPE '\'

• SIMILAR TO 是整串匹配，不是子串查找。 想「包含」必须两端加 %。

-- 错误理解：以为能匹配含 abc 的串
SELECT 'xabcy' SIMILAR TO 'abc';       -- false
-- 正确
SELECT 'xabcy' SIMILAR TO '%abc%';     -- true

• SIMILAR TO 不支持 \d / \w / 非贪婪等特性，复杂需求直接换 POSIX 正则。

• ~ 是子串匹配，不需要 %。 但想「整串完全是数字」必须写 ^...$。

-- 错误理解：以为这样能校验「全是数字」
SELECT '12a3' ~ '\d';            -- true（含数字即可）
-- 正确：锚定首尾
SELECT '12a3' ~ '^\d+$';         -- false
SELECT '123'  ~ '^\d+$';         -- true

• regexp_match 只返回第一个匹配，regexp_matches 返回集合。 在 SELECT 列表里直接用 regexp_matches 会把结果集展开，注意行数变化。

-- 与 CROSS JOIN LATERAL 配合才是「把匹配展开成多行」的标准写法
SELECT name, m[1] FROM products CROSS JOIN LATERAL regexp_matches(name, '\d+') AS m;

• regexp_replace 默认只替换第一处，全局替换必须加 'g' 标志。

-- 错误！只替换了第一个 '-'
SELECT regexp_replace('2026-09-24', '-', '/');        -- 2026/09-24
-- 正确
SELECT regexp_replace('2026-09-24', '-', '/', 'g');   -- 2026/09/24

• LIKE '%abc' 与 ~ 'abc$' 无法使用 B-tree 索引。 大表上的中缀/后缀匹配要改用 pg_trgm 的 GIN 索引。

• 非 C locale 的数据库里，LIKE 'abc%' 也需要 text_pattern_ops 运算符类才能走索引。 直接建 (col) 的普通索引可能无效。

-- 推荐：显式指定 pattern 运算符类
CREATE INDEX idx_t_col_pat ON t (col text_pattern_ops);

• SIMILAR TO 与 POSIX 正则的性能远低于 LIKE。 能用 LIKE 表达的就不该上正则；正则会启动 NFA 引擎，CPU 开销明显。

• .psqlrc 或函数里写正则时注意反斜杠层级。 SQL 字符串与正则各有一层转义，\\d 在字符串里表示正则的 \d（当 standard_conforming_strings = on 时只需 \d）。

最佳实践

1. 优先用 LIKE，其次 POSIX，尽量别用 SIMILAR TO。

-- 简单前缀/后缀/包含用 LIKE
SELECT * FROM products WHERE name LIKE '显示器%';
-- 复杂校验才用正则
SELECT * FROM products WHERE sku ~ '^SKU-\d{4}$';

2. 来自用户的模式输入一律转义，把它当参数处理，不要直接拼接。

-- 封装成可复用的写法：只匹配用户输入的字面内容
SELECT sku, name
FROM products
WHERE name LIKE '%' || replace(replace(:keyword, '\', '\\'), '%', '\%') || '%' ESCAPE '\';

3. 前缀查询用 B-tree + text_pattern_ops，中缀查询用 pg_trgm GIN。

CREATE INDEX idx_products_name_pat  ON products (name text_pattern_ops);  -- 前缀
CREATE INDEX idx_products_name_trgm ON products USING gin (name gin_trgm_ops);  -- 中缀

4. 把「大小写不敏感」的语义下推到类型或索引，而不是依赖 ILIKE。

-- 方案 A：citext 类型
CREATE TABLE users (email citext UNIQUE);
-- 方案 B：表达式唯一索引（保持 text 类型）
CREATE UNIQUE INDEX uq_users_email ON users (lower(email));

5. 用 regexp_replace 的捕获组做「重排」与「归一化」，比在应用层拼字符串更省事也更少出错。

SELECT regexp_replace('2026-09-24', '(\d{4})-(\d{2})-(\d{2})', '\3.\2.\1');

6. 在 CHECK 约束里用正则把格式校验前移到写入阶段，比事后清洗数据便宜得多。

ALTER TABLE products
  ADD CONSTRAINT ck_products_sku_format CHECK (sku ~ '^SKU-[0-9]{4}$');

7. regexp_matches 要配 CROSS JOIN LATERAL，否则「匹配不到」的行会静默消失，容易漏数据。

-- 推荐：LEFT JOIN LATERAL 保留未匹配的行
SELECT p.sku, p.name, m[1] AS digits
FROM products p
LEFT JOIN LATERAL regexp_matches(p.name, '\d+') AS m ON true
ORDER BY p.sku;

8. 用 EXPLAIN 验证模式条件是否走了索引，看到 Seq Scan 且 Rows Removed by Filter 很大就要考虑 pg_trgm。

EXPLAIN (ANALYZE, BUFFERS)
SELECT sku FROM products WHERE name LIKE '%线缆%';

9. 能用 strpos() / position() 表达的「纯字面量包含」，优先用它，它比 LIKE '%...%' 更快且天然无需转义。

SELECT sku FROM products WHERE strpos(name, '线缆') > 0;

下一节

继续学习：IS NULL 与 NULL 判断

参考链接

• Pattern Matching（LIKE / SIMILAR TO / POSIX 正则）
• String Functions and Operators
• pg_trgm 三元组相似度扩展
• citext 大小写不敏感文本类型
• 索引类型（含运算符类与 text_pattern_ops）