SQL LIKE 通配符使用教程：模式匹配实战 – wiki基地

作者： /

SQL LIKE 通配符使用教程：模式匹配实战

在数据库查询中，我们经常需要根据特定的模式来搜索文本数据，而不是仅仅进行精确匹配。例如，查找所有名字以“张”开头的人，或所有地址中包含“街”的记录，抑或是查找所有产品描述中含有特定关键词但位置不固定的商品。这时，SQL 的 LIKE 运算符就成为了实现这种“模糊”或“模式”匹配的强大工具。

LIKE 运算符与 WHERE 子句结合使用，用于在列中搜索指定的模式。它不是用于比较两个值是否相等，而是比较列中的值是否符合某个预设的字符串模式。这种模式是通过通配符（Wildcard Characters）来定义的。

本教程将带你深入了解 SQL LIKE 运算符及其配套的通配符，通过详细的例子和解释，让你掌握如何在各种场景下进行高效的模式匹配实战。

1. LIKE 运算符基础

LIKE 运算符总是与 WHERE 子句一起使用，其基本语法如下：

sql
SELECT column1, column2, ...
FROM table_name
WHERE column_name LIKE pattern;

其中：
* column_name：你想要进行模式匹配的列。这通常是一个文本类型的列（如 VARCHAR, TEXT 等）。
* pattern：你想要匹配的字符串模式。这个模式字符串可以包含通配符。

SQL 标准定义了两种主要的通配符：

  • % (百分号)：代表零个、一个或多个字符的任意序列。
  • _ (下划线)：代表恰好一个字符。

接下来，我们将详细探讨这两个通配符的使用。

2. 通配符的详细使用

2.1 % 通配符：匹配任意长度的字符序列

% 是最常用的通配符，它非常灵活，可以代表空字符串、单个字符或任意多个字符。

  • 匹配以特定字符串开头：
    如果你想查找所有以某个字符串开头的记录，可以将该字符串放在模式的开头，后面跟随 %。

    示例： 查找所有姓“王”的客户。假设客户表名为 Customers，姓名列为 CustomerName。

    sql
    SELECT CustomerID, CustomerName
    FROM Customers
    WHERE CustomerName LIKE '王%';
    这条查询会返回 CustomerName 字段值为 ‘王芳’, ‘王强’, ‘王小明’ 等所有以“王”开头的记录。

  • 匹配以特定字符串结尾：
    如果你想查找所有以某个字符串结尾的记录，可以将 % 放在模式的开头，后面跟随该字符串。

    示例： 查找所有邮箱地址以 @example.com 结尾的用户。假设用户表名为 Users，邮箱列为 Email。

    sql
    SELECT UserID, Email
    FROM Users
    WHERE Email LIKE '%@example.com';
    这条查询会返回 Email 字段值为 ‘[email protected]’, ‘[email protected]’ 等所有以 @example.com 结尾的记录。

  • 匹配包含特定字符串的记录：
    如果你想查找所有包含某个子字符串的记录，可以将该子字符串放在两个 % 之间。

    示例： 查找所有地址中包含“街”或“路”的客户。这里需要使用 OR 连接两个 LIKE 条件。假设客户表名为 Customers，地址列为 Address。

    sql
    SELECT CustomerID, CustomerName, Address
    FROM Customers
    WHERE Address LIKE '%街%' OR Address LIKE '%路%';
    这条查询会返回 Address 字段值如 ‘南京路100号’, ‘北京西街3号’, ‘上海市延安中路’ 等所有包含“街”或“路”的记录。

  • 精确匹配（无通配符）：
    虽然 LIKE 主要用于模式匹配，但如果不使用任何通配符，LIKE 的行为就等同于 = 运算符，执行精确匹配。

    示例： 查找名字恰好是“李华”的客户。

    sql
    SELECT CustomerID, CustomerName
    FROM Customers
    WHERE CustomerName LIKE '李华';
    这与 WHERE CustomerName = '李华' 的效果相同。在实际应用中，精确匹配通常更倾向于使用 =，因为其意图更明确，且在某些数据库系统中，= 可能比无通配符的 LIKE 性能略优。

