MySQL - 通配符

MySQL - 模式匹配中的通配符

通配符在 SQL 中的作用

在 SQL 中，通配符（wildcards）是与 LIKE 运算符（operator）一起使用的特殊字符，用于在字符串比较中执行模式匹配（pattern matching）。它们对于构建灵活的搜索查询至关重要，例如查找所有名字以 ‘J’ 开头的客户，或者查找所有包含单词 ‘-inch’ 的产品。MySQL 主要提供两种通配符：百分号 (%) 和下划线 (_)。

通配符	描述
%	匹配零个或多个字符的任意序列。例如，‘a%’ 匹配 ‘a’、‘apple’ 和 ‘application’。
_	匹配任意单个字符。例如，‘h_t’ 匹配 ‘hot’、‘hat’ 和 ‘hit’，但不匹配 ‘heat’。

通用语法

WHERE 子句（clause）中使用通配符的基本语法如下：

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

以下是一些结合两种通配符的常见模式：

序号	模式和描述
1	WHERE name LIKE ‘Jo%‘ 查找以 ‘Jo’ 开头的所有值。
2	WHERE name LIKE ‘%son%‘ 查找字符串中任意位置包含 ‘son’ 的所有值。
3	WHERE name LIKE ‘_o%‘ 查找第二个字符是 ‘o’ 的所有值。
4	WHERE name LIKE ‘J__n’ 查找以 ‘J’ 开头、以 ‘n’ 结尾的四个字符的名称。
5	WHERE name LIKE ‘%a’ 查找以 ‘a’ 结尾的所有值。
6	WHERE name LIKE ‘C%i’ 查找以 ‘C’ 开头、以 ‘i’ 结尾的所有值。

设置：创建示例表

我们来创建一个 products 表来演示这些概念。注意这里使用了现代数据类型（data types）和约束（constraints）。

CREATE TABLE products (
    id INT AUTO_INCREMENT PRIMARY KEY,
    product_name VARCHAR(100) NOT NULL,
    category VARCHAR(50),
    stock_quantity INT NOT NULL DEFAULT 0
);

-- 插入一些示例数据
INSERT INTO products (product_name, category, stock_quantity) VALUES
('Laptop 15-inch', 'Electronics', 50),
('Wireless Mouse', 'Electronics', 250),
('Mechanical Keyboard', 'Electronics', 120),
('Coffee Mug 12oz', 'Kitchenware', 300),
('T-shirt (Large)', 'Apparel', 150),
('T-shirt (Medium)', 'Apparel', 200),
('Notebook A5', 'Stationery', 500);

百分号 (%) 通配符

% 通配符最为灵活，它匹配任意数量的字符（包括零个）。它非常适合用于查找子字符串（substrings）。

示例 1：查找以 ‘L’ 开头的产品

SELECT product_name, category FROM products
WHERE product_name LIKE 'L%';

此查询返回产品名称以 ‘L’ 开头的产品。

product_name	category
Laptop 15-inch	Electronics

示例 2：查找包含 ‘board’ 的产品

SELECT product_name, stock_quantity FROM products
WHERE product_name LIKE '%board%';

此查询返回产品名称中任意位置包含 ‘board’ 的任何产品。

product_name	stock_quantity
Mechanical Keyboard	120

下划线 (_) 通配符

_ 通配符是精确匹配，它只匹配一个字符。当您知道字符串某部分的长度时，它非常有用。

示例 3：查找具有特定模式的产品

我们来查找属于 ‘Notebook’ 类型且尺寸由两个字符指定（如 ‘A5’）的文具类商品。

SELECT product_name, category FROM products
WHERE product_name LIKE 'Notebook __';

这匹配 ‘Notebook ’ 后跟正好两个字符的字符串。

product_name	category
Notebook A5	Stationery

转义通配符字符

如果您需要在数据中搜索字面意义的 ’%’ 或 ’_’ 怎么办？例如，查找名称中包含 ‘50%’ 的产品。您可以使用 ESCAPE 子句来指定一个转义字符（escape character）。

示例 4：搜索字面意义的百分号

首先，我们添加一个名称中包含 ’%’ 的产品：

INSERT INTO products (product_name, category, stock_quantity)
VALUES ('Discount Coupon 50%', 'Offers', 1000);

现在，我们来查找它。我们将使用反斜杠 \ 作为转义字符。

SELECT product_name FROM products
WHERE product_name LIKE '%50\%%' ESCAPE '\\';

此查询将正确找到 ‘Discount Coupon 50%‘。

product_name
Discount Coupon 50%

最佳实践和性能

- **尽可能避免前置通配符：** `WHERE column LIKE '%value'` 这样的查询被称为 'un-SARGable'，因为它们无法有效利用 `column` 上的标准 B-tree 索引。数据库必须执行全表扫描（full table scan），这在大型表上会非常慢。
- **优先使用后置通配符：** `WHERE column LIKE 'value%'` 这样的查询可以使用 `column` 上的索引，并且速度快得多。
- **大小写敏感性：** `LIKE` 的行为（大小写敏感或不敏感）取决于列的排序规则（collation）。大多数默认排序规则（如 `utf8mb4_general_ci`）都是大小写不敏感的（`'a%'` 匹配 `'Apple'`）。要强制进行大小写敏感搜索，您可以使用二进制排序规则：`WHERE column LIKE 'value%' COLLATE utf8mb4_bin;`。
- **考虑全文搜索：** 对于文本中复杂的词语和短语搜索，`LIKE` 效率低下。MySQL 内置的全文搜索（Full-Text Search）（`MATCH() ... AGAINST()`）是更好、性能更高的替代方案。

高级模式匹配：REGEXP

对于 LIKE 无法处理的更复杂模式，MySQL 提供了 REGEXP（或 RLIKE）运算符，它使用正则表达式（regular expressions）。这是一个高级主题，但值得了解。

示例 5：使用 REGEXP 查找以数字结尾的产品

SELECT product_name FROM products
WHERE product_name REGEXP '[0-9]$';

此查询查找以数字 (0-9) 结尾的产品名称。