尧图网站建设 免费方案咨询
ARTICLE DETAIL

资讯详情

深耕网站建设、视觉设计与SEO优化的一线实战洞察。

首页/资讯中心/资讯详情

MySQL LIKE模糊查询：从基础语法到性能优化全解析

发布时间：2026/8/17 6:46:06 尧图网站建设
MySQL LIKE模糊查询：从基础语法到性能优化全解析

1. 从“大海捞针”到“精准定位”：为什么我们需要LIKE模糊查询

在数据库的世界里，数据就像一座巨大的图书馆。很多时候，我们并不是拿着精确的索书号去找书，而是凭着一些模糊的记忆：“书名里好像有‘编程’两个字”、“作者姓‘张’”、“是关于‘2023年’的”。这时候，精确匹配的=操作符就束手无策了。LIKE操作符，就是MySQL为我们提供的这把“模糊搜索”的钥匙，它允许我们使用通配符来匹配符合特定模式的数据，是数据检索中最常用、最基础，但也最容易用错的功能之一。

无论是用户在前端搜索框输入的关键词，还是后台需要筛选特定格式的记录（比如所有以.com结尾的邮箱），LIKE都扮演着核心角色。它看似简单，一个WHERE column LIKE ‘%pattern%’就搞定了，但背后却涉及到查询性能、索引失效、字符集匹配等一系列深水区问题。很多开发者初期觉得它“好用”，直到某天一张百万级数据表的模糊查询把数据库CPU打满，才开始重新审视这个老朋友。今天，我们就来彻底拆解MySQL中的LIKE模糊查询，不仅要知道怎么用，更要明白为什么这么用，以及如何用得高效、安全。

2. LIKE操作符的核心语法与两种通配符详解

LIKE操作符的语法结构非常直观，它用在WHERE子句中，基本形式如下：

SELECT column1, column2, ... FROM table_name WHERE columnN LIKE pattern;

这里的pattern（模式）是核心，它决定了匹配的规则。LIKE支持两种主要的通配符：%和_。理解它们的行为差异是正确使用模糊查询的第一步。

2.1 百分号%：代表零个、一个或多个字符

%是“万用牌”，它可以匹配任意长度的任意字符序列（包括零个字符）。你可以把它想象成搜索引擎中的“*”号。

使用场景与示例：

  1. 以特定字符串开头：查找所有姓“张”的用户。

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

    这会匹配“张三”、“张伟”、“张三丰”等。‘张%’表示第一个字必须是“张”，后面可以是任何字符或没有字符。

  2. 以特定字符串结尾：查找所有使用公司邮箱的员工。

    SELECT * FROM employees WHERE email LIKE '%@company.com';

    这会匹配zhangsan@company.com、lisi@company.com。‘%@company.com’表示前面可以是任意字符，但必须以@company.com结尾。

  3. 包含特定字符串：在产品描述中查找含有“防水”关键词的商品。

    SELECT * FROM products WHERE description LIKE '%防水%';

    这是最常用也最需要警惕的用法。‘%防水%’表示在字符串的任意位置出现“防水”二字都会被匹配，如“超强防水手机壳”、“不防水涂层说明”。

  4. 组合使用：查找文件名以“report_”开头，以“.pdf”结尾的文件记录。

    SELECT * FROM documents WHERE file_name LIKE 'report_%.pdf';

    这会匹配report_2023_q1.pdf、report_final.pdf，但不会匹配report.pdf（因为_必须匹配一个字符，见下文）。

2.2 下划线_：代表恰好一个任意字符

_是“填空牌”，它严格匹配单个任意字符。一个_就代表一个字符位置。