2.2 _ 通配符：匹配恰好一个字符

_ 通配符代表一个且仅一个任意字符。它在需要匹配固定长度或在特定位置有未知字符时非常有用。

  • 匹配固定长度的字符串：
    如果你知道字符串的长度，并想匹配任意字符组合，可以使用多个 _。

    示例： 查找所有恰好由三个字符组成的姓名的客户。

    sql
    SELECT CustomerID, CustomerName
    FROM Customers
    WHERE CustomerName LIKE '___';
    这条查询会返回 CustomerName 字段值如 ‘张三’, ‘李四’, ‘王五’ (如果数据库按字符计数，这里是三个字符) 等所有恰好是三个字符长度的记录。注意：在某些字符集（如UTF-8）下，一个中文字可能被视为多个字节，但大多数现代数据库的 LIKE 操作在处理常用字符集时，_ 通常按一个“字符”单元（而非字节）计算。不过为了严谨，最好在特定数据库环境下测试确认。对于英文字符和数字，一个字符就是一个 _。

  • 匹配在特定位置有未知字符的模式：
    你可以将 _ 放置在模式中的特定位置，以代表该位置的任意单个字符。

    示例： 查找所有第二个字是“小”的客户。

    sql
    SELECT CustomerID, CustomerName
    FROM Customers
    WHERE CustomerName LIKE '_小%';
    这条查询会返回 CustomerName 字段值如 ‘张小明’, ‘李小平’, ‘王小丽’ 等所有第二个字是“小”的记录。第一个 _ 代表第一个字，小 是第二个字，% 代表第三个字及以后的任意字符（包括没有）。

    示例： 查找所有名字是两个字，且第二个字是“明”的客户。

    sql
    SELECT CustomerID, CustomerName
    FROM Customers
    WHERE CustomerName LIKE '_明';
    这条查询只会返回名字恰好是两个字，且第二个字是“明”的记录，如 ‘张明’, ‘李明’, ‘王明’。

3. 组合使用通配符

% 和 _ 通配符可以结合使用，以构建更复杂的模式。

示例： 查找所有邮政编码符合“四位数字，然后一个未知字符，然后两位数字”格式的记录。例如：’1000A10′, ‘2345B99’。假设表名为 Addresses，邮编列为 ZipCode。

sql
SELECT AddressID, ZipCode
FROM Addresses
WHERE ZipCode LIKE '_____%__'; -- 四个下划线，一个百分号（任意字符序列），两个下划线
错误示例与解释： 上面示例中的模式 '_____%__' 实际上是：四个任意字符 + 零个或多个任意字符 + 两个任意字符。这并不能保证中间只有一个未知字符，且总长度不确定。

正确示例与解释： 要匹配“四位数字，然后一个未知字符，然后两位数字”，模式应该是：

sql
SELECT AddressID, ZipCode
FROM Addresses
WHERE ZipCode LIKE '____#__'; -- 四个下划线，一个下划线，两个下划线
这个模式 '____#__' 代表：
* 第一个 _：代表第一个字符
* 第二个 _：代表第二个字符
* 第三个 _：代表第三个字符
* 第四个 _：代表第四个字符
* 第五个 _：代表第五个字符（恰好一个未知字符）
* 第六个 _：代表第六个字符
* 第七个 _：代表第七个字符

所以，'____#__' 模式匹配的是总长度恰好是 7 个字符，且中间第五个字符是任意字符的字符串。如果邮编列存储的是字符串，且格式固定，这个模式就能找到类似 ‘1000A10’, ‘2345B99’, ‘9876X54’ 这样的邮编。

另一个组合示例： 查找所有以“A”开头，倒数第二个字符是“Z”的字符串。

