MySQL LIKE操作符与通配符过滤技术

MySQL LIKE 操作符基础

LIKE 操作符简介

在 MySQL 数据库中，LIKE 操作符是一种用于字符串匹配的工具，它允许我们在查询时筛选出符合特定模式的字符串数据。与其他精确匹配操作符（如 =）不同，LIKE 操作符结合通配符使用，能够实现更灵活的模糊匹配查询。

在 SQL 查询语句中，LIKE 操作符通常用于 WHERE 子句里，用来限定查询结果集。例如，假设有一个名为 employees 的表，其中有一个 name 列存储员工姓名。如果我们想查询所有姓 “张” 的员工，就可以使用 LIKE 操作符。

SELECT * FROM employees WHERE name LIKE '张%';

上述查询语句会返回 name 列中以 “张” 开头的所有员工记录。

LIKE 操作符的语法结构

LIKE 操作符的基本语法格式为：

expression LIKE pattern [ESCAPE escape_character]

expression：这是要进行匹配的字符串表达式，可以是列名，也可以是通过字符串拼接等方式生成的表达式。
pattern：指定的匹配模式，其中会包含通配符。
ESCAPE escape_character（可选）：用于指定转义字符。当模式中需要匹配通配符本身时，就需要用到转义字符。如果不使用 ESCAPE 子句，通配符在模式中就按其特殊含义进行匹配。

例如，我们要在一个 products 表的 product_name 列中查询名称包含 “-” 符号（假设 “-” 在这里不被视为通配符，而是产品名称的一部分）的产品，并且 “-” 后面紧跟着 “A”，可以这样写查询语句：

SELECT * FROM products WHERE product_name LIKE '%-%A' ESCAPE '-';

在这个例子中，“-” 被指定为转义字符，这样 “-%A” 中的 “-” 就不再被视为通配符，而是作为普通字符进行匹配。

通配符的种类与使用

百分号（%）通配符

百分号（%）通配符在 LIKE 操作符中代表任意长度（包括 0 长度）的任意字符序列。它在模糊匹配中非常灵活且常用。

匹配字符串开头

假设我们有一个 customers 表，其中 city 列存储客户所在城市。如果我们要查询所有居住在以 “北” 开头城市的客户，可以使用以下查询：

SELECT * FROM customers WHERE city LIKE '北%';

这个查询会返回城市名称以 “北” 开头的所有客户记录，比如 “北京”、“北海” 等城市的客户。

匹配字符串结尾

若要查询所有居住在以 “市” 结尾城市的客户，查询语句如下：

SELECT * FROM customers WHERE city LIKE '%市';

这样就会匹配到像 “北京市”、“上海市” 等城市的客户记录。

匹配字符串中间

% 通配符也可以用于匹配字符串中间的部分。例如，我们要查询城市名称中包含 “京” 字的客户，无论 “京” 字在城市名的什么位置：

SELECT * FROM customers WHERE city LIKE '%京%';

这个查询不仅会匹配到 “北京”、“南京”，还会匹配到像 “京山市” 等城市的客户记录。

下划线（_）通配符

下划线（_）通配符在 LIKE 操作符中代表任意单个字符。与 % 通配符不同，_ 通配符只能匹配一个字符。

匹配固定长度字符串中的单个字符

假设我们有一个 phone_numbers 表，其中 phone_number 列存储电话号码。如果电话号码格式为 “3 位区号 - 8 位号码”，且我们知道某个电话号码的区号第三位是 “5”，但不知道其他信息，可以使用如下查询：

SELECT * FROM phone_numbers WHERE phone_number LIKE '___5-%';

这里前三个 _ 代表区号的前三位任意字符，而 5 是我们确定的区号第三位字符，后面的 - 是电话号码格式中的分隔符，再后面的 % 表示 8 位电话号码部分的任意字符序列。

替换单个未知字符

再比如，在一个 words 表中有 word 列存储单词。如果我们要查找所有长度为 5 且第三个字母为 “e” 的单词，可以使用：

SELECT * FROM words WHERE word LIKE '__e__';

