通配符主要用在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（或其他utf8mb4的排序规则）时，LIKE中的_通配符是按“字符”（character）匹配，而不是按“字节”（byte）。因此，对于中文等多字节字符，_依然能正确匹配单个汉字。但是在某些旧版本或特殊配置下可能存在差异。我们建议大家在关键场景进行实测验证。在二进制排序规则下，如：WHERE name LIKE '_' COLLATE utf8mb4_bin，_将严格匹配单个字符，但是对于多字节字符集，这可能带来预期外的结果，我们需要特别注意字符集配置。极端情况示例：在某些数据库和排序规则下，_匹配单个逻辑字符的行为可能受组合字符影响。例如：一个emoji，如 “😀”(U+1F600)，通常被LIKE '_'匹配为一个字符；而像“é”这样的组合字符（'e' + U+0301），在一些“敏感”的排序规则下，LIKE '_'可能将其匹配为一个字符，但是在“不敏感”的规则下，可能与'e'比较时结果可能为真。

二、特殊字符转义：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. ESCAPE是ANSI SQL标准，具有最好的可移植性。我们需要特别指出：在ANSI SQL标准中，LIKE没有默认的转义字符，我们必须使用ESCAPE子句来转义。某些数据库（如：MySQL）默认使用反斜杠\转义是其扩展功能，并且受sql_mode影响（例如，在NO_BACKSLASH_ESCAPES模式下，\就只是普通字符）。因此始终显式使用ESCAPE是最安全的推荐做法。
3. 数据库差异：
• MySQL：默认情况下，LIKE '50\%'中的\会转义%，但是在NO_BACKSLASH_ESCAPES模式下不会；
• PostgreSQL：LIKE '50\%'中的%仍然是通配符，我们必须使用ESCAPE显式指定转义；
• SQL Server：行为类似PostgreSQL，默认不转义。
4. 在编写LIKE模式时，我们需要区分SQL字符串转义和LIKE通配符转义。例如，在大多数SQL客户端中，'\\'表示一个反斜杠字符。如果该反斜杠用于ESCAPE，则它后面的%或_会被视为字面值。为避免混淆，我们推荐使用非反斜杠字符（如：!、#）作为ESCAPE字符。

三、注意事项

1、大小写敏感性

• 是否区分大小写，取决于数据库系统及其排序规则（collation）：MySQL默认使用不区分大小写的排序规则（如：utf8_general_ci），LIKE '张%'会匹配"张三"，但是不会匹配"ZHANG SAN"（因为中英文不同）。如果字段为英文，如：name LIKE 'john%'，则可能匹配"John"。强制区分大小写：使用BINARY，如：WHERE BINARY name LIKE 'John%'。PostgreSQL/SQL Server/Oracle默认区分大小写。
• 我们建议：明确业务需求，必要时使用UPPER()/LOWER()统一大小写，或调整排序规则。

2、性能影响与优化建议

• 尾部通配符（如：'技术%'）：如果字段上有索引，通常可以使用索引（数据库优化器支持前缀匹配）。注意：只有当LIKE模式以固定字符串开头（如：'abc%'）时，B-tree索引才可能被使用。如果模式以通配符开头（'%abc'）、或字段被函数包裹（如：UPPER(col)），则索引通常无法使用。
• 前导通配符（如：'%技术' 或 '%伟%'）：会导致索引失效，触发全表扫描，在大数据表中性能显著下降。
• 双端通配符（如：'%伟%'）：同样无法使用索引，性能最差。
• NOT LIKE的潜在性能问题：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仅在需要模糊匹配时使用。

性能对比：

• LIKE 'abc%'：索引可能生效，复杂度O(log n)；
• LIKE '%abc'：全表扫描，复杂度O(n)；
• LIKE '%abc%'：全表扫描，复杂度O(n)。

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

1. 数据类型不匹配：如：WHERE phone LIKE '138%'，但是phone是数值类型（隐式类型转换）。例如：WHERE varchar_column LIKE 123%（数字123被隐式转换为字符串）可能无法使用索引，而WHERE varchar_column LIKE '123%'则可以。
2. 使用函数或表达式：如：WHERE UPPER(name) LIKE 'JOHN%'。
3. 对计算列查询：如：WHERE CONCAT(first_name, last_name) LIKE '张%'。

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;

常见陷阱与解决方法：

-- 陷阱1：LIKE '%'会匹配所有非NULL值（包括空字符串），但是不匹配NULL
SELECT * FROM employees WHERE name LIKE '%';

-- 陷阱2：直接使用LIKE NULL总是返回UNKNOWN
SELECT * FROM users WHERE name LIKE NULL; -- 错误用法，不会返回任何行

-- 正确做法：显式检查NULL
SELECT * FROM users WHERE name IS NULL OR name LIKE '%搜索词%';

-- 陷阱3：空字符串与NULL的区别
SELECT '' LIKE '%';   -- TRUE（空字符串匹配 %）
SELECT NULL LIKE '%'; -- UNKNOWN（不返回行）

注意：空字符串''是有效值，会被LIKE '%'匹配；而NULL表示"未知"，任何LIKE比较结果均为UNKNOWN，不会出现在结果集中。

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（不匹配）

-- 实际应用：搜索时可能需要考虑全角半角问题
SELECT * FROM products 
WHERE product_name LIKE '%笔记本%' 
   OR product_name LIKE '%笔记本%'; -- 全角"笔记"和半角"笔记"可能都需要

四、高级主题与替代方法

1、字符集范围匹配（非标准语法）

某些数据库扩展了LIKE语法，支持简单的字符集范围匹配，但是这不是SQL标准，可移植性差。

-- SQL Server/MS Access语法示例：
-- 匹配以A、B或C开头的姓名
SELECT * FROM employees WHERE name LIKE '[ABC]%';

-- 匹配不以A、B或C开头的姓名
SELECT * FROM employees WHERE name LIKE '[^ABC]%';

-- 匹配以A-F开头，第二个字符为0-9的字符串
SELECT * FROM products WHERE code LIKE '[A-F][0-9]%';

⚠️ 注意：MySQL的LIKE不支持[ ]语法，但是提供了REGEXP进行正则表达式匹配。

2、正则表达式匹配

当LIKE无法满足复杂模式匹配需求时，我们可以使用正则表达式。正则表达式，虽然功能强大，但是通常无法利用普通B-tree索引，性能开销远高于LIKE 'prefix%'，我们应谨慎用在高频查询或大数据量场景。

-- 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/RLIKE，PostgreSQL: ~，Oracle: REGEXP_LIKE）。

3、全文检索

对于自然语言的模糊搜索、分词搜索，全文检索是性能远优于LIKE '%...%'的解决方法：

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

4、综合应用示例

-- 场景：搜索包含特定内容但排除某些模式的文档
-- 查找包含"测试"但不以"废弃"开头，且不包含"[废弃]"的文档
SELECT * FROM documents 
WHERE content LIKE '%测试%' 
  AND content NOT LIKE '废弃%'
  AND content NOT LIKE '%[废弃]%'
  AND (content IS NOT NULL); -- 显式排除NULL