SQL LIKE 模糊查询实用教程 – wiki基地

---

SQL LIKE 模糊查询实用教程：深入理解与应用

在数据库操作中，我们经常需要根据精确的条件来检索数据。然而，实际场景往往更为复杂，我们可能只需要查找包含特定关键词、以某个字母开头、或符合某种模式的数据。这时，精确匹配（如 WHERE column = 'value'）就显得力不从心了。SQL 提供了一种强大的工具来应对这种模糊匹配的需求，那就是 LIKE 操作符。

LIKE 操作符用于在 WHERE 子句中搜索列中的指定模式。它与两个特殊的通配符结合使用，使得模式匹配变得灵活而强大。本文将深入探讨 LIKE 操作符的使用方法、通配符的含义、各种实用场景以及一些高级话题和注意事项。

1. LIKE 操作符的基础

LIKE 操作符是 SQL 标准的一部分，几乎所有主流关系型数据库管理系统（RDBMS）都支持它，如 MySQL, PostgreSQL, SQL Server, Oracle, SQLite 等。它的基本语法如下：

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

这里的 pattern 是一个字符串表达式，其中可以包含通配符。LIKE 操作符会检查 column_name 列中的每一个值，看是否符合 pattern 定义的模式。如果符合，则该行数据会被包含在查询结果中。

2. LIKE 操作符的核心：通配符

LIKE 操作符的强大之处在于它可以与两个特殊的通配符配合使用：

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

理解这两个通配符是掌握 LIKE 模糊查询的关键。下面我们详细解释它们的使用。

2.1 通配符 % (百分号)

% 是最常用的通配符，它可以匹配任何长度的字符串（包括空字符串）。

• 模式：'pattern%'
  • 含义：匹配任何以 pattern 开头的字符串。
  • 例子：LIKE 'A%' 将匹配 ‘Apple’, ‘America’, ‘Australia’, ‘A’, 但不匹配 ‘Banana’, ‘Data’.
• 模式：'%pattern'
  • 含义：匹配任何以 pattern 结尾的字符串。
  • 例子：LIKE '%son' 将匹配 ‘Johnson’, ‘Anderson’, ‘Peterson’, 但不匹配 ‘Jackson’, ‘Sony’.
• 模式：'%pattern%'
  • 含义：匹配任何包含 pattern 子串的字符串。
  • 例子：LIKE '%data%' 将匹配 ‘Database’, ‘BigData’, ‘Metadata’, ‘Data’, ‘Just data’, 但不匹配 ‘Application’, ‘Science’.
• 模式：'pattern1%pattern2'
  • 含义：匹配以 pattern1 开头，并在其后包含 pattern2 的字符串。pattern1 和 pattern2 之间可以有零个或多个字符。
  • 例子：LIKE 'J%n' 将匹配 ‘Johnson’, ‘Jackson’, ‘Julian’, 但不匹配 ‘Jones’.

2.2 通配符 _ (下划线)

_ 通配符代表一个且仅一个字符。它常用于匹配具有特定结构但某个位置字符不确定的情况。

• 模式：'_pattern'
  • 含义：匹配任何第二个字符是 pattern 第一个字符的两个字符字符串。或者更普遍地，匹配任何长度大于等于1，且第一个字符后的子串以 pattern 开头的字符串。实际上，它更常用于指定某个位置是任意单个字符。
  • 例子：LIKE '_pple' 将匹配 ‘Apple’, ‘Cpple’, ‘Xpple’, 但不匹配 ‘Aapple’, ‘Apple Pie’. (匹配任何以任意单个字符开头，后面跟着 ‘pple’ 的5个字符的字符串)
• 模式：'pattern_'
  • 含义：匹配任何以 pattern 开头，后面紧跟着任意一个字符的字符串。
  • 例子：LIKE 'App_' 将匹配 ‘Appl’, ‘Apps’, 但不匹配 ‘App’, ‘Apple’. (匹配任何以 ‘App’ 开头，后面跟着任意单个字符的4个字符的字符串)
• 模式：'p_ttern'
  • 含义：匹配以 p 开头，第二个字符是任意字符，后面跟着 ttern 的字符串。
  • 例子：LIKE 'b_t' 将匹配 ‘bat’, ‘bet’, ‘bit’, ‘but’, ‘bbt’, 但不匹配 ‘boat’, ‘beet’. (匹配任何以 ‘b’ 开头，第三个字符是 ‘t’ 的三个字符字符串)