sql
SELECT ProductName
FROM Products
WHERE ProductName LIKE 'A%_Z';
这个模式 'A%_Z' 代表：
* A：必须以字符 ‘A’ 开头。
* %：接下来是零个或多个任意字符。
* _：然后是恰好一个任意字符（这个字符将是倒数第二个字符）。
* Z：最后必须是字符 ‘Z’（这个字符将是最后一个字符）。

所以，它会匹配 ‘AZ’, ‘AAZ’, ‘ABZ’, ‘APPLEAZ’, ‘DATA_Z’ 等字符串。

4. 排除模式：NOT LIKE

与大多数 SQL 运算符一样，LIKE 也可以通过 NOT 关键字进行否定。NOT LIKE 用于查找 不 符合指定模式的记录。

示例： 查找所有名字 不 以“张”开头的客户。

sql
SELECT CustomerID, CustomerName
FROM Customers
WHERE CustomerName NOT LIKE '张%';
这条查询会返回除了姓“张”之外的所有客户记录。

示例： 查找所有地址中 不 包含“街”或“路”的客户。

sql
SELECT CustomerID, CustomerName, Address
FROM Customers
WHERE Address NOT LIKE '%街%' AND Address NOT LIKE '%路%';
注意这里使用了 AND，因为要排除 所有 包含“街”的记录 并且 排除 所有 包含“路”的记录。如果使用 OR (Address NOT LIKE '%街%' OR Address NOT LIKE '%路%')，那会返回包含“街”但不含“路”的记录，以及包含“路”但不含“街”的记录，这不是我们想要的结果。

5. 处理特殊字符：ESCAPE 子句

到目前为止，我们看到 % 和 _ 是通配符。但是，如果你的数据中 本身 就包含字符 % 或 _，并且你想在模式中匹配这些 字面量 字符怎么办？直接写 % 或 _ 会被解释为通配符。这时就需要使用 ESCAPE 子句来指定一个转义字符。

ESCAPE 子句紧跟在 LIKE 模式之后，用于定义一个字符，该字符紧跟在它后面的通配符（% 或 _）或转义字符本身时，会使后面的字符被视为字面量而不是通配符或转义字符。

基本语法：

sql
SELECT column1, column2, ...
FROM table_name
WHERE column_name LIKE pattern ESCAPE 'escape_character';

其中：
* pattern：包含需要被转义的通配符（前面带有 escape_character）的模式字符串。
* escape_character：你指定的转义字符。这个字符可以是任何一个你认为在你的数据和模式中不会被误解的字符，但通常选择反斜杠 \ 或者其他不常用的符号，如 ! 或 $。

示例： 查找所有产品 SKU 包含字面量字符串 50% 的产品。例如 SKU: ‘ABC-50%-XYZ’, ‘PROD50%FINAL’。假设产品表为 Products，SKU 列为 SKU。

如果我们直接写 WHERE SKU LIKE '%50%%', 数据库会解释为：匹配包含 50，后面跟着 任意数量字符 的字符串，这显然不符合我们的需求（我们想要匹配 50 后面跟着 字面量 %）。

我们需要转义 %。让我们选择 ! 作为转义字符。

sql
SELECT ProductID, SKU
FROM Products
WHERE SKU LIKE '%50!%%' ESCAPE '!';
解释：
* %50：匹配任意字符序列，后跟字面量 50。
* !%：由于前面指定了 ESCAPE '!'，这里的 !% 不会被解释为通配符 %，而是被解释为字面量字符 %。
* %：最后一个 % 仍然是通配符，匹配字面量 % 后面的任意字符序列。

所以，整个模式 ' %50!%%' ESCAPE '!' 的意思是：匹配包含字面量字符串 50% 的任意字符串。

示例： 查找包含字面量字符串 user_ 的记录。假设日志表为 Logs，消息列为 Message。

