PostgreSQL 模式匹配完全指南

PostgreSQL 提供了三种强大的模式匹配方法，每种都适用于不同的应用场景。本指南将深入探讨这三种方法的原理、语法和实际应用。

📋 概述

PostgreSQL 支持三种独立的模式匹配方法：

选择指南

LIKE：适用于简单的通配符匹配，安全性最高
SIMILAR TO：适用于需要基本正则表达式功能的场景
POSIX 正则表达式：适用于复杂的模式匹配需求

安全提醒

正则表达式可能消耗大量时间和内存。在处理来自不可信源的模式时，建议：

设置语句超时
优先使用 LIKE 操作符
谨慎处理用户输入的正则表达式

🔍 LIKE 操作符详解

基本语法

sql

string LIKE pattern [ESCAPE escape-character]
string NOT LIKE pattern [ESCAPE escape-character]

通配符规则

通配符	含义	示例
`%`	匹配零个或多个字符	`'hello%'` 匹配以 "hello" 开头的字符串
`_`	匹配任意单个字符	`'h_llo'` 匹配 "hello", "hallo" 等

实战示例：用户搜索功能

假设我们有一个用户表，需要实现用户搜索功能：

sql

-- 创建示例表
CREATE TABLE users (
    id SERIAL PRIMARY KEY,
    username VARCHAR(50),
    email VARCHAR(100),
    phone VARCHAR(20)
);

-- 插入测试数据
INSERT INTO users (username, email, phone) VALUES
    ('john_doe', '[email protected]', '123-456-7890'),
    ('jane_smith', '[email protected]', '987-654-3210'),
    ('bob_wilson', '[email protected]', '555-123-4567'),
    ('alice_brown', '[email protected]', '444-555-6666');

场景 1：模糊搜索用户名

sql

-- 搜索包含 'john' 的用户名
SELECT username, email FROM users
WHERE username LIKE '%john%';

输出结果：

 username |      email
----------+------------------
 john_doe | [email protected]

分析过程：

%john% 模式匹配任何包含 "john" 子字符串的用户名
前后的 % 确保匹配字符串中任意位置的 "john"

场景 2：搜索特定格式的邮箱

sql

-- 搜索以特定域名结尾的邮箱
SELECT username, email FROM users
WHERE email LIKE '%@email.com';

输出结果：

  username   |       email
-------------+-------------------
 john_doe    | [email protected]
 alice_brown | [email protected]

场景 3：搜索特定格式的电话号码

sql

-- 搜索特定格式的电话号码（xxx-xxx-xxxx）
SELECT username, phone FROM users
WHERE phone LIKE '___-___-____';

输出结果：

  username   |    phone
-------------+--------------
 john_doe    | 123-456-7890
 jane_smith  | 987-654-3210
 bob_wilson  | 555-123-4567
 alice_brown | 444-555-6666

分析过程：

每个 _ 匹配一个字符
模式 ___-___-____ 确保电话号码格式为 "三位数-三位数-四位数"

转义字符的使用

当需要匹配字面上的 % 或 _ 字符时：

sql

-- 创建包含特殊字符的测试数据
INSERT INTO users (username, email) VALUES
    ('test_user', 'test%[email protected]'),
    ('user_100%', '[email protected]');

-- 搜索包含字面 % 字符的邮箱
SELECT username, email FROM users
WHERE email LIKE '%\%%' ESCAPE '\';

输出结果：

 username |       email
----------+--------------------
 test_user| test%[email protected]

转义字符技巧

默认转义字符是反斜杠 \
可以使用 ESCAPE 子句指定自定义转义字符
使用 ESCAPE '' 禁用转义功能

ILIKE：不区分大小写的匹配

sql

-- 不区分大小写的搜索
SELECT username FROM users
WHERE username ILIKE 'JOHN%';

输出结果：

 username
----------
 john_doe

🔧 SIMILAR TO 操作符详解

基本语法

sql

string SIMILAR TO pattern [ESCAPE escape-character]
string NOT SIMILAR TO pattern [ESCAPE escape-character]

支持的正则表达式元字符