• 多个 _ 的组合：'_____'
  • 含义：匹配任何恰好由指定数量的下划线代表的字符组成的字符串。
  • 例子：LIKE '_____' (五个下划线) 将匹配任何恰好是5个字符长度的字符串，如 ‘World’, ‘China’, ‘Query’, 但不匹配 ‘Hello World’, ‘SQL’.

2.3 % 和 _ 的组合使用

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

• 模式：'A_%'
  • 含义：匹配任何以 ‘A’ 开头，后面跟着至少一个字符的字符串。等同于 LIKE 'A%' 并且长度大于1。
  • 例子：LIKE 'A_%' 将匹配 ‘Apple’, ‘America’, 但不匹配 ‘A’.
• 模式：'%a_b%'
  • 含义：匹配任何包含子序列 ‘a’，后面跟着任意单个字符，再后面跟着 ‘b’ 的字符串。
  • 例子：LIKE '%a_b%' 将匹配 ‘acid rain and blue sky’, ‘database’, ‘label’, 但不匹配 ‘cab’, ‘table’.
• 模式：'_%_@_%._%'
  • 含义：一个简化的电子邮件地址模式匹配。匹配任何包含 ‘@’ 符号，且 ‘@’ 符号前后都有至少一个字符，且 ‘@’ 后面的部分包含 ‘.’ 符号，且 ‘.’ 符号前后都有至少一个字符的字符串。
  • 例子：LIKE '_%_@_%._%' 将匹配 ‘[email protected]’, ‘[email protected]’, 但不匹配 ‘[email protected]’, ‘@example.com’, ‘user@example.’, ‘user@examplecom’. (注意：这是一个非常粗糙的 email 格式匹配，仅用于示例，实际email验证复杂得多)。

3. 实用示例：使用 LIKE 查询数据

假设我们有一个名为 employees 的表，结构如下：

column_name	data_type
employee_id	INT
first_name	VARCHAR
last_name	VARCHAR
title	VARCHAR
email	VARCHAR
hire_date	DATE

表中的部分数据可能像这样：

employee_id	first_name	last_name	title	email	hire_date
1	John	Smith	Software Engineer	[email protected]	2020-01-15
2	Jane	Doe	Data Analyst	[email protected]	2019-05-20
3	Peter	Jones	Project Manager	[email protected]	2018-11-10
4	Alice	Williams	Software Engineer	[email protected]	2021-03-01
5	Robert	Johnson	Data Scientist	[email protected]	2022-07-18
6	Michael	Davis	Data Engineer	[email protected]	2020-09-25
7	Emily	Smith	QA Engineer	[email protected]	2021-04-12
8	William	Brown	Software Tester	[email protected]	2019-12-01
9	Linda	Garcia	IT Support	[email protected]	2022-01-05
10	David	Wilson	Data Analyst	[email protected]	2020-06-30

现在，让我们用 LIKE 来进行一些查询：

示例 1: 查找名字以 ‘J’ 开头的员工

sql
SELECT first_name, last_name
FROM employees
WHERE first_name LIKE 'J%';

结果可能包含：
– John Smith
– Jane Doe

示例 2: 查找姓氏以 ‘son’ 结尾的员工

sql
SELECT first_name, last_name
FROM employees
WHERE last_name LIKE '%son';

结果可能包含：
– Robert Johnson
– Peter Jones (如果数据库不区分大小写，且有姓氏包含’son’的部分)
– … (根据实际数据)

示例 3: 查找职位中包含 ‘Data’ 的员工

sql
SELECT first_name, last_name, title
FROM employees
WHERE title LIKE '%Data%';

结果可能包含：
– Jane Doe, Data Analyst
– Robert Johnson, Data Scientist
– Michael Davis, Data Engineer
– David Wilson, Data Analyst

示例 4: 查找邮箱地址以 ‘.com’ 结尾的员工

sql
SELECT first_name, last_name, email
FROM employees
WHERE email LIKE '%.com';

结果可能包含所有示例数据中的员工，因为他们的邮箱都以 .com 结尾。

示例 5: 查找名字是4个字母的员工

sql
SELECT first_name
FROM employees
WHERE first_name LIKE '____'; -- 四个下划线

结果可能包含：
– John
– Jane