如果我们直接写 WHERE Message LIKE '%user_%', 数据库会解释为：匹配包含 user，后跟 恰好一个任意字符 的字符串。这不符合需求。

让我们选择 \ 作为转义字符。

sql
SELECT LogID, Message
FROM Logs
WHERE Message LIKE '%user\_%' ESCAPE '\';
解释：
* %：匹配任意字符序列。
* user：匹配字面量字符串 user。
* \_：由于指定了 ESCAPE '\', 这里的 \_ 不会被解释为通配符 _，而是被解释为字面量字符 _。
* %：匹配字面量 _ 后面的任意字符序列。

所以，整个模式 ' %user\_%' ESCAPE '\' 的意思是：匹配包含字面量字符串 user_ 的任意字符串。

示例： 查找包含字面量转义字符本身的记录。假设我们选择了 ! 作为转义字符，现在想查找包含字面量字符 ! 的记录。

sql
SELECT DataID, Value
FROM Data
WHERE Value LIKE '%!!%' ESCAPE '!';
解释： !! 在 ESCAPE '!' 的上下文中，第一个 ! 是转义字符，它使其后面的第二个 ! 被解释为字面量字符 !。所以 !! 代表字面量字符 !。整个模式 ' %!!%' ESCAPE '!' 的意思是：匹配包含字面量字符 ! 的任意字符串。

记住，ESCAPE 子句非常重要，特别是当你需要在数据中搜索字面量的 % 或 _ 时。选择一个不会在你的数据和模式中引起混淆的字符作为转义字符是关键。

6. 大小写敏感性

LIKE 运算符的大小写敏感性不是由 SQL 标准严格定义的，而是取决于具体的数据库系统以及列的字符集和排序规则（collation）。

  • 默认不敏感的系统： 某些数据库系统（如默认配置的 MySQL 在 Windows 上）默认情况下 LIKE 是不区分大小写的。这意味着 'abc' LIKE 'ABC' 可能会返回 true。
  • 默认敏感的系统： 另一些系统（如 PostgreSQL，或者配置了区分大小写排序规则的 SQL Server）默认情况下 LIKE 是区分大小写的。这意味着 'abc' LIKE 'ABC' 会返回 false。

如果你需要确保 LIKE 操作的大小写敏感性符合你的预期，无论数据库的默认设置如何，有几种常见的处理方法：

  1. 使用函数强制大小写转换： 在进行 LIKE 比较之前，将列的值和模式字符串都转换为大写或小写。这是最通用的跨数据库方法。

    示例： 查找包含子字符串 “apple”，不区分大小写。

    sql
    SELECT ProductID, ProductName
    FROM Products
    WHERE LOWER(ProductName) LIKE '%apple%'; -- 将列值和模式都转为小写进行比较
    或者

    sql
    SELECT ProductID, ProductName
    FROM Products
    WHERE UPPER(ProductName) LIKE '%APPLE%'; -- 将列值和模式都转为大写进行比较
    这种方法虽然可靠，但可能会影响性能，因为它阻止了数据库对列使用标准索引（后面会详细讨论）。

  2. 利用数据库特定的函数或语法： 某些数据库提供了特定的函数或运算符来控制大小写敏感性。例如：

    • SQL Server 可以使用 COLLATE 子句指定排序规则。
    • PostgreSQL 可以使用 ILIKE 运算符进行不区分大小写的模式匹配 (WHERE ProductName ILIKE '%apple%';)。

选择哪种方法取决于你的具体数据库系统和对性能的要求。如果性能是关键因素，并且你需要不区分大小写的匹配，考虑数据库特定的解决方案或调整列的排序规则可能更优。

7. 性能考量与优化