使用场景与示例：

  1. 固定格式的匹配：查找所有手机号前三位为“138”的用户（假设手机号字段为11位纯数字）。

    SELECT * FROM customers WHERE phone LIKE '138________';

    这里用了8个下划线，表示在“138”之后，必须恰好有8个数字字符。这会精确匹配13800138000，但不会匹配1380013800（少一位）或13800138000a（最后一位不是数字）。

  2. 与%组合，限定中间部分长度：查找所有第二和第三个字符为“ab”的字符串。

    SELECT * FROM some_table WHERE code LIKE '_ab%';

    这会匹配xabc、1ab123、cab，但不会匹配ab123（缺少第一个字符）或aab（第二个字符是‘a’不是‘b’）。

注意：通配符就是普通的字符，如果你想搜索的内容本身就包含%或_，需要使用ESCAPE关键字来定义转义字符。例如，查找包含“10%”折扣的字段：

SELECT * FROM promotions WHERE discount_text LIKE '%10!%%' ESCAPE '!';

这里指定!为转义符，!%表示匹配字面量的百分号。

3. 性能深渊：LIKE查询的索引失效与优化策略

这是LIKE模糊查询最关键的实战部分，也是初级开发者最容易踩坑的地方。很多人写了WHERE name LIKE ‘%张%’后发现查询慢如蜗牛，却不知其所以然。

3.1 为什么LIKE ‘%xxx%’会导致索引失效？

数据库索引（如B-Tree索引）的工作原理类似于字典的拼音目录。它按照字段值的顺序存储，可以快速定位到以某个值“开头”的数据。

  • LIKE ‘张%’（左匹配固定）：这相当于问“字典里所有拼音以‘zhang’开头的字在哪里？”。数据库可以利用索引的有序性，快速定位到第一个“张”开头的记录，然后向后顺序扫描，直到条件不满足为止。这种情况下，索引通常是有效的（前缀索引）。
  • LIKE ‘%张%’或LIKE ‘%张’（左模糊）：这相当于问“字典里所有包含‘zhang’这个拼音片段的字在哪里？”。索引目录对此无能为力，因为它无法告诉你中间或结尾有什么。数据库只能退回到最原始的方式——全表扫描，逐行检查每一行数据是否满足条件。当表数据量巨大时，性能灾难就发生了。

3.2 针对模糊查询的优化方案

面对必须使用模糊查询的场景，我们不能因噎废食，而是需要一些策略来优化。

方案一：尽可能使用右模糊（LIKE ‘张%’）这是最有效的优化。在设计搜索功能时，可以引导用户进行“前缀搜索”。例如，在搜索联系人时，输入“张”可以列出所有姓张的人，这比直接搜“三”要高效得多。很多成熟的搜索框都会默认或推荐这种模式。

方案二：使用覆盖索引减少IO即使索引不能用于快速定位（WHERE条件），它仍然可以用于“覆盖查询”。如果查询的列都包含在某个索引中，数据库可以直接从索引中读取数据，避免回表查询数据行，从而提升速度。

-- 假设在 (name, id) 上建立了联合索引 SELECT id, name FROM users WHERE name LIKE '%张%';

在这个查询中，id和name都在索引里，引擎可能会选择扫描整个索引而不是整个表，虽然还是扫描，但索引文件通常比数据文件小，IO代价更低。

方案三：使用全文索引（FULLTEXT Index）对于大文本字段（如文章内容、产品描述）的模糊搜索，LIKE ‘%关键词%’是绝对的下策。MySQL提供了专门的全文索引来应对这种场景。

  1. 创建全文索引（仅适用于MyISAM和InnoDB存储引擎，且MySQL 5.6+的InnoDB才支持）：
    ALTER TABLE articles ADD FULLTEXT INDEX ft_idx_content (content);
  2. 使用MATCH() ... AGAINST()进行搜索：
    SELECT * FROM articles WHERE MATCH(content) AGAINST('防水' IN NATURAL LANGUAGE MODE);
    全文索引不仅速度快，还支持自然语言模式、布尔模式等高级搜索，能根据相关性排序，是文本搜索的首选。