示例 6: 查找名字以 ‘A’ 开头，第二个字母是任意字符，第三个字母是 ‘i’ 的员工

sql
SELECT first_name
FROM employees
WHERE first_name LIKE 'A_i%';

结果可能包含：
– Alice (A-l-i-c-e)

示例 7: 查找姓氏以 ‘S’ 开头，倒数第二个字母是 ‘t’ 的员工 (这里需要结合 % 和 _)

模式应该是 'S%t_'。S开头，后面任意多个字符，接着一个 t，最后任意一个字符。

sql
SELECT first_name, last_name
FROM employees
WHERE last_name LIKE 'S%t_';

结果可能包含：
– John Smith (S-m-i-t-h) – 匹配 S%t_ 中的 S, m-i 匹配 %, t 匹配 t, h 匹配 _
– Emily Smith (S-m-i-t-h)

4. NOT LIKE：排除匹配模式的数据

与 LIKE 相反，NOT LIKE 操作符用于查找不匹配指定模式的数据。其语法是在 LIKE 前面加上 NOT 关键字。

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

示例 8: 查找职位中不包含 ‘Engineer’ 的员工

sql
SELECT first_name, last_name, title
FROM employees
WHERE title NOT LIKE '%Engineer%';

结果可能包含：
– Jane Doe, Data Analyst
– Peter Jones, Project Manager
– Robert Johnson, Data Scientist
– Linda Garcia, IT Support
– David Wilson, Data Analyst

示例 9: 查找名字不以 ‘M’ 或 ‘N’ 开头的员工

这需要结合 NOT LIKE 和 OR 或 AND 条件。

sql
SELECT first_name, last_name
FROM employees
WHERE first_name NOT LIKE 'M%' AND first_name NOT LIKE 'N%';

或者，使用 NOT 关键字修饰整个 OR 条件（但这样写通常不如上面的直观）：

sql
SELECT first_name, last_name
FROM employees
WHERE NOT (first_name LIKE 'M%' OR first_name LIKE 'N%');

5. 如何搜索包含通配符本身 (% 或 _) 的字符串？

假设你想查找产品名称中包含字面上的 ‘%’ 符号或者 ‘_’ 下划线的数据。直接在 LIKE 模式中使用 % 或 _ 会被解释为通配符，而不是字面字符。为了解决这个问题，你需要使用 ESCAPE 子句。

ESCAPE 子句允许你指定一个转义字符。紧跟在转义字符后面的通配符 (% 或 _) 或转义字符本身会被解释为字面字符。

基本语法：

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

常用的转义字符是反斜杠 \，但你也可以选择其他字符，只要它不出现在你的模式字符串中并且不是通配符。

示例 10: 查找描述中包含字面 ‘% discount’ 的产品

假设产品表 products 有一个 description 列。

sql
SELECT product_name, description
FROM products
WHERE description LIKE '%50\% discount%' ESCAPE '\';

在这个例子中，反斜杠 \ 被指定为转义字符。\% 告诉数据库搜索字面上的 ‘%’ 符号。第一个 % 和最后一个 % 仍然是通配符。

示例 11: 查找部件号中包含字面 ‘_’ (下划线) 的产品

假设产品表 products 有一个 part_number 列。

sql
SELECT product_name, part_number
FROM products
WHERE part_number LIKE 'ABC\_Part\_XYZ%' ESCAPE '\';

这里，\_ 告诉数据库搜索字面上的 ‘_’ 符号。

示例 12: 如何搜索包含转义字符本身？

如果你选择了 \ 作为转义字符，并且需要搜索包含字面反斜杠的字符串，你需要将转义字符自身也进行转义，也就是 \\。

sql
-- 假设你要找一个路径字符串 'C:\Data\Temp'
SELECT file_path
FROM documents
WHERE file_path LIKE 'C:\\Data\\Temp%' ESCAPE '\';

6. LIKE 查询的性能考虑

虽然 LIKE 非常有用，但在处理大量数据时，其性能可能会成为瓶颈。理解何时 LIKE 查询效率低下以及原因是很重要的。

• 模式以通配符 % 开头 (LIKE '%pattern')：
  这是最影响性能的情况。数据库通常使用索引来快速定位数据。然而，如果模式以 % 开头，数据库无法使用索引的B-树结构从字符串的开头进行快速查找。它可能需要扫描表中的大部分或所有行（全表扫描），然后对每一行进行模式匹配，这在大型表上会非常慢。