| 元字符 | 含义 | 示例 | | ------- | ---------- | --------------------------------- | --- | ------------------- | | | | 或操作 | (a | b) 匹配 'a' 或 'b' | | * | 零次或多次 | a* 匹配 '', 'a', 'aa' 等 | | + | 一次或多次 | a+ 匹配 'a', 'aa', 'aaa' 等 | | ? | 零次或一次 | a? 匹配 '' 或 'a' | | {m} | 精确 m 次 | a{3} 匹配 'aaa' | | {m,} | 至少 m 次 | a{2,} 匹配 'aa', 'aaa' 等 | | {m,n} | m 到 n 次 | a{2,4} 匹配 'aa', 'aaa', 'aaaa' |

实战示例：数据验证

场景 1：验证邮箱格式

sql

-- 验证基本邮箱格式
SELECT email,
       email SIMILAR TO '%@%.%' AS is_valid_email
FROM users;

输出结果：

       email        | is_valid_email
--------------------+----------------
 [email protected] | t
 [email protected]| t
 [email protected]    | t
 [email protected]| t

场景 2：验证复杂模式

sql

-- 验证用户名格式：字母开头，可包含字母、数字、下划线，长度3-20
SELECT username,
       username SIMILAR TO '[a-zA-Z][a-zA-Z0-9_]{2,19}' AS is_valid_username
FROM users;

输出结果：

  username   | is_valid_username
-------------+-------------------
 john_doe    | t
 jane_smith  | t
 bob_wilson  | t
 alice_brown | t

场景 3：提取数据模式

sql

-- 查找包含数字的用户名
SELECT username FROM users
WHERE username SIMILAR TO '%[0-9]%';

SUBSTRING 函数与 SIMILAR TO

sql

-- 从字符串中提取匹配模式的部分
SELECT
    phone,
    SUBSTRING(phone SIMILAR '([0-9]{3})-.*' ESCAPE '') AS area_code
FROM users;

输出结果：

    phone     | area_code
--------------+-----------
 123-456-7890 | 123
 987-654-3210 | 987
 555-123-4567 | 555
 444-555-6666 | 444

🚀 POSIX 正则表达式详解

基本操作符

操作符	含义	示例
`~`	匹配	`'abc' ~ 'a.c'` 返回 true
`~*`	不区分大小写匹配	`'ABC' ~* 'a.c'` 返回 true
`!~`	不匹配	`'abc' !~ 'x.z'` 返回 true
`!~*`	不区分大小写不匹配	`'ABC' !~* 'x.z'` 返回 true

实战示例：日志分析系统

假设我们有一个日志表需要分析：

sql

-- 创建日志表
CREATE TABLE access_logs (
    id SERIAL PRIMARY KEY,
    ip_address VARCHAR(15),
    request_path VARCHAR(200),
    user_agent TEXT,
    response_code INTEGER,
    log_time TIMESTAMP
);

-- 插入测试数据
INSERT INTO access_logs (ip_address, request_path, user_agent, response_code, log_time) VALUES
    ('192.168.1.100', '/api/users/123', 'Mozilla/5.0 (Windows NT 10.0)', 200, NOW()),
    ('10.0.0.50', '/admin/dashboard', 'Chrome/91.0.4472.124', 403, NOW()),
    ('203.0.113.10', '/api/data.json', 'PostmanRuntime/7.28.0', 200, NOW()),
    ('192.168.1.200', '/login', 'Mozilla/5.0 (Macintosh)', 302, NOW());

场景 1：识别可疑 IP 地址

sql

-- 查找私有 IP 地址范围
SELECT ip_address, request_path FROM access_logs
WHERE ip_address ~ '^(192\.168\.|10\.|172\.(1[6-9]|2[0-9]|3[01])\.)';

输出结果：

  ip_address  | request_path
--------------+------------------
 192.168.1.100| /api/users/123
 10.0.0.50    | /admin/dashboard
 192.168.1.200| /login

分析过程：

^ 匹配字符串开始
\. 转义点字符，匹配字面上的点
| 表示或操作
[6-9] 匹配 6-9 范围内的数字

场景 2：提取 API 端点

sql

-- 提取 API 路径中的资源 ID
SELECT
    request_path,
    regexp_match(request_path, '/api/(\w+)/(\d+)') AS extracted_parts
