SQL LIKE通配符%与_高效使用指南：从场景到性能优化与避坑

通配符主要用于SQL的模糊查询，必须与LIKE（或NOT LIKE）操作符配合使用。不同数据库对通配符的支持略有差异。在标准SQL中，最常用的是%和_。此外，当我们需要搜索包含通配符本身作为普通字符的数据时，可以通过ESCAPE关键字来处理。本文将对其进行详细拆解。

一、常用通配符

1、%通配符：匹配任意长度的字符（包括零个字符）

• % 可以匹配任意数量（0个或多个）的任意字符（包括字母、数字、符号、空格等）。
• 它可以出现在模式字符串的开头、中间或结尾。

使用示例：

-- 1.匹配以“张”开头的所有姓名（如：张三、张伟、张小明）
SELECT * FROM employees WHERE name LIKE '张%';

-- 2.匹配以“技术”结尾的所有部门（如：后端技术、AI技术）
SELECT * FROM departments WHERE dept_name LIKE '%技术';

-- 3.匹配包含“伟”字的所有姓名（如：张伟、李伟、王宏伟）
SELECT * FROM employees WHERE name LIKE '%伟%';

-- 4.匹配以“李”开头、以“华”结尾的姓名（如：李华、李爱华、李明华）
SELECT * FROM employees WHERE name LIKE '李%华';

注意：'李%华'要求至少包含“李”和“华”两个字符，中间可以有任意内容（包括空内容，即“李华”也匹配）。%匹配零字符的情况也很常见，例如：SELECT 'abc' LIKE 'a%c'; -- TRUE 中，%匹配的是'b'；而SELECT 'ac' LIKE 'a%c'; -- TRUE 中，%匹配的是零个字符（空字符串）。

2、_通配符：匹配单个任意字符

• _ 严格匹配一个且仅一个字符，不能匹配零个或多个字符。
• 它常用于对字符串长度或特定位置字符进行约束。

使用示例：

-- 1.匹配恰好3个字符的姓名，且第2个字符为“三”（如：张三丰、李三娘、王三思）
-- 不匹配：张小三（中间不是“三”）、李三（长度不足）、欧阳三丰（长度超）
SELECT * FROM employees WHERE name LIKE '_三_';

-- 2.匹配以"张"开头、总长度为2的姓名（如：张三、张四）
-- 不匹配：张伟明（3字）
SELECT * FROM employees WHERE name LIKE '张_';

-- 3.匹配第2个字符为"丽"的3字姓名（如：王丽娟、张丽娜）
SELECT * FROM employees WHERE name LIKE '_丽_';

-- 4.匹配邮箱格式为：前3位任意 + “@xx.com”（如：abc@xx.com）
SELECT * FROM users WHERE email LIKE '___@xx.com';

⚠️ 注意：_匹配的是单个逻辑字符，而非单个字节。在支持多字节字符的字符集（如MySQL的utf8mb4）及相应排序规则下，_通常按“逻辑字符”匹配，因此可以正确匹配单个中文字符、英文字母或emoji。具体行为（特别是对组合字符、代理对等复杂Unicode字符的处理）受数据库系统、版本及排序规则影响。例如，在MySQL中，当字段使用utf8mb4_bin等排序规则时，LIKE中的_通配符是按字符匹配的。但在某些旧版本或特殊配置下可能存在差异，建议关键场景进行实测验证。在二进制排序规则下，如 WHERE name LIKE '_' COLLATE utf8mb4_bin，_将严格匹配单个字符，但这对于多字节字符集可能带来预期外的结果，需要特别注意。

二、特殊字符转义：ESCAPE关键字

当需要将%或_作为普通字符而非通配符进行匹配时，需要使用ESCAPE指定转义字符。

使用示例：

-- 1.查询产品名称中包含字面值“50%”的商品
-- 使用反斜杠\作为转义字符
SELECT * FROM products WHERE product_name LIKE '%50\%' ESCAPE '\';

-- 2.查询用户名中包含字面值“a_b”的用户
-- 使用#作为转义字符
SELECT * FROM users WHERE username LIKE '%a#_b%' ESCAPE '#';

-- 3.查询路径中包含“C:\Windows”的记录
-- 注意：需要转义反斜杠本身
SELECT * FROM files WHERE path LIKE '%C:\\Windows%' ESCAPE '\';

-- 4.也可使用其他未在模式中出现的字符，如：!
SELECT * FROM logs WHERE message LIKE 'Error!_%' ESCAPE '!';

说明：

1. 转义字符选择：可以是任意单字符（如\、#、!、|等），只要它在模式中未被用作普通字符即可。
2. ANSI SQL标准：ESCAPE是ANSI SQL标准，具有最好的可移植性。在标准中，LIKE没有默认的转义字符，必须使用ESCAPE子句。某些数据库（如MySQL）默认使用反斜杠\转义是其扩展功能，并且受sql_mode影响。因此，始终显式使用ESCAPE是最安全的做法。
3. 数据库差异：
   • MySQL：默认情况下，LIKE '50\%'中的\会转义%，但在NO_BACKSLASH_ESCAPES模式下不会。
   • PostgreSQL/SQL Server：行为类似，LIKE '50\%'中的%仍然是通配符，必须使用ESCAPE显式指定。
4. 避免混淆：在编写LIKE模式时，需区分SQL字符串字面量转义和LIKE通配符转义。为避免混淆，推荐使用非反斜杠字符（如!、#）作为ESCAPE字符。