• 模式中间包含通配符 % (LIKE '%pattern%')：
  类似于以 % 开头的情况，中间的 % 也会阻止索引的有效使用，通常导致全表扫描。
• 模式以固定字符串开头 (LIKE 'pattern%')：
  这种模式可以有效地使用索引（如B-tree索引）。数据库可以根据模式开头的固定字符串快速定位到索引中的相关部分，从而大大减少需要检查的数据量。
• 模式只包含 _ 通配符或以固定字符串开头后跟 _ (LIKE 'pattern_ _ ...')：
  如果模式以固定字符串开头，_ 的存在不会完全破坏索引的使用。数据库仍然可以利用索引定位到开头匹配的数据范围。但如果模式全是 _ (LIKE '_____') 或者以 % 开头 (LIKE '%_...'), 索引效果有限。

性能优化建议：

1. 避免使用以 % 开头的模式进行查询，除非数据量很小或别无选择。 如果可能，尽量重构查询或数据。
2. 确保对经常用于 LIKE 'pattern%' 查询的列建立索引。
3. 考虑使用全文搜索（Full-Text Search）：对于复杂的、基于关键词或短语的文本搜索需求，特别是文档内容搜索，全文搜索是比 LIKE 更高效、功能更强大的解决方案。大多数现代RDBMS都提供了内置的全文搜索功能（如 SQL Server Full-Text Search, PostgreSQL Full-Text Search, MySQL Full-Text Search）。
4. 对于以 % 开头的模式，如果需要优化，可能需要其他技术，例如：
   • 反转字符串并索引： 在某些数据库中，可以创建反转字符串的列，并对该列创建索引，然后对反转后的查询模式使用 LIKE 'pattern%'。
   • 使用 trigram 索引： PostgreSQL 等数据库支持 trigram 索引，可以加速 LIKE '%pattern%' 或 LIKE '%pattern' 的查询，但索引大小和维护成本可能更高。

7. LIKE 和大小写敏感性

LIKE 操作符是否区分大小写取决于数据库系统、操作系统以及列的排序规则（collation）。

• 在某些数据库或配置中（如 PostgreSQL 默认、某些 MySQL 配置），LIKE 是区分大小写的。
  • WHERE last_name LIKE '%smith%' 只会匹配 ‘smith’，而不匹配 ‘Smith’ 或 ‘SMITH’。
• 在其他数据库或配置中（如 SQL Server 默认、Windows 系统上的 MySQL 默认），LIKE 是不区分大小写的。
  • WHERE last_name LIKE '%smith%' 会匹配 ‘Smith’, ‘smith’, ‘SMITH’。

如何控制大小写敏感性：

1. 使用函数强制转换大小写： 最通用的方法是在比较之前将列和模式都转换为统一的大小写（通常是小写）。
   sql
SELECT first_name, last_name
FROM employees
WHERE LOWER(last_name) LIKE '%smith%'; -- 无论数据库是否区分大小写，都进行不区分大小写的匹配
   注意： 使用函数 (LOWER(), UPPER()) 会阻止索引的使用，因为它需要在每一行上计算函数结果后再进行比较。这可能导致性能下降。

2. 使用数据库特定的排序规则（Collation）： 许多数据库允许你在查询中或在列定义级别指定排序规则，排序规则会影响字符串比较是否区分大小写。

   • SQL Server:
     “`sql
     — 不区分大小写 (CI: Case Insensitive)
     SELECT first_name, last_name
     FROM employees
     WHERE last_name COLLATE SQL_Latin1_General_CP1_CI_AS LIKE ‘%smith%’;

     — 区分大小写 (CS: Case Sensitive)
     SELECT first_name, last_name
     FROM employees
     WHERE last_name COLLATE SQL_Latin1_General_CP1_CS_AS LIKE ‘%smith%’;
     * **MySQL:** 使用 `COLLATE` 关键字。例如 `utf8mb4_general_ci` 是不区分大小写，`utf8mb4_bin` 是区分大小写。sql
     SELECT first_name, last_name
     FROM employees
     WHERE last_name COLLATE utf8mb4_general_ci LIKE ‘%smith%’;
     * **PostgreSQL:** 使用 `COLLATE` 关键字或 `ILIKE` 操作符。`ILIKE` 是 PostgreSQL 特有的，用于不区分大小写的 `LIKE` 匹配，且通常比 `LOWER(column) LIKE pattern` 更能利用索引（如果使用了适当的文本索引）。sql
     — 不区分大小写 (PostgreSQL 特有)
     SELECT first_name, last_name
     FROM employees
     WHERE last_name ILIKE ‘%smith%’;

     — 区分大小写 (标准 LIKE)
     SELECT first_name, last_name
     FROM employees
     WHERE last_name LIKE ‘%smith%’;
     “`
     使用排序规则通常比使用函数更有效率，因为它可以在索引级别进行处理（取决于数据库和索引类型）。