方案四：引入专业的搜索引擎对于海量数据、高并发、复杂条件的搜索需求（如电商网站的商品搜索），最终的解决方案是将数据同步到专业的搜索引擎中，如Elasticsearch或Solr。这些搜索引擎专为全文检索设计，支持分词、高亮、聚合、排序等复杂功能，性能远超数据库自带的模糊查询。

方案五：函数索引与反向存储这是一种比较“黑科技”的思路。如果查询模式固定为LIKE ‘%xxx’（右模糊），可以考虑将字段值反转后存储，并建立索引，查询时也反转查询条件。

-- 新增一个反向字段 ALTER TABLE users ADD COLUMN name_reverse VARCHAR(100) AS (REVERSE(name)) STORED; CREATE INDEX idx_name_reverse ON users(name_reverse); -- 查询以‘三’结尾的名字 SELECT * FROM users WHERE name_reverse LIKE REVERSE('三') + '%'; -- 等价于 WHERE name LIKE '%三'

这样就把右模糊转换成了左模糊，可以利用索引。但这种方法增加了存储和维护成本，需谨慎评估。

4. 实战中的边界问题与避坑指南

掌握了基本用法和性能优化，在实际编码中还会遇到一些意想不到的“坑”。

4.1 字符集与排序规则（Collation）的影响

LIKE匹配的结果严重依赖于字段的字符集和排序规则。排序规则决定了字符比较的规则，比如是否区分大小写、是否区分重音。

  • 区分大小写：如果字段的排序规则是utf8mb4_bin（二进制比较）或xxx_cs（Case-Sensitive），那么LIKE ‘a%’和LIKE ‘A%’会返回不同的结果。
  • 不区分大小写：如果排序规则是utf8mb4_general_ci或utf8mb4_unicode_ci（Case-Insensitive），那么LIKE ‘a%’会匹配到以 ‘A’ 或 ‘a’ 开头的记录。
  • 特殊字符：某些排序规则会将特定字符序列视为等价。例如，在utf8mb4_unicode_ci下，德语中的 ‘ß’ 可能与 ‘ss’ 等价。

我的经验是：在创建表时，务必根据业务需求明确指定字符集和排序规则。对于大多数中文互联网应用，使用utf8mb4字符集和utf8mb4_unicode_ci排序规则是通用且稳妥的选择，它支持完整的Unicode（包括Emoji）且不区分大小写。如果业务上必须区分，再考虑_bin或_cs规则。

4.2 NULL值的处理

LIKE对NULL值的处理是一个静默的陷阱。任何值与NULL进行LIKE比较，结果都是NULL，在WHERE条件中相当于FALSE。

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

如果某条记录的name字段是NULL，它将不会出现在结果集中。这符合SQL的三值逻辑，但有时会被忽略，导致数据统计不准确。如果你也需要找出NULL值，必须显式添加OR column IS NULL条件。

4.3 在编程语言中的参数化查询与注入风险

这是安全层面的重中之重。绝对不要直接拼接用户输入到SQL语句中！

-- 危险！SQL注入漏洞 String sql = “SELECT * FROM products WHERE name LIKE ‘%” + userInput + “%’”;

如果用户输入是‘ OR ‘1’=‘1，那么整个条件就会变成LIKE ‘%’ OR ‘1’=‘1%’，导致查询出所有数据，甚至可能引发更严重的后果。

