SQL LIKE 模式匹配：实用指南与示例

假设您有一张 employees 表，想要找到所有以 “A” 开头的名字：

您当然可以手动翻阅整张表，但既然有 LIKE 运算符，何必如此呢？

SELECT DISTINCT
	first_name
FROM employees
WHERE first_name LIKE 'A%'技术支持

关键在于子句 `WHERE first_name LIKE ‘A%’`，意思是“查找所有以 "A" 开头，后接任意数量字符的 first_name”。这里的 A% 就是用于匹配的模式。

% 并不是与 LIKE 搭配使用的唯一通配符，您也可以使用下划线 _：

• `%` 匹配任意数量的字符。
• _ 匹配任意单个字符。

语法很容易记忆，就是：

column_name LIKE pattern技术支持

您可以用 LIKE 实现多种模式匹配。方法如下：

SQL LIKE 入门示例

下面列出了一些使用 LIKE 语句的实用示例，以及我们示例数据集对应的结果。

1. 使用 LIKE 进行精确字符串匹配

如果您要进行精确字符串匹配，可在不使用 “%” 或 “_” 的情况下使用 LIKE

SELECT
    first_name, last_name
FROM employees
WHERE first_name LIKE 'Barry' -- the same as WHERE first_name = ‘Barry’技术支持

2. 使用 “%” 匹配任意数量的字符 

“%” 可用于匹配任意（甚至为零个）数量的字符——数字、字母或符号。

假设您想找出名字以 “Adam” 开头的所有员工；可以使用模式 “Adam%”

SELECT DISTINCT
    first_name
FROM employees
WHERE first_name LIKE 'Adam%'技术支持

要查找以 "z" 结尾的名字，使用模式 “%z”。同一个模式中也可以使用多个 “%”。例如，想找出包含 "z" 的名字，可用 “%z%”。

3. 使用 “_” 匹配一个（且仅一个）字符

就像“刽子手”（Hangman）游戏一样，下划线 _ 只能占一个字符。

Le_n 可以有多少种拼法？该模式会匹配从 "Lexn"、"LeAn"、"Le3n" 到 "Le-n" 的任意形式。

SELECT DISTINCT
    first_name
FROM employees
WHERE first_name LIKE 'Le_n'技术支持

有哪些名字只有三个字符？我们可以用三个连续的下划线 ___ 作为模式来找出。

SELECT DISTINCT
    first_name
FROM employees
WHERE first_name LIKE '___'技术支持

4. 同时使用 “%” 和 “_” 匹配任意模式

当然，您也可以将 “%” 和 “_” 组合，构造更有意思的模式。

SELECT DISTINCT
    first_name
FROM employees
WHERE first_name LIKE '%ann_'技术支持

模式 “%ann_” 匹配以任意数量字符开头、以 "ann" 加一个字符结尾的字符串。

5. 使用 NOT 查找不匹配某模式的字符串

如果您想找出不满足某个条件的所有行，可以使用 NOT LIKE 运算符。比如，要查找除 "Staff" 之外的所有职称，我们可以使用如下语法

`WHERE title NOT LIKE ‘Staff’`技术支持

这与以下语法完全等价：

WHERE title != 'Staff'技术支持

SELECT DISTINCT
    title
FROM titles
WHERE title NOT LIKE 'Staff'技术支持

当然，您也可以将 NOT LIKE 用于我们前面介绍的任意模式。

SELECT DISTINCT
    title
FROM titles 
WHERE title NOT LIKE '%engineer'技术支持

6. 将 LOWER（或 UPPER）与 LIKE 搭配用于不区分大小写的匹配

如果需要进行模式匹配，但不确定字符串是以小写、大写还是混合形式存储，可以使用以下语法。

LOWER(column_name) LIKE pattern技术支持

LOWER() 函数会将所有字符串转为小写，不论其原本是大写、小写或混合大小写。

使用该语法时，请确保将模式全部写成小写！否则，可能得不到任何匹配结果。