三、注意事项与最佳实践

1、大小写敏感性

LIKE操作是否区分大小写，完全取决于数据库系统的排序规则（collation）。

• MySQL：默认使用不区分大小写的排序规则（如utf8mb4_general_ci），LIKE 'john%'会匹配"John"。如需强制区分，可使用WHERE BINARY name LIKE 'John%'。
• PostgreSQL/SQL Server/Oracle：默认区分大小写。

建议：明确业务需求，必要时使用UPPER()/LOWER()函数统一大小写，或调整表/列的排序规则。

2、性能影响与优化建议

模糊查询对数据库索引的利用情况直接决定其性能：

• 尾部通配符（如 '技术%'）：如果模式以固定字符串开头，且字段上有B-tree索引，通常可以使用索引进行高效的前缀扫描。
• 前导通配符（如 '%技术'）或双端通配符（如 '%伟%'）：会导致索引失效，引发全表扫描，在大数据表中性能极差。
• NOT LIKE：通常和LIKE '%...%'一样，无法有效利用索引。

优化建议：

1. 避免前导%：尽量避免以%开头的查询。若必须使用，可考虑：
   • 建立反转索引：对于以%abc结尾的查询，新增一列存储反转后的值并建索引，查询WHERE reverse_column LIKE 'cba%'。
   • 使用函数索引：在支持表达式索引的数据库（如PostgreSQL, Oracle）中，对REVERSE(column)建立索引。
2. 使用专业搜索方案：对高频、复杂的文本模糊查询，考虑：
   • 全文索引：如MySQL的FULLTEXT、PostgreSQL的tsvector。
   • 专用搜索引擎：如Elasticsearch、Solr。
3. 精确匹配用=：在精确匹配时（如 WHERE name LIKE '张三'），应优先使用等值操作符=，它对优化器更友好且可读性更高。

索引失效的特殊情况：即使使用LIKE 'abc%'，以下情况也可能导致索引失效：

• 数据类型不匹配：如 WHERE phone LIKE '138%'，但phone是数值类型（引发隐式转换）。
• 使用函数或表达式：如 WHERE UPPER(name) LIKE 'JOHN%'。

3、关于NULL值的处理

NULL值在LIKE或NOT LIKE比较中，结果均为UNKNOWN，不会被选中。

-- 查询姓名中不包含“伟”字的员工（且name不为NULL）
SELECT * FROM employees WHERE name NOT LIKE '%伟%';

-- 如果需要包含NULL值的记录，必须显式处理：
SELECT * FROM employees WHERE name NOT LIKE '%伟%' OR name IS NULL;

常见陷阱：

• LIKE '%' 会匹配所有非NULL值（包括空字符串''），但不匹配NULL。
• 直接使用 LIKE NULL 总是返回UNKNOWN，不会返回任何行。正确做法是显式检查 IS NULL。

4、操作符优先级与括号使用

AND的优先级高于OR，不加括号可能导致意外逻辑。

-- 危险：可能返回意外结果
SELECT * FROM users WHERE name LIKE '张%' OR name LIKE '李%' AND age > 30;
-- 等价于：name LIKE '张%' OR (name LIKE '李%' AND age > 30)

-- 安全：使用括号明确意图
SELECT * FROM users WHERE (name LIKE '张%' OR name LIKE '李%') AND age > 30;

5、字符集与排序规则的影响

排序规则可能影响字符的等价性判断，例如在全角/半角字符匹配时：

-- 在utf8_general_ci等不区分全角半角的排序规则中
SELECT 'Ａ' LIKE 'A'; -- 可能返回1（匹配）
SELECT 'ａ' LIKE 'A'; -- 可能返回1（匹配）

-- 如需精确区分，可使用二进制排序规则
SELECT 'Ａ' LIKE 'A' COLLATE utf8mb4_bin; -- 返回0（不匹配）

四、高级替代方案

当LIKE无法满足需求或性能成为瓶颈时，可考虑以下方案：

1、正则表达式匹配

功能强大，但通常无法利用B-tree索引，性能开销高，慎用于大数据量或高频查询。

-- MySQL正则示例：匹配以数字开头的字符串
SELECT * FROM users WHERE username REGEXP '^[0-9]';

-- PostgreSQL正则示例：匹配有效的电子邮件格式
SELECT * FROM users WHERE email ~ '^[A-Za-z0-9._%+-]+@[A-Za-z0-9.-]+\.[A-Z]{2,}$';

⚠️ 注意：不同数据库的正则语法和函数名差异很大（MySQL:REGEXP，PostgreSQL:~，Oracle:REGEXP_LIKE）。

2、全文检索

对于自然语言的分词搜索，全文检索是性能远优于LIKE '%...%'的解决方案。

-- MySQL全文检索示例
ALTER TABLE articles ADD FULLTEXT INDEX ft_index (title, content);
SELECT * FROM articles WHERE MATCH(title, content) AGAINST('数据库 优化' IN NATURAL LANGUAGE MODE);

3、综合应用示例

-- 场景：搜索包含特定内容但排除某些模式的文档
SELECT * FROM documents
WHERE content LIKE '%测试%'
  AND content NOT LIKE '废弃%'
  AND content NOT LIKE '%[废弃]%'
  AND content IS NOT NULL; -- 显式排除NULL值