PostgreSQL 通配符指南：解锁 LIKE 查询的魔法 - % 与 _ 详解

在 PostgreSQL 的数据查询中，精确匹配往往不够灵活。这时 LIKE 操作符配合通配符 % 和 _ 就成为模糊查询的利器。它们能高效筛选出符合特定模式的数据，本文将深入解析它们的用法、区别与性能优化技巧。

一、核心概念：通配符与 LIKE

• LIKE 操作符： 用于在 WHERE 子句中搜索符合特定模式的字符串。
• 通配符： 代替字符串中的未知字符：
  • %：匹配任意数量（0个或多个）的任意字符。
  • _：匹配单个任意字符。

二、% 通配符详解：匹配任意长度字符

• 作用： 表示“任何内容，长度不限”。
• 场景举例：

  -- 查找所有以 "张" 开头的姓名
  SELECT * FROM employees WHERE name LIKE '张%';
  
  -- 查找所有包含 "com" 的邮箱地址
  SELECT * FROM users WHERE email LIKE '%com%';
  
  -- 查找所有以 ".jpg" 结尾的文件名
  SELECT * FROM files WHERE filename LIKE '%.jpg';
  
  -- 查找所有中间包含 "admin" 的用户名
  SELECT * FROM accounts WHERE username LIKE '%admin%';
  

三、_ 通配符详解：精准匹配单个字符

• 作用： 表示“恰好一个任意字符”。
• 场景举例：

  -- 查找类似 "A1B", "A2B", "AXB" 的产品代码（第2位任意）
  SELECT * FROM products WHERE product_code LIKE 'A_B';
  
  -- 查找名字为 3 个字符且以 "小" 结尾的员工
  SELECT * FROM employees WHERE name LIKE '__小'; 
  
  -- 查找手机号格式为 138-XXXX-1234 的用户（X为任意数字）
  SELECT * FROM customers WHERE phone LIKE '138-____-1234';
  

四、组合使用 % 与 _ ：构建复杂模式

-- 查找第2个字符是 "o"，且以 "le" 结尾的单词
SELECT * FROM words WHERE word LIKE '_o%le';

-- 查找以 "B" 开头、第3个字符是 "k" 的客户名称
SELECT * FROM customers WHERE name LIKE 'B_k%';

五、性能关键点与优化建议

1. 谨慎使用开头通配符 %：

   • 查询 LIKE '%keyword' 会导致 全表扫描，无法有效利用索引。
   • 优化策略： 尽可能将 % 放在模式末尾（'keyword%'），此时可以利用索引加速。

2. 索引利用：

   • LIKE 'prefix%' 可利用 B-tree 索引。
   • LIKE '%suffix' 或 LIKE '%infix%' 通常 无法使用索引。

3. 特殊字符转义：

   • 若需搜索包含 % 或 _ 的字符串，使用 ESCAPE 子句：

     -- 查找包含 "20%" 的折扣信息
     SELECT * FROM discounts WHERE description LIKE '%20!%%' ESCAPE '!';
     

4. 大小写敏感处理：

   • LIKE 默认区分大小写。
   • 需不区分大小写时，使用 ILIKE：

     SELECT * FROM products WHERE name ILIKE '%apple%'; -- 匹配 "Apple", "apple" 等
     

六、% 与 _ 核心区别总结

七、实战建议

• 明确需求： 先确定需要的是“任意长度”匹配(%)还是“单字符”匹配(_)。
• 模式优化： 尽量让模式以具体字符开头，避免前导通配符。
• 大小写注意： 根据业务需求选择 LIKE 或 ILIKE。
• 转义必要字符： 当数据本身包含 % 或 _ 时，必须使用 ESCAPE。

总结

掌握 % 和 _ 通配符是高效使用 PostgreSQL 进行模糊查询的基础。% 擅长处理长度不确定的匹配，而 _ 精确定位单个字符位置。合理组合它们能解决大部分模式匹配需求，但务必注意前导通配符对性能的影响以及大小写敏感性问题。善用这些技巧，让你的数据查询更加灵活强大！

提示： 对于极端复杂的模式匹配需求（如正则表达式），可考虑 PostgreSQL 强大的 ~ 操作符和正则表达式功能。但对于日常的模糊查询，% 和 _ 配合 LIKE/ILIKE 通常是最高效简洁的选择。