正确的做法是使用参数化查询（预编译语句）：

  • 在Java (JDBC)中：
    String sql = “SELECT * FROM products WHERE name LIKE ?”; PreparedStatement stmt = connection.prepareStatement(sql); stmt.setString(1, “%” + userInput + “%”); // 通配符作为参数的一部分传入
  • 在Python (PyMySQL/pymysql)中：
    sql = “SELECT * FROM products WHERE name LIKE %s” cursor.execute(sql, (“%” + user_input + “%”,)) # 注意参数是元组
  • 在MyBatis（#{}）中：
    <select id=“search” resultType=“Product”> SELECT * FROM products WHERE name LIKE CONCAT(‘%’, #{keyword}, ‘%’) </select>
    注意，这里使用的是#{}而非${}。#{}是预编译的，安全的；${}是字符串替换，存在注入风险。网上热词中提到的#{}模糊查询，指的就是这种安全的做法。

参数化查询会将用户输入始终视为数据，而非SQL代码的一部分，从根本上杜绝了SQL注入。

4.4 与正则表达式REGEXP/RLIKE的对比

LIKE简单但功能有限。当模式更复杂时，可以考虑使用REGEXP（或同义词RLIKE）。

-- 查找名字以‘张’、‘李’或‘王’开头的人 SELECT * FROM users WHERE name REGEXP ‘^(张|李|王)’; -- 使用LIKE需要写多个OR -- 查找邮箱格式不正确的记录（简单示例） SELECT * FROM users WHERE email NOT REGEXP ‘^[A-Za-z0-9._%+-]+@[A-Za-z0-9.-]+\.[A-Za-z]{2,}$’;

但是，请注意：

  1. REGEXP的功能强大得多，但语法也更复杂，性能通常比简单的LIKE更差，尤其是在数据量大时。
  2. REGEXP同样无法使用标准B-Tree索引进行优化。在MySQL 8.0+中，可以针对REGEXP使用函数索引，但LIKE在某些情况下（左匹配固定）可以利用索引。
  3. 除非模式复杂到必须用正则，否则优先使用LIKE。对于简单的“包含”、“开头”、“结尾”查询，LIKE是更清晰、更可能被优化的选择。

5. 进阶应用：在存储过程、触发器和复杂查询中的实践

LIKE不仅用于简单的SELECT，它在数据库编程中也无处不在。

5.1 在存储过程中进行动态模糊查询

存储过程中可能需要根据传入参数进行灵活的模糊查询。这里的关键是安全地构建SQL字符串。

DELIMITER // CREATE PROCEDURE SearchProducts(IN keyword VARCHAR(255)) BEGIN -- 使用CONCAT安全地构建模式，注意参数过滤 SET @pattern = CONCAT(‘%’, REPLACE(keyword, ‘%’, ‘\%’), ‘%’); -- 使用用户定义变量和预处理语句防止注入 SET @sql = CONCAT(‘SELECT * FROM products WHERE name LIKE ? OR description LIKE ?’); PREPARE stmt FROM @sql; SET @kw = @pattern; EXECUTE stmt USING @kw, @kw; DEALLOCATE PREPARE stmt; END // DELIMITER ;

这里使用了REPLACE对输入中的通配符进行了转义，并使用预处理语句PREPARE和EXECUTE来执行，确保了安全性。

5.2 在触发器中基于模式匹配进行逻辑判断

触发器可以在数据变更前后执行逻辑。LIKE可以用于条件判断。

DELIMITER // CREATE TRIGGER before_insert_user BEFORE INSERT ON users FOR EACH ROW BEGIN -- 检查新插入的邮箱是否为管理员邮箱 IF NEW.email LIKE ‘%@admin.company.com’ THEN SET NEW.role = ‘admin’; -- 可以记录日志或进行其他操作 INSERT INTO admin_audit_log (user_id, action) VALUES (NEW.id, ‘Auto-assigned admin role’); END IF; END // DELIMITER ;

5.3 在复杂查询中与其他子句联用

LIKE可以和其他SQL子句无缝结合，构建强大的查询。

  • 与CASE WHEN结合：在查询结果中根据模式匹配添加标记列。
    SELECT name, email, CASE WHEN email LIKE ‘%@gmail.com’ THEN ‘Gmail’ WHEN email LIKE ‘%@outlook.com’ THEN ‘Outlook’ ELSE ‘Other’ END AS email_provider FROM users;
  • 与聚合函数和GROUP BY结合：统计不同类别的数量。
    SELECT CASE WHEN url LIKE ‘%/products/%’ THEN ‘产品页’ WHEN url LIKE ‘%/blog/%’ THEN ‘博客页’ ELSE ‘其他页面’ END AS page_type, COUNT(*) AS visit_count FROM site_logs GROUP BY page_type;
  • 在UPDATE或DELETE语句中使用：批量更新或删除符合特定模式的数据。
    -- 将所有临时邮箱用户的状态置为无效 UPDATE users SET status = ‘inactive’ WHERE email LIKE ‘%temp%@%’ OR email LIKE ‘%test%@%’;

    重要提示：执行此类操作前，务必先使用SELECT语句验证匹配的结果，确认无误后再执行UPDATE或DELETE，避免误操作。

6. 性能监控与诊断：当LIKE查询变慢时该怎么办

即使我们遵循了优化策略，在生产环境中，随着数据增长，模糊查询仍可能变慢。这时需要一套诊断方法。

6.1 使用EXPLAIN分析查询执行计划

这是MySQL性能调优的必备工具。在查询语句前加上EXPLAIN或EXPLAIN FORMAT=JSON。

EXPLAIN SELECT * FROM orders WHERE order_no LIKE ‘202310%’;

关注结果中的几个关键字段：

  • type：这是最重要的指标之一。如果看到ALL，就表示全表扫描，对于大表来说性能极差。我们期望看到的是range（范围扫描，对于LIKE ‘xxx%’有可能）或index（全索引扫描，比全表扫描好）。
  • key：显示MySQL实际决定使用的索引。如果为NULL，说明没有使用索引。
  • rows：MySQL预估需要扫描的行数。这个数字越接近实际结果集越好。
  • Extra：包含额外信息。如果出现Using where，表示服务器在存储引擎检索行后再进行过滤。如果出现Using index condition，是个好现象，表示使用了索引条件下推。

对于LIKE ‘%xxx%’查询，EXPLAIN结果中的type很可能是ALL，key为NULL，这就是性能问题的直接证据。

6.2 慢查询日志定位罪魁祸首

如果应用整体变慢，需要开启MySQL的慢查询日志，它可以帮助你捕获所有执行时间超过指定阈值（如2秒）的SQL语句。

  1. 在MySQL配置文件（如my.cnf）中设置：
    slow_query_log = 1 slow_query_log_file = /var/log/mysql/mysql-slow.log long_query_time = 2
  2. 重启MySQL服务或动态设置。
  3. 分析慢日志文件，使用mysqldumpslow工具或pt-query-digest（Percona Toolkit）进行汇总分析，找出最耗时的模糊查询。

6.3 针对性优化措施

根据诊断结果，可以采取以下措施：

  1. 重写查询：再次审视业务，是否真的需要LIKE ‘%xxx%’？能否改为前缀匹配LIKE ‘xxx%’？
  2. 增加或调整索引：对于必须的左匹配固定模式，确保字段上有索引。对于LIKE ‘%xxx’，考虑前面提到的“反向索引”方案。
  3. 应用层缓存：对于不常变化的热点模糊查询结果（如热门搜索词），可以在应用层（如Redis）进行缓存，定时更新。
  4. 读写分离与分库分表：对于超大规模数据，终极方案是进行架构升级，将查询压力分散到只读从库，或者对数据进行水平拆分。
  5. 引入异步搜索：对于实时性要求不高的搜索，可以将其放入消息队列，由后台任务处理，结果生成后通知前端。

模糊查询是数据库操作中的一把双刃剑，它提供了极大的灵活性，但也对性能构成了持续挑战。我的体会是，在设计之初就要对数据的增长和查询模式有预判，为高频的模糊查询字段建立合适的索引，并明确其使用边界。在代码层面，坚持使用参数化查询是底线。当性能问题出现时，从EXPLAIN开始，一步步分析，从查询语句、索引、到数据库架构，层层递进地寻找解决方案。记住，没有银弹，只有最适合当前业务场景的权衡之策。

网站建设 视觉设计 SEO优化
返回列表