sql-like-clause

SQL - 使用 LIKE 运算符进行模式匹配

模式匹配简介

当你只记得客户姓名的一部分时，如何找到该客户？或者如何找到所有具有特定型号前缀的产品？对于这些场景，简单的相等检查（=）是不够的。这就是 SQL LIKE 运算符的用武之地。它用于 WHERE 子句中，以在列中搜索指定的模式。

LIKE 运算符及其通配符（%，_）

LIKE 运算符的强大之处来自于两个特殊的通配符：

• 百分号（%）： 代表零个、一个或多个字符。
• 下划线（_）： 代表单个字符。

语法

SELECT column_list
FROM table_name
WHERE column_name LIKE pattern;

设置：示例 CUSTOMERS 表

在我们的示例中，我们将使用以下 CUSTOMERS 表。

CREATE TABLE CUSTOMERS(
    ID INT PRIMARY KEY,
    NAME VARCHAR(50) NOT NULL,
    CITY VARCHAR(50)
);

INSERT INTO CUSTOMERS (ID, NAME, CITY) VALUES
    (1, 'Ramesh Kumar', 'Ahmedabad'),
    (2, 'Khilan Sharma', 'Delhi'),
    (3, 'Kaushik Patel', 'Kota'),
    (4, 'Chaitali Singh', 'Mumbai'),
    (5, 'Hardik Pandya', 'Bhopal'),
    (6, 'Komal Verma', 'Hyderabad'),
    (7, 'Muffy', 'Indore');

实用的 LIKE 模式

以下是使用 LIKE 最常见的方式：

模式	描述	示例查询	结果
'K%'	查找以 ‘K’ 开头的任何值。	WHERE NAME LIKE ‘K%'	'Khilan Sharma’, ‘Kaushik Patel’, ‘Komal Verma’
'%a'	查找以 ‘a’ 结尾的任何值。	WHERE NAME LIKE ‘%a'	'Komal Verma’
'%al%'	查找在任何位置包含 ‘al’ 的任何值。	WHERE NAME LIKE ‘%al%'	'Khilan Sharma’, ‘Chaitali Singh’, ‘Komal Verma’
'_h%'	查找在第二个位置包含 ‘h’ 的任何值。	WHERE NAME LIKE ‘_h%'	'Khilan Sharma’, ‘Chaitali Singh’
'K__%'	查找以 ‘K’ 开头且至少有 3 个字符长的任何值。	WHERE NAME LIKE ‘K__%'	'Khilan Sharma’, ‘Kaushik Patel’, ‘Komal Verma’
'M%y'	查找以 ‘M’ 开头并以 ‘y’ 结尾的任何值。	WHERE NAME LIKE ‘M%y'	'Muffy’

高级用法：NOT、OR 和 ESCAPE

使用 NOT LIKE

要查找不匹配模式的行，只需使用 NOT 运算符。

-- 查找所有姓名不以 'K' 开头的客户
SELECT NAME FROM CUSTOMERS WHERE NAME NOT LIKE 'K%';

搜索字面通配符：ESCAPE 子句

如果你需要查找字面包含 ’%’ 或 ’_’ 字符的值怎么办？例如，搜索产品名称中包含“50%”折扣的产品。你可以使用 ESCAPE 子句来定义一个“转义字符”，该字符告诉 SQL 将其后面的通配符视为字面字符。

CREATE TABLE Deals (ProductName VARCHAR(100));
INSERT INTO Deals VALUES ('Summer Sale - 50% Off!'), ('Standard Item');

-- 查找名称中包含字面 '%' 的产品。这里，'!' 是转义字符。
SELECT ProductName FROM Deals
WHERE ProductName LIKE '%!%%' ESCAPE '!';

-- 结果: 'Summer Sale - 50% Off!'

重要考虑事项：性能和安全性

性能：前导通配符问题

LIKE 查询的性能很大程度上取决于模式。像 WHERE name LIKE 'K%' 这样的查询很快，因为数据库可以使用 name 列上的索引。然而，带有前导通配符的查询，例如 WHERE name LIKE '%mer'，通常非常慢。它无法使用标准索引，必须执行全表扫描，读取每一行以检查匹配项。在大型表上应尽可能避免使用前导通配符。

安全性：防止 SQL 注入

切勿将用户输入直接拼接（concatenate）到 LIKE 查询字符串中。这会造成严重的安全漏洞，称为 SQL 注入（SQL Injection）。始终使用参数化查询（parameterized queries）或预处理语句（prepared statements），它们能安全地处理用户输入。

-- 差：容易受到 SQL 注入攻击
-- query = "SELECT * FROM USERS WHERE name LIKE '%" + userInput + "%'";

-- 好：使用参数化查询（语法因语言而异）
-- query = "SELECT * FROM USERS WHERE name LIKE ?";
-- parameters = ["%" + userInput + "%"];

超越 LIKE：何时使用其他工具

LIKE 对于简单模式非常有用，但对于更复杂的需求，请考虑以下替代方案：

• 正则表达式（REGEXP，RLIKE）： 对于复杂模式匹配（例如，查找有效的电子邮件格式或特定数字序列），正则表达式提供比 LIKE 强大得多的功能和灵活性。这是一个非标准功能，但在 MySQL 和 PostgreSQL 等许多关系型数据库管理系统（RDBMS）中都可用。
• 全文搜索（Full-Text Search, FTS）： 当你需要在大量文本块（如文章或产品描述）中搜索单词或短语时，FTS 是正确的工具。它比 LIKE '%word%' 快得多，功能也更强大，提供词干提取（匹配 ‘run’, ‘ran’, ‘running’）和相关性排名等功能。