这个查询会匹配像 “plane”、“slope” 等符合条件的单词记录。

方括号（[]）通配符

方括号（[]）通配符用于指定一个字符集合，只要匹配的字符是集合中的任意一个，就算匹配成功。

匹配字符集合中的单个字符

假设有一个 employees 表，gender 列存储员工性别，取值为 “男”、“女” 以及一些错误录入的非中文字符。如果我们要查询性别为 “男” 或 “女” 的员工，可以使用：

SELECT * FROM employees WHERE gender LIKE '[男女]';

这个查询只会返回 gender 列值为 “男” 或者 “女” 的员工记录。

匹配字符范围

方括号内还可以指定字符范围。例如，在一个 students 表的 grade 列存储学生成绩等级，取值为从 “A” 到 “F”。如果我们要查询成绩等级为 “C” 到 “E” 的学生：

SELECT * FROM students WHERE grade LIKE '[C-E]';

此查询会返回成绩等级在 “C”、“D”、“E” 的学生记录。

脱字符（^）通配符（在方括号内使用）

脱字符（^）在方括号 [] 内使用时，表示否定的意思，即匹配不在指定字符集合或范围中的单个字符。

匹配不在字符集合中的单个字符

还是以 students 表的 grade 列为例，如果我们要查询成绩等级不是 “A” 和 “B” 的学生：

SELECT * FROM students WHERE grade LIKE '[^AB]';

这个查询会返回成绩等级除了 “A” 和 “B” 之外的所有学生记录。

匹配不在字符范围中的单个字符

假设在一个 characters 表的 char_value 列存储单个字符，我们要查询所有不在字母 “a” 到 “m” 范围内的字符：

SELECT * FROM characters WHERE char_value LIKE '[^a-m]';

此查询会返回所有满足条件的字符记录。

LIKE 操作符与通配符的性能考量

通配符位置对性能的影响

在使用 LIKE 操作符和通配符时，通配符的位置会显著影响查询性能。

通配符在开头

当通配符 % 位于模式的开头时，MySQL 通常无法使用索引进行优化。例如，以下查询：

SELECT * FROM products WHERE product_name LIKE '%手机';

这个查询要查找名称中结尾为 “手机” 的产品。由于 % 在开头，MySQL 必须对 product_name 列的每一行数据进行全表扫描，将每一行的字符串与模式进行匹配，这在大数据量的表中性能会非常低下。

通配符在中间或结尾

如果通配符 % 位于模式的中间或结尾，MySQL 有可能使用索引。例如：

SELECT * FROM products WHERE product_name LIKE '华为%';

在此查询中，MySQL 可以利用 product_name 列上的索引（如果存在），从索引中快速定位到以 “华为” 开头的记录，然后再对这些记录进行完整匹配，这样相比全表扫描性能会有很大提升。同样，对于 LIKE '%手机' 这种通配符在结尾的情况，如果表数据量较大且没有合适的索引优化，也可能导致性能问题，但相对通配符在开头的情况，通过一些索引策略仍有优化空间。

索引对 LIKE 操作性能的作用

在 MySQL 中，为经常用于 LIKE 查询的列创建索引可以显著提升查询性能，但要注意索引的类型和使用方式。

普通索引

对于 LIKE 查询，普通索引在一定程度上可以发挥作用。例如，在 products 表的 product_name 列上创建普通索引：

CREATE INDEX idx_product_name ON products(product_name);

创建索引后，对于类似 SELECT * FROM products WHERE product_name LIKE '小米%'; 的查询，MySQL 可以利用该索引快速定位到以 “小米” 开头的记录，从而加快查询速度。然而，对于 LIKE '%小米' 这种通配符在开头的查询，普通索引无法有效优化，仍然需要全表扫描。

前缀索引

当列中的数据较长时，创建前缀索引是一种更有效的优化方式。例如，在一个 long_text 表的 content 列存储长文本内容，我们经常使用 LIKE 操作查询以某些字符串开头的记录。如果直接为 content 列创建完整索引，可能会占用大量的存储空间，并且查询性能提升有限。此时可以创建前缀索引：