您也可以在上述语法中用 UPPER 替换 LOWER。同时务必将模式写成大写。

`UPPER(column_name) LIKE PATTERN`技术支持

例如，若要查找员工名字可能为 "Joanne"、"JoAnne"、"Joanna" 或 "JoAnna"，可尝试以下任一方式：

SELECT DISTINCT
    first_name
FROM employees
WHERE LOWER(first_name) LIKE 'joann_'技术支持

SELECT DISTINCT
    first_name
FROM employees
WHERE UPPER(first_name) LIKE 'JOANN_'技术支持

7. 使用 OR/AND 让 LIKE 同时匹配多个值

您也可以在 LIKE 语法中组合多个条件。

例如，使用 OR 条件来查找满足多个 LIKE 模式中至少一个的结果。

SELECT DISTINCT
    first_name
FROM employees
WHERE
first_name LIKE 'Ad_l' OR
first_name LIKE 'Ad_m'技术支持

另一方面，若要查找同时满足多个 LIKE 条件的字符串，请使用 AND 关键字。

SELECT DISTINCT
    first_name
FROM employees
WHERE
first_name LIKE '%am%' AND
first_name LIKE '%me%'技术支持

只要列的数据类型是可变长度字符（varchar），就可以将 LIKE 语法应用到多列上。基于此，我们可以找出姓名首字母都是 "Z. Z." 的员工。

SELECT DISTINCT
    first_name, last_name
FROM employees
WHERE
first_name LIKE 'Z%' AND
last_name LIKE 'Z%'技术支持

8. 在 SELECT 的 CASE WHEN 子句中使用 LIKE 

到目前为止，我们主要关注在WHERE 子句中将 LIKE 作为选择记录的条件。我们也可以在 SELECT 子句中使用 LIKE。例如，能否统计数据库中名为 "Adam" 的员工有多少？

SELECT
    COUNT(CASE WHEN first_name LIKE 'Adam' THEN 1 END) num_employees_adam
FROM employees技术支持

另一方面，首字母为 "A.Z." 的员工有多少？

SELECT
    COUNT(CASE WHEN first_name LIKE 'A%' AND last_name LIKE 'Z%' THEN 1 END) num_employees
FROM employees技术支持

SQL LIKE 的常用模式

下面对我们讨论过的模式做一个速查总结：

SQL LIKE 的进阶示例

LIKE 在不同 SQL 方言（如 PostgreSQL、MySQL、Redshift 等）中大体相同。但有些方言提供了值得一提的变体。

1. ILIKE 运算符

在 Redshift 和PostgreSQL 中可用，ILIKE 是大小写不敏感版本的 LIKE。因此，以下写法等价。

SELECT
  datacamp ILIKE ‘datacamp’, -- returns TRUE
  DATACAMP ILIKE ‘datacamp’, -- returns TRUE
  Datacamp ILIKE ‘datacamp’, -- returns TRUE
  datacamp ILIKE ‘DataCamp’, -- returns TRUE技术支持

2. 使用方括号 [] 和 [^] 作为通配符

T-SQL 或 SQL Server 的用户有额外的通配符可进行更复杂的模式匹配。

方括号语法 [] 匹配范围或集合内的任意特定字符。例如，下面这些都会返回 TRUE。

SELECT
  ‘Carson’ LIKE ‘[C-K]arson’, -- returns TRUE because C is in the range C-K
  ‘Karson’ LIKE ‘[C-K]arson’, -- returns TRUE because K is in range
  ‘Larson’ LIKE ‘[CKL]arson’, -- returns TRUE because L is in the set [CKL]
  ‘Parson’ LIKE ‘[C-K]arson’ -- returns FALSE because P is out of range技术支持

插入在方括号内的尖号 [^] 通配符，匹配不在指定范围或集合内的任意单个字符。您能看出下面结果的原因吗？

SELECT
  ‘Carson’ LIKE ‘[^C-K]arson’ -- returns FALSE 
  ‘Parson’ LIKE ‘[^C-K]arson’ -- returns TRUE技术支持