LIKE 运算符虽然功能强大，但在处理大量数据时，其性能可能成为瓶颈。理解其性能特点对于编写高效的 SQL 查询至关重要。

  • 索引的使用： 标准的 B-tree 索引是数据库中最常见的索引类型，它对基于范围和前缀的查找非常高效。

    • 模式以非通配符开头 ('pattern%')： 当 LIKE 模式以非通配符字符开始时，数据库可以利用 B-tree 索引进行范围扫描。例如 WHERE CustomerName LIKE '王%' 可以利用 CustomerName 列上的索引快速定位到所有以“王”开头的记录。
    • 模式以通配符开头 ('%pattern', '_pattern')： 当 LIKE 模式以 % 或 _ 开头时，数据库 通常 无法使用标准的 B-tree 索引进行有效的查找。因为数据库不知道匹配的字符串从哪里开始，它可能需要扫描整个表（全表扫描）或索引（全索引扫描），检查每一条记录是否符合模式。这对于大型表来说是非常低效的。
    • 模式包含 % 在中间 ('pattern%pattern')： 类似于以 % 开头的模式，这种模式也难以利用标准的 B-tree 索引进行高效加速。
    • 使用 ESCAPE 的模式： 正确使用 ESCAPE 不会显著改变索引使用的基本规则。如果转义后的模式以非通配符开头，可能仍然可以使用索引。

  • 优化 LIKE 查询的策略：

    1. 避免在模式开头使用 % 或 _： 如果可能的话，重新设计你的查询或数据存储，以避免这种模式。例如，如果你经常需要搜索包含某个关键词的文本，但关键词位置不固定，考虑使用更专业的全文搜索技术（见下文）。
    2. 如果模式以非通配符开头，确保列上有索引： 对于 WHERE column LIKE 'prefix%' 这样的查询，在 column 列上创建 B-tree 索引将大大提高查询速度。
    3. 考虑使用全文搜索（Full-Text Search, FTS）： 对于复杂的文本搜索需求，特别是搜索长文本字段中的关键词，或者需要更高级的功能（如模糊匹配、同义词、相关性排序），数据库提供的全文搜索功能（如 SQL Server 的 Full-Text Search, MySQL 的 Full-Text Index, PostgreSQL 的 Text Search）是比 LIKE 更高效、更专业的选择。FTS 使用倒排索引等技术，非常适合处理非结构化文本数据。
    4. 创建函数式索引（Functional Index）： 如果你经常使用 LOWER() 或 UPPER() 进行不区分大小写的 LIKE 查询，并且你的数据库系统支持函数式索引（也称为表达式索引），你可以在 LOWER(column) 或 UPPER(column) 上创建索引。这样，即使使用了函数，查询也能利用索引。例如在 PostgreSQL 中：CREATE INDEX idx_customername_lower ON Customers (LOWER(CustomerName));
    5. 缩小搜索范围： 如果可以在 LIKE 之外使用其他条件（如日期范围、分类等）来先过滤掉大量不相关的记录，先执行这些过滤条件通常会提高整体性能。

8. LIKE vs. 正则表达式（Regular Expressions, RegEx）

在某些数据库系统（如 MySQL, PostgreSQL, Oracle）中，除了 LIKE，还支持使用正则表达式进行更强大的模式匹配。正则表达式提供了比 LIKE 通配符 (%, _) 更丰富、更灵活的模式匹配能力，例如：

  • 匹配字符集 ([0-9], [a-z])
  • 匹配重复次数 ({n}, {n,m})
  • 匹配模式的开始 (^) 和结束 ($)
  • 逻辑或 (|)
  • 分组和捕获

示例 (PostgreSQL 使用 ~ 进行 RegEx 匹配): 查找所有电话号码符合 NNN-NNN-NNNN 格式的记录（N 代表数字）。

sql
SELECT ContactInfo
FROM Users
WHERE ContactInfo ~ '^[0-9]{3}-[0-9]{3}-[0-9]{4}$';

这用 LIKE 几乎不可能实现或者非常复杂。