CREATE INDEX idx_content ON long_text(content(10));

这里的 (10) 表示只对 content 列的前 10 个字符创建索引。对于类似 SELECT * FROM long_text WHERE content LIKE '重要信息%'; 的查询，如果 “重要信息” 长度不超过 10 个字符，MySQL 可以利用这个前缀索引快速定位记录，既节省了索引空间，又在一定程度上提升了查询性能。

避免全表扫描的优化策略

为了避免在 LIKE 操作中进行全表扫描，除了合理使用索引外，还可以采用一些其他优化策略。

分段查询

当使用通配符在开头的 LIKE 查询无法避免时，可以考虑分段查询。例如，要查询所有名称中包含 “苹果” 的产品，原始查询为 SELECT * FROM products WHERE product_name LIKE '%苹果%';。我们可以先查询以 “苹果” 开头的产品，再查询中间包含 “苹果” 但不以 “苹果” 开头的产品。

-- 先查询以 “苹果” 开头的产品
SELECT * FROM products WHERE product_name LIKE '苹果%';
-- 再查询中间包含 “苹果” 但不以 “苹果” 开头的产品
SELECT * FROM products WHERE product_name NOT LIKE '苹果%' AND product_name LIKE '%苹果%';

通过这种方式，可以在一定程度上减少单次查询的数据量，提升整体查询效率。

利用全文索引

对于文本数据量较大且需要进行复杂模糊匹配的场景，全文索引是更好的选择。MySQL 提供了全文索引功能，相比普通索引，它在处理长文本和复杂查询时性能更优。首先要确保表的存储引擎为支持全文索引的类型（如 InnoDB），然后创建全文索引：

ALTER TABLE products ADD FULLTEXT(product_name);

创建全文索引后，查询时需要使用 MATCH AGAINST 语法，而不是 LIKE。例如：

SELECT * FROM products WHERE MATCH(product_name) AGAINST('苹果' IN NATURAL LANGUAGE MODE);

这种方式不仅性能更好，而且还支持更高级的文本搜索功能，如自然语言处理、多词匹配等。

LIKE 操作符与通配符在实际场景中的应用

在用户信息查询中的应用

在一个用户管理系统中，用户表 users 包含 username（用户名）、email（邮箱）等字段。

根据用户名模糊查询

如果管理员想要查找所有用户名中包含 “admin” 字样的用户，可以使用 LIKE 操作符：

SELECT * FROM users WHERE username LIKE '%admin%';

这对于排查系统中可能与管理员相关的用户账号非常有用。

根据邮箱后缀查询

假设要查询所有使用 “gmail.com” 邮箱的用户，可以这样查询：

SELECT * FROM users WHERE email LIKE '%gmail.com';

通过这种方式，网站运营者可以了解使用特定邮箱服务提供商的用户群体情况。

在商品搜索中的应用

在电商平台的商品数据库中，商品表 products 包含 product_name（商品名称）、description（商品描述）等字段。

商品名称模糊搜索

当用户在搜索框中输入部分商品名称时，例如输入 “手机”，系统可以使用 LIKE 操作符来返回相关商品：

SELECT * FROM products WHERE product_name LIKE '%手机%';

为了提升查询性能，可以在 product_name 列上创建索引。

商品描述精准匹配部分关键词

如果用户想要查找商品描述中包含 “高清屏幕” 且价格在一定范围内的商品，可以进行如下查询：

SELECT * FROM products WHERE description LIKE '%高清屏幕%' AND price BETWEEN 1000 AND 5000;

这样可以帮助用户快速筛选出符合特定描述和价格范围的商品。

在日志分析中的应用

在系统日志表 system_logs 中，包含 log_message（日志信息）、log_time（日志时间）等字段。

根据日志信息关键词查询

运维人员可能需要查找所有包含 “错误” 关键词的日志记录，以排查系统故障：

SELECT * FROM system_logs WHERE log_message LIKE '%错误%';

通过这种方式，可以快速定位到可能存在问题的日志，加快故障排查速度。

按日期范围和关键词联合查询