在此，由于 "C" 属于 [C-K] 范围，因此字符 ‘C’ 不会匹配 [^C-K]。于是，"Carson" 不会匹配 [^C-K]arson，而 "Parson" 会。

3. RLIKE 运算符

在 MySQL 中可用，RLIKE 运算符能够识别模式中的正则表达式（RegEx）。正则是进行高级模式匹配的强大而灵活的工具。

若您的 SQL 方言支持 RLIKE，了解一些正则基础非常有用。您可以通过我们的Python 正则表达式课程进一步学习。

SELECT DISTINCT
    first_name
FROM employees
WHERE first_name RLIKE 'Susann[a-e]'技术支持

4. “~~” 运算符

在 PostgreSQL 中，“~~” 与 LIKE 完全同义。同时还存在与 ILIKE、NOT LIKE、NOT ILIKE 等价的写法。

故障排查：使用 LIKE 的常见错误与陷阱

以下是使用 LIKE 时可能遇到的一些常见错误，以及相应的修正方法：

1. 无结果返回：检查大小写敏感性。在某些 SQL 方言中，LIKE 默认区分大小写。可使用 LOWER() 或（若支持）ILIKE 进行不区分大小写的匹配。
2. 通配符导致的意外结果：确保正确使用 % 和 _。% 匹配任意数量字符，而 _ 仅匹配一个字符，放置不当会带来意外匹配。
3. 性能问题：若查询缓慢，留意模式中的前导 %（如 %pattern），它会禁用索引使用。若可能，改写为以特定字符串起始的模式。性能详见下节。
4. SQL 注入错误：若查询中使用了用户输入，请确保正确参数化，避免SQL 注入风险。
5. 模式中的特殊字符：若需按字面匹配搜索字符串中的 % 和 _ 等通配符，必须进行转义。使用 ESCAPE 在查询中定义转义字符。

WHERE column_name LIKE '50\% OFF' ESCAPE '\'技术支持

使用 LIKE 运算符的性能考量

LIKE 运算符非常实用，但在大型数据集上可能影响查询性能。以下是优化建议：

1. 索引：当模式以常量字符串开头（如 Adam%）时，数据库可利用索引，LIKE 的性能最佳。然而，像 %Adam 或 %Adam% 这样的模式需要全表扫描，对于大表会很慢。
2. 避免前导通配符：以 % 起始的模式（如 %pattern）会禁用索引使用，数据库不得不检查每条记录。
3. 排序规则与大小写不敏感匹配：对列使用 LOWER() 或 UPPER() 实现不区分大小写的搜索，也可能导致无法使用索引。更好的做法是将数据库排序规则设置为支持不区分大小写的比较。
4. 替代方案：对于大型数据集，执行复杂或高频的字符串匹配时，考虑使用全文搜索或数据库特有的搜索功能，如 PostgreSQL 的 GIN索引或 MySQL 的 FULLTEXT 索引。
5. 提高选择性：通过添加其他过滤条件（如日期范围或数值列）缩小查询范围，减少 LIKE 需处理的数据量。

LIKE 与全文搜索

MySQL、PostgreSQL 和 SQL Server 等数据库提供全文搜索。虽然 LIKE 适合直接的模式匹配，但全文搜索为更高级的文本查询而生。两者对比如下：

若您需要超出模式匹配的更高级字符串操作，请参阅SQL CONTAINS 教程。

自信使用 SQL LIKE

掌握 SQL 函数是数据科学成功的关键，SQL 的 LIKE 命令也不例外。熟练掌握 SQL 将显著加速您的分析进展，请务必深入学习！

想获得更多 SQL 资源，请查看以下内容：

• Introduction to SQL 课程
• Intermediate SQL 课程
• SQL WHERE 子句入门
• SQL 子查询：全面指南
• SQL 连接（JOIN）入门
• Learn SQL 专区
• SQL 基础速查表