何时使用 LIKE，何时使用 RegEx？

  • 使用 LIKE： 当你的模式需求比较简单，只需要用到 % 和 _ 通配符时，优先使用 LIKE。LIKE 的语法更简单易懂，且在支持索引使用的场景下（模式以非通配符开头），性能通常优于 RegEx。
  • 使用 RegEx： 当你的模式非常复杂，LIKE 无法表达时，或者需要利用 RegEx 提供的更高级功能时，使用 RegEx。但是要注意，RegEx 匹配的计算成本通常比简单的 LIKE 高，且很少能利用标准索引进行加速（除非结合特定的全文搜索功能或表达式索引）。

9. 实战案例回顾与总结

让我们通过一些实际的查询场景来巩固 LIKE 的使用：

  • 查找所有描述包含“防水”或“耐用”的产品：
    sql
    SELECT ProductID, ProductName, Description
    FROM Products
    WHERE Description LIKE '%防水%' OR Description LIKE '%耐用%';

  • 查找所有客户姓氏是“张”、“李”、“王”之一的：
    sql
    SELECT CustomerID, CustomerName
    FROM Customers
    WHERE CustomerName LIKE '张%' OR CustomerName LIKE '李%' OR CustomerName LIKE '王%';
    -- 或者使用更简洁的 IN 结合 LEFT 函数，但不属于LIKE范围
    -- WHERE LEFT(CustomerName, 1) IN ('张', '李', '王');

  • 查找所有文件名是以字母开头，后面跟着三个字符，然后是.txt 结尾的文件记录： 假设表名为 Files，文件名列为 FileName。
    sql
    SELECT FileID, FileName
    FROM Files
    WHERE FileName LIKE '_%.txt'; -- 以一个任意字符开头，后面跟任意字符序列，最后以 .txt 结尾
    -- 如果要求文件名恰好是“字母+三个任意字符+.txt”，则需要指定长度
    -- WHERE FileName LIKE '____.txt'; -- 字母+三个任意字符+.txt，总共8个字符
    第一个例子 '_%.txt' 匹配如 ‘a.txt’, ‘ab.txt’, ‘abc.txt’, ‘abcd.txt’ 等以一个任意字符开头，并以 .txt 结尾的文件名。第二个例子 '____.txt' 匹配如 ‘abcd.txt’, ‘eFgh.txt’ (如果区分大小写) 这样恰好是四个字符加 .txt 的文件名。

  • 查找所有电子邮件地址是 gmail.com 或 hotmail.com 域名的用户：
    sql
    SELECT UserID, Email
    FROM Users
    WHERE Email LIKE '%@gmail.com' OR Email LIKE '%@hotmail.com';

10. 总结

SQL LIKE 运算符是进行文本模式匹配的基础工具。通过灵活运用 %（零个或多个字符）和 _（恰好一个字符）这两个通配符，我们可以构建各种模式来查找符合特定规则的字符串数据。

  • LIKE pattern：查找匹配模式的记录。
  • NOT LIKE pattern：查找不匹配模式的记录。
  • pattern ESCAPE 'escape_char'：指定一个转义字符，用于匹配字面量的 % 和 _。

虽然 LIKE 功能强大，但在使用时需要注意其性能特点，特别是以通配符开头的模式可能导致全表扫描。对于复杂的模式匹配或大规模文本数据搜索，考虑使用正则表达式或数据库提供的全文搜索功能可能更为合适。

掌握 LIKE 运算符及其通配符的使用，是进行高效 SQL 查询、从海量文本数据中提取有用信息的重要技能。通过不断实践和尝试不同的模式，你将能更加熟练地运用 LIKE 来满足各种数据查询需求。

希望这篇教程能帮助你全面理解和掌握 SQL LIKE 通配符的用法！在实际工作中，多加练习，结合具体的数据和查询场景，你会发现 LIKE 在文本数据处理中的强大之处。

发表评论

您的邮箱地址不会被公开。 必填项已用 * 标注

滚动至顶部