FROM access_logs
WHERE request_path ~ '/api/\w+/\d+';

输出结果：

 request_path   |  extracted_parts
----------------+-------------------
 /api/users/123 | {users,123}

场景 3：分析用户代理

sql

-- 识别不同类型的客户端
SELECT
    user_agent,
    CASE
        WHEN user_agent ~* 'Mozilla.*Chrome' THEN 'Chrome Browser'
        WHEN user_agent ~* 'Mozilla.*Firefox' THEN 'Firefox Browser'
        WHEN user_agent ~* 'PostmanRuntime' THEN 'API Testing Tool'
        ELSE 'Other'
    END AS client_type
FROM access_logs;

输出结果：

         user_agent          |   client_type
-----------------------------+------------------
 Mozilla/5.0 (Windows NT 10.0)| Other
 Chrome/91.0.4472.124       | Chrome Browser
 PostmanRuntime/7.28.0      | API Testing Tool
 Mozilla/5.0 (Macintosh)    | Other

高级正则表达式函数

regexp_count：计算匹配次数

sql

-- 统计路径中的分段数量
SELECT
    request_path,
    regexp_count(request_path, '/') AS path_segments
FROM access_logs;

输出结果：

 request_path     | path_segments
------------------+---------------
 /api/users/123   | 3
 /admin/dashboard | 2
 /api/data.json   | 2
 /login           | 1

regexp_replace：文本替换

sql

-- 将私有 IP 地址匿名化
SELECT
    ip_address,
    regexp_replace(ip_address, '(\d+\.\d+\.\d+\.)\d+', '\1***') AS anonymized_ip
FROM access_logs;

输出结果：

  ip_address  | anonymized_ip
--------------+---------------
 192.168.1.100| 192.168.1.***
 10.0.0.50    | 10.0.0.***
 203.0.113.10 | 203.0.113.***
 192.168.1.200| 192.168.1.***

regexp_split_to_array：字符串分割

sql

-- 分割请求路径
SELECT
    request_path,
    regexp_split_to_array(request_path, '/') AS path_parts
FROM access_logs
WHERE request_path != '/login';

输出结果：

 request_path     |      path_parts
------------------+-----------------------
 /api/users/123   | {"",api,users,123}
 /admin/dashboard | {"",admin,dashboard}
 /api/data.json   | {"",api,data.json}

📊 性能优化与最佳实践

索引优化

sql

-- 为常用的模式匹配创建索引
CREATE INDEX idx_users_username_pattern ON users
USING gin (username gin_trgm_ops);

-- 对于前缀匹配，可以使用 B-tree 索引
CREATE INDEX idx_users_email_prefix ON users (email text_pattern_ops);

性能对比测试

sql

-- 测试不同匹配方法的性能
EXPLAIN ANALYZE
SELECT * FROM users WHERE username LIKE 'john%';

EXPLAIN ANALYZE
SELECT * FROM users WHERE username ~ '^john';

EXPLAIN ANALYZE
SELECT * FROM users WHERE username SIMILAR TO 'john%';

性能建议

优先使用 LIKE：对于简单模式匹配，LIKE 通常最快
合理使用索引：为频繁查询的模式创建适当的索引
避免前导通配符：%pattern 无法使用标准索引
考虑全文搜索：对于复杂文本搜索，考虑使用 PostgreSQL 的全文搜索功能

🔄 实际应用场景总结

用户输入验证

sql

-- 综合验证系统
CREATE OR REPLACE FUNCTION validate_user_input(
    p_username VARCHAR(50),
    p_email VARCHAR(100),
    p_phone VARCHAR(20)
) RETURNS TABLE (
    field_name TEXT,
    is_valid BOOLEAN,
    error_message TEXT
) AS $$
BEGIN
    -- 验证用户名
    RETURN QUERY SELECT
        'username'::TEXT,
        p_username SIMILAR TO '[a-zA-Z][a-zA-Z0-9_]{2,49}',
        CASE WHEN p_username SIMILAR TO '[a-zA-Z][a-zA-Z0-9_]{2,49}'
             THEN 'Valid'
             ELSE 'Username must start with letter, 3-50 characters'
        END;

    -- 验证邮箱
    RETURN QUERY SELECT
        'email'::TEXT,
        p_email ~ '^[A-Za-z0-9._%+-]+@[A-Za-z0-9.-]+\.[A-Za-z]{2,}$',
        CASE WHEN p_email ~ '^[A-Za-z0-9._%+-]+@[A-Za-z0-9.-]+\.[A-Za-z]{2,}$'
             THEN 'Valid'
             ELSE 'Invalid email format'
        END;

    -- 验证电话号码
    RETURN QUERY SELECT
        'phone'::TEXT,
        p_phone ~ '^\d{3}-\d{3}-\d{4}$',
        CASE WHEN p_phone ~ '^\d{3}-\d{3}-\d{4}$'
             THEN 'Valid'
             ELSE 'Phone must be in format xxx-xxx-xxxx'
        END;