8. LIKE 在其他 SQL 语句中的应用

LIKE 操作符不仅可以在 SELECT 语句的 WHERE 子句中使用，还可以在其他需要条件表达式的地方使用，例如：

• UPDATE 语句: 更新符合特定模式的数据。
  sql
-- 将所有职位中包含 'Data' 的员工的头衔改为 'Analytics Specialist'
UPDATE employees
SET title = 'Analytics Specialist'
WHERE title LIKE '%Data%';
• DELETE 语句: 删除符合特定模式的数据。
  sql
-- 删除所有邮箱地址以 '.old' 结尾的员工记录
DELETE FROM employees
WHERE email LIKE '%.old';

• JOIN 条件 (较少见但可能): 在某些情况下，可以使用 LIKE 作为连接条件，但这通常意味着数据模型设计可能存在问题，或者在处理非规范化数据。性能通常很差。
  sql
-- 示例：查找与某个产品描述模式相关的订单 (假设 order_items 有一个 product_description)
-- 注意：这种 JOIN 非常低效
SELECT o.order_id, p.product_name
FROM orders o
JOIN products p ON o.product_description LIKE '%' + p.product_name + '%'; -- 示例语法，具体连接字符串方法取决于数据库
  绝大多数情况下，JOIN 应该基于精确匹配的外键关系。

• CASE 表达式: 根据字段是否匹配某个模式来返回不同的值。
  sql
SELECT
first_name,
last_name,
title,
CASE
WHEN title LIKE '%Engineer%' THEN 'Technical Staff'
WHEN title LIKE '%Manager%' THEN 'Management'
WHEN title LIKE '%Analyst%' OR title LIKE '%Scientist%' THEN 'Data Staff'
ELSE 'Other'
END AS employee_category
FROM employees;

9. 总结与最佳实践

通过本文的学习，你应该对 SQL 的 LIKE 操作符有了全面的了解：

• LIKE 用于执行模糊匹配，结合通配符 % (零个或多个字符) 和 _ (恰好一个字符)。
• NOT LIKE 用于查找不匹配指定模式的数据。
• ESCAPE 子句用于搜索包含通配符本身 (%, _) 或转义字符的字面值。
• LIKE 查询的性能取决于模式：以固定字符串开头的模式 ('pattern%') 可以利用索引，而以 % 开头的模式 ('%pattern') 或中间包含 % 的模式 ('%pattern%') 通常会导致性能下降。
• 大小写敏感性取决于数据库配置和排序规则，可以使用 LOWER()/UPPER() 函数或 COLLATE 子句进行控制（PostgreSQL 还有 ILIKE）。
• LIKE 可以应用于 WHERE, UPDATE, DELETE, CASE 等子句中。

最佳实践：

1. 优先使用以固定字符串开头的 LIKE 模式 ('pattern%')。
2. 对于复杂的文本搜索或大表上的模糊搜索，考虑使用全文搜索功能。
3. 了解你的数据库是否区分大小写，并根据需要使用 LOWER() 或 COLLATE 来控制匹配行为。 注意 LOWER() 可能影响性能。
4. 谨慎使用以 % 开头或中间包含 % 的 LIKE 模式，尤其是在大型生产数据库中。 如果必须使用，确保是在可接受的性能范围内，或者考虑其他搜索技术。
5. 使用 ESCAPE 子句时，选择一个不常出现在目标字符串中的字符作为转义符，并记住转义符本身需要双写来匹配字面转义符。

掌握 LIKE 操作符及其通配符，是进行灵活数据库查询的基本技能。通过合理地使用它，并注意其潜在的性能影响，你可以更有效地检索和管理数据库中的文本数据。现在，拿起你的 SQL 编辑器，开始练习使用 LIKE 进行各种模式匹配查询吧！