如果要查找某一天内包含 “数据库连接” 关键词的日志记录，可以使用如下查询：

SELECT * FROM system_logs WHERE log_time BETWEEN '2023-10-01 00:00:00' AND '2023-10-01 23:59:59' AND log_message LIKE '%数据库连接%';

这有助于分析特定时间段内与数据库连接相关的系统状况。

LIKE 操作符与通配符使用的常见问题及解决方法

字符集和编码问题

在使用 LIKE 操作符时，字符集和编码可能会导致匹配结果不准确。

不同字符集下的匹配差异

例如，在一个数据库中，表 text_data 的 text_column 列使用 utf8mb4 字符集存储文本数据，而查询语句在不同字符集环境下执行时可能出现问题。如果在 latin1 字符集环境下执行如下查询：

SELECT * FROM text_data WHERE text_column LIKE 'äöü';

由于 latin1 字符集对某些特殊字符（如 “ä”、“ö”、“ü”）的编码与 utf8mb4 不同，可能无法正确匹配到 utf8mb4 字符集中存储的相应数据。

解决方法

确保数据库、表以及查询环境使用相同的字符集和编码。可以通过以下方式查看和修改字符集：

查看数据库字符集：SHOW VARIABLES LIKE 'character_set_database';
修改数据库字符集：ALTER DATABASE database_name CHARACTER SET = utf8mb4;
查看表字符集：SHOW CREATE TABLE table_name;
修改表字符集：ALTER TABLE table_name CONVERT TO CHARACTER SET utf8mb4;

转义字符的使用不当

在需要匹配通配符本身或者特殊字符时，转义字符使用不当会导致查询结果错误。

错误的转义字符使用

假设在一个 file_names 表的 file_name 列存储文件名，文件名中可能包含 “%” 字符。如果要查询文件名中包含 “%20” 的文件，错误的查询可能如下：

SELECT * FROM file_names WHERE file_name LIKE '%\%20%';

这里错误地将 \ 作为转义字符，但如果 MySQL 没有设置正确的转义规则，\ 可能不会被识别为转义字符，导致查询结果不准确。

解决方法

正确指定转义字符。例如，将 “-” 指定为转义字符：

SELECT * FROM file_names WHERE file_name LIKE '%-%20%' ESCAPE '-';

这样就可以确保 “%” 被正确转义，按照普通字符进行匹配。

性能问题的进一步排查与优化

即使采取了索引等优化措施，LIKE 操作有时仍可能出现性能问题，需要进一步排查和优化。

复杂查询中的性能瓶颈

在复杂查询中，例如多个 LIKE 操作与其他条件组合时，可能会出现性能瓶颈。比如：

SELECT * FROM orders
JOIN customers ON orders.customer_id = customers.customer_id
WHERE orders.order_status LIKE '%完成%' AND customers.city LIKE '%上海%' AND orders.total_amount > 1000;

在这种情况下，MySQL 需要处理多个条件的匹配和表连接操作，性能可能受到影响。

解决方法

分析查询执行计划：使用 EXPLAIN 关键字查看查询执行计划，了解 MySQL 如何执行查询，找出性能瓶颈点。例如：EXPLAIN SELECT * FROM orders JOIN customers ON orders.customer_id = customers.customer_id WHERE orders.order_status LIKE '%完成%' AND customers.city LIKE '%上海%' AND orders.total_amount > 1000;
优化索引策略：根据查询执行计划，调整索引。可能需要创建复合索引，例如在 orders 表的 (order_status, total_amount) 列和 customers 表的 city 列上创建复合索引：

CREATE INDEX idx_orders ON orders(order_status, total_amount);
CREATE INDEX idx_customers ON customers(city);

考虑查询重写：对于复杂的 LIKE 查询，可以尝试重写查询，将复杂条件分解为多个简单查询，或者使用更高效的查询方式，如全文索引等。

通过深入理解 LIKE 操作符与通配符的原理、正确使用它们以及注意常见问题和优化策略，我们能够在 MySQL 数据库开发中更有效地进行字符串匹配查询，提升数据库应用的性能和效率。