END;
$$ LANGUAGE plpgsql;

-- 使用验证函数
SELECT * FROM validate_user_input('john_doe', '[email protected]', '123-456-7890');

数据清理和标准化

sql

-- 清理和标准化电话号码
UPDATE users
SET phone = regexp_replace(
    regexp_replace(phone, '[^\d]', '', 'g'),
    '(\d{3})(\d{3})(\d{4})',
    '\1-\2-\3'
)
WHERE phone IS NOT NULL;

关键要点总结

LIKE 操作符：简单快速，适合基本通配符匹配
SIMILAR TO：SQL 标准，结合了 LIKE 和正则表达式的特性
POSIX 正则表达式：功能最强大，适合复杂模式匹配
安全性考虑：处理用户输入时要小心，避免正则表达式攻击
性能优化：合理使用索引，选择合适的匹配方法

通过掌握这三种模式匹配方法，你可以在 PostgreSQL 中高效地处理各种文本匹配需求，从简单的用户搜索到复杂的数据验证和清理任务。

PostgreSQL 模式匹配完全指南 ​

📋 概述 ​

🔍 LIKE 操作符详解 ​

基本语法 ​

通配符规则 ​

实战示例：用户搜索功能 ​

场景 1：模糊搜索用户名 ​

场景 2：搜索特定格式的邮箱 ​

场景 3：搜索特定格式的电话号码 ​

转义字符的使用 ​

ILIKE：不区分大小写的匹配 ​

🔧 SIMILAR TO 操作符详解 ​

基本语法 ​

支持的正则表达式元字符 ​

实战示例：数据验证 ​

场景 1：验证邮箱格式 ​

场景 2：验证复杂模式 ​

场景 3：提取数据模式 ​

SUBSTRING 函数与 SIMILAR TO ​

🚀 POSIX 正则表达式详解 ​

基本操作符 ​

实战示例：日志分析系统 ​

场景 1：识别可疑 IP 地址 ​

场景 2：提取 API 端点 ​

场景 3：分析用户代理 ​

高级正则表达式函数 ​

regexp_count：计算匹配次数 ​

regexp_replace：文本替换 ​

regexp_split_to_array：字符串分割 ​

📊 性能优化与最佳实践 ​

索引优化 ​

性能对比测试 ​

🔄 实际应用场景总结 ​

用户输入验证 ​

数据清理和标准化 ​

PostgreSQL 模式匹配完全指南

📋 概述

🔍 LIKE 操作符详解

基本语法

通配符规则

实战示例：用户搜索功能

场景 1：模糊搜索用户名

场景 2：搜索特定格式的邮箱

场景 3：搜索特定格式的电话号码

转义字符的使用

ILIKE：不区分大小写的匹配

🔧 SIMILAR TO 操作符详解

基本语法

支持的正则表达式元字符

实战示例：数据验证

场景 1：验证邮箱格式

场景 2：验证复杂模式

场景 3：提取数据模式

SUBSTRING 函数与 SIMILAR TO

🚀 POSIX 正则表达式详解

基本操作符

实战示例：日志分析系统

场景 1：识别可疑 IP 地址

场景 2：提取 API 端点

场景 3：分析用户代理

高级正则表达式函数

regexp_count：计算匹配次数

regexp_replace：文本替换

regexp_split_to_array：字符串分割

📊 性能优化与最佳实践

索引优化

性能对比测试

🔄 实际应用场景总结

用户输入验证

数据清理和标准化