SQL LIKE 操作符：MySQL 模糊查询完全指南 – wiki基地

作者： /

SQL LIKE 操作符是 SQL 中用于模式匹配的强大工具，尤其在 MySQL 中，它提供了灵活的方式来执行模糊查询。本指南将详细介绍 LIKE 操作符的用法、通配符、性能考虑以及常见应用场景。

SQL LIKE 操作符：MySQL 模糊查询完全指南

在数据库查询中，我们经常需要根据不完全匹配的模式来检索数据，例如查找包含特定字符或以某个前缀开头的所有记录。这时，SQL 的 LIKE 操作符就显得尤为重要。它允许我们使用通配符来定义搜索模式，从而实现强大的模糊查询功能。

1. LIKE 操作符基础

LIKE 操作符与 WHERE 子句一起使用，用于在 SELECT, UPDATE, DELETE 语句中搜索列中的指定模式。

基本语法：

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

pattern 是一个字符串，可以包含以下两个特殊的通配符：

  • % (百分号)：匹配零个、一个或多个任意字符。
  • _ (下划线)：匹配一个任意字符。

2. 通配符详解与示例

2.1 % (百分号)

% 是最常用的通配符，可以匹配任意数量的字符（包括零个字符）。

示例：

  1. 查找以 “A” 开头的所有姓名：
    sql
    SELECT name
    FROM customers
    WHERE name LIKE 'A%';
    这将返回 “Alice”, “Anna”, “Adam” 等。

  2. 查找以 “son” 结尾的所有姓名：
    sql
    SELECT name
    FROM customers
    WHERE name LIKE '%son';
    这将返回 “Jackson”, “Johnson”, “Peterson” 等。

  3. 查找包含 “or” 的所有地址：
    sql
    SELECT address
    FROM suppliers
    WHERE address LIKE '%or%';
    这将返回 “New York”, “Oregon”, “Portland” 等。

  4. 查找不包含任何字符 (即空字符串) 或只包含数字的字符串 (结合其他条件)
    虽然 % 匹配零个或多个字符，但通常不会单独用于匹配空字符串，因为 LIKE '' 通常只匹配空字符串。更常见的是用于匹配存在某些模式的字符串。

2.2 _ (下划线)

_ 通配符匹配单个任意字符。它在需要精确控制匹配长度时非常有用。

示例：

  1. 查找第二个字母是 “a” 的所有姓名：
    sql
    SELECT name
    FROM employees
    WHERE name LIKE '_a%';
    这将返回 “Mary”, “David”, “Sarah” 等。

  2. 查找恰好有 5 个字符且以 “apple” 结尾的单词（注意，这里应为____e如果末尾为’e’，或者apple直接匹配）：
    sql
    SELECT product_name
    FROM products
    WHERE product_name LIKE '_____'; -- 匹配任何5个字符的字符串
    sql
    SELECT product_name
    FROM products
    WHERE product_name LIKE '____e'; -- 匹配任何4个字符后跟'e'的字符串 (总共5个字符)
    这两个示例展示了 _ 如何确保长度匹配。

  3. 查找第一个字母是 “B”，第三个字母是 “t” 的 4 字母单词：
    sql
    SELECT word
    FROM vocabulary
    WHERE word LIKE 'B_t_';
    这将返回 “Byte”, “Belt” 等。

2.3 组合使用通配符

% 和 _ 可以组合使用，以构建更复杂的模式。

示例：

  1. 查找以 “S” 开头，以 “e” 结尾，并且中间至少有一个字符的姓名：
    sql
    SELECT name
    FROM customers
    WHERE name LIKE 'S%e';
    这将返回 “Steve”, “Sophie”, “Simone” 等。

  2. 查找以 “j” 开头，第二个字母不是 “a”，后面任意多个字符的姓名：
    这种场景无法直接用 LIKE 和 % _ 组合实现“不是”的功能。通常需要结合 NOT LIKE 或正则表达式 (REGEXP / RLIKE)。

3. 转义通配符

如果你的搜索模式中需要包含 % 或 _ 这两个字符本身，而不是作为通配符使用，你需要使用 ESCAPE 子句来转义它们。

语法：

sql
SELECT column_name
FROM table_name
WHERE column_name LIKE pattern ESCAPE 'escape_character';

escape_character 是你选择的任何单个字符，它告诉 MySQL 跟在它后面的字符应该被视为字面值。

示例：

假设你想查找包含字符串 “50%” 的产品名称。

sql
SELECT product_name
FROM products
WHERE product_name LIKE '%50\%%' ESCAPE '\';
这里，\ 被指定为转义字符，所以 \% 将匹配字面值 %。

4. NOT LIKE 操作符

NOT LIKE 用于查找不符合指定模式的记录。

语法：

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

示例：

查找所有不以 “A” 开头的姓名：

sql
SELECT name
FROM customers
WHERE name NOT LIKE 'A%';

5. 性能考虑

虽然 LIKE 操作符非常灵活，但在处理大量数据时，它可能会影响查询性能。

  • 索引利用： 当 LIKE 模式以非通配符开头时（例如 LIKE 'prefix%'），MySQL 可以利用该列上的索引来加速查询。这被称为“前缀匹配”。
  • 无法利用索引：
    • 当模式以通配符 % 或 _ 开头时（例如 LIKE '%suffix' 或 LIKE '%substring%'），MySQL 无法使用常规 B-tree 索引进行优化，因为它需要扫描所有行来查找匹配项。这会导致全表扫描，性能较差。
    • 当使用 NOT LIKE 时，通常也无法利用索引。

优化建议：

  1. 避免前置通配符： 如果可能，尽量设计数据库模式和查询，使得 LIKE 模式能够以非通配符开头。
  2. 全文搜索： 对于需要频繁进行包含任意子字符串的模糊查询（例如 LIKE '%substring%'），考虑使用 MySQL 的全文搜索功能（FULLTEXT 索引）。全文搜索是为这类需求优化的，性能远超 LIKE。
  3. 其他技术： 对于某些高级模糊匹配需求，可以考虑使用 Elasticsearch 或其他专门的搜索技术。

6. LIKE 与 REGEXP (正则表达式)

对于更复杂的模式匹配需求，MySQL 提供了 REGEXP (或 RLIKE) 操作符，它支持正则表达式。正则表达式提供了比 LIKE 更强大的模式匹配能力。

示例：

查找包含数字的姓名：

sql
SELECT name
FROM employees
WHERE name REGEXP '[0-9]';

虽然 REGEXP 功能更强大，但它通常比 LIKE 性能更差，因为它永远不会利用常规 B-tree 索引。仅在 LIKE 无法满足需求时才考虑使用 REGEXP。

总结

SQL LIKE 操作符是 MySQL 中实现模糊查询的核心工具。通过 _ 和 % 这两个通配符，你可以构建各种模式来匹配字符串数据。了解如何有效使用它们，以及何时考虑性能问题并转向全文搜索或其他更高级的匹配技术，将帮助你编写更高效和强大的数据库查询。

滚动至顶部