sql-wildcards

SQL 通配符用于模式匹配

SQL 通配符简介

在 SQL 中，通配符是特殊字符，与 LIKE 操作符一起使用，用于在字符串数据中搜索模式。这使得在 WHERE 子句中进行灵活而强大的过滤成为可能，超越了简单的相等性检查。

ANSI SQL 定义了两个标准通配符：

通配符	描述
百分号 (%)	匹配任意零个或多个字符序列。
下划线 (_)	匹配任意单个字符。

这些通配符可以组合使用，创建复杂的搜索模式，以实现更精细的数据检索。

语法

使用通配符的基本语法如下：

SELECT column1, column2, ...
FROM table_name
WHERE column_name LIKE 'your_pattern';

以下是一些常见示例，演示了如何将通配符与 LIKE 操作符一起使用：

语句	描述
WHERE name LIKE ‘A%‘	查找以 ‘A’ 开头的任何值。
WHERE name LIKE ‘%a’	查找以 ‘a’ 结尾的任何值。
WHERE name LIKE ‘%om%‘	查找在任何位置包含 ‘om’ 的任何值。
WHERE name LIKE ‘_o%‘	查找在第二个位置包含 ‘o’ 的任何值。
WHERE name LIKE ‘K__l%‘	查找以 ‘K’ 开头、至少 4 个字符长且第四个字符为 ‘l’ 的任何值。
WHERE name LIKE ‘R%h’	查找以 ‘R’ 开头并以 ‘h’ 结尾的任何值。

实际示例

我们来设置一个示例 CUSTOMERS 表以演示这些概念。

-- 用于创建表的现代 DDL（数据定义语言）
CREATE TABLE CUSTOMERS (
    ID INT PRIMARY KEY,
    NAME VARCHAR(255) NOT NULL,
    AGE INT NOT NULL,
    CITY VARCHAR(255),
    SALARY DECIMAL(18, 2)
);

-- 插入示例数据
INSERT INTO CUSTOMERS (ID, NAME, AGE, CITY, SALARY) VALUES
(1, 'Ramesh', 32, 'Ahmedabad', 2000.00),
(2, 'Khilan', 25, 'Delhi', 1500.00),
(3, 'Kaushik', 23, 'Kota', 2000.00),
(4, 'Chaitali', 25, 'Mumbai', 6500.00),
(5, 'Hardik', 27, 'Bhopal', 8500.00),
(6, 'Komal', 22, 'Hyderabad', 4500.00),
(7, 'Muffy', 24, 'Indore', 10000.00);

这是一个查询，用于查找所有姓名以 ‘K’ 开头的客户：

SELECT ID, NAME, CITY FROM CUSTOMERS
WHERE NAME LIKE 'K%';

输出

ID	NAME	CITY
2	Khilan	Delhi
3	Kaushik	Kota
6	Komal	Hyderabad

最佳实践与高级概念

转义通配符

如果您需要搜索 ’%’ 或 ’_’ 字符本身怎么办？您可以使用 ESCAPE 子句来指定一个转义字符。

-- 查找名称中包含 '50%' 的产品
SELECT product_name FROM products
WHERE product_name LIKE '%50\%%' ESCAPE '\';

大小写敏感性

关于大小写敏感性，LIKE 的行为并非标准化，它取决于数据库系统及其排序规则（collation）设置：

• PostgreSQL： LIKE 是大小写敏感的。使用 ILIKE 进行大小写不敏感匹配。
• MySQL： 行为取决于列的排序规则。大多数默认排序规则（例如 utf8mb4_general_ci）都是大小写不敏感的。
• SQL Server： 行为也取决于数据库排序规则。默认通常是大小写不敏感的。

性能考量

尽可能避免使用前导通配符（%search_term）。带有前导通配符的查询无法在该列上使用标准的 B 树索引，这可能导致全表扫描（full table scan）并降低大型表的性能。

安全注意事项：SQL 注入

切勿将用户输入直接拼接（concatenate）到 LIKE 子句中。这会带来 SQL 注入（SQL Injection）的风险。始终使用参数化查询（预处理语句，prepared statements）来安全地处理用户输入。

超越 LIKE：正则表达式

对于更复杂的模式匹配，大多数现代数据库支持正则表达式，通过 REGEXP（MySQL）、~（PostgreSQL）等操作符，或 REGEXP_LIKE（Oracle、SQL Server）等函数来实现。它们提供了比标准通配符强大得多的功能。