1. 项目概述为什么模糊查询是数据库的“搜索框”在数据库的世界里精确匹配是基本功但现实业务中我们更多时候面对的是“记不全”、“不确定”的查询需求。比如用户想找所有姓“张”的客户或者产品名称里包含“旗舰”的商品。这时候LIKE操作符就成了我们手中最趁手的“搜索框”。它不像那样要求严丝合缝而是允许使用通配符进行模式匹配从而在数据海洋中捞出我们想要的那部分。LIKE是 MySQL 乃至所有 SQL 数据库中最基础、最常用的字符串匹配操作符。它的核心价值在于处理非精确查询场景极大地提升了数据检索的灵活性。无论是后台管理系统的筛选、电商网站的商品搜索还是内容管理系统的文章查找背后都离不开LIKE的身影。对于开发者、数据分析师甚至运维人员来说深入理解LIKE的用法、性能特性和避坑技巧是高效使用数据库的必备技能。这篇文章我就结合自己多年踩坑和调优的经验带你彻底搞懂 MySQL 中的LIKE模糊查询。2. LIKE 操作符的核心语法与通配符详解LIKE操作符的语法非常简单但其威力完全来自于两个通配符%和_。理解它们是玩转模糊查询的第一步。2.1 基础语法结构LIKE通常用在WHERE子句中基本格式如下SELECT column1, column2, ... FROM table_name WHERE columnN LIKE pattern;这里的pattern模式就是包含了通配符的匹配字符串。查询会返回所有columnN字段值符合该模式的记录。2.2 百分号%匹配任意多个字符包括零个字符这是最常用的通配符代表任意长度的字符串长度可以为0。经典使用场景与示例以特定字符串开头查找所有以“北京”开头的客户地址。SELECT * FROM customers WHERE address LIKE 北京%;这会匹配“北京市海淀区”、“北京朝阳区CBD”等。以特定字符串结尾查找所有以“.com”结尾的邮箱。SELECT * FROM users WHERE email LIKE %.com;匹配userexample.com但不匹配userexample.org。包含特定字符串在产品名中查找含有“手机”的商品。SELECT * FROM products WHERE product_name LIKE %手机%;这会匹配“智能手机”、“华为手机壳”、“小米手机充电器”等。这是最消耗性能的写法之一需要特别注意我们后面会详细分析。匹配任意字符%本身可以匹配空字符串。LIKE %会匹配该字段所有非NULL的值相当于没有过滤条件但效率低于不加条件。2.3 下划线_匹配单个字符下划线通配符只匹配一个确切的字符。它在需要固定长度或特定位置字符匹配时非常有用。经典使用场景与示例固定长度的模糊匹配查找股票代码为6位且以“600”开头的所有股票A股主板。SELECT * FROM stocks WHERE stock_code LIKE 600___;这里三个_匹配任意三个字符因此会匹配“600001”、“600519”等。匹配特定位置的单个字符查找第二个字符是“A”的四个字母的英文名。SELECT * FROM employees WHERE english_name LIKE _A__;可能匹配“Mary”、“Jack”如果‘a’被匹配等。组合使用查找文件名类似“report_2024_01.pdf”的文档但月份可能是个位数或两位数。SELECT * FROM documents WHERE file_name LIKE report_2024_%.pdf;这里用%来灵活匹配月份和日期部分。注意通配符就是普通的字符如果你想搜索的内容本身就包含%或_需要使用转义字符。默认的转义字符是反斜线\。例如查找字段中包含“50%”的记录SELECT * FROM discounts WHERE note LIKE %50\%%;第一个%是通配符50\%匹配字面值“50%”最后一个%又是通配符。你也可以使用ESCAPE子句自定义转义符如LIKE %50!%% ESCAPE !。3. LIKE 查询的性能陷阱与深度优化策略如果说LIKE是便利的搜索框那么以%开头的查询就是堵在这个搜索框前的“减速带”。很多新手开发者会抱怨数据库慢却不知道问题往往出在这里。理解其背后的原理是进行优化的关键。3.1 为什么LIKE ‘%关键字%’会导致全表扫描这要从数据库索引的工作原理说起。最常见的 B-Tree 索引其数据结构就像一本字典的目录它是按照字段值的从头开始的顺序排列的。当你查询WHERE name LIKE ‘张%’时数据库可以快速定位到索引中第一个以“张”开头的条目然后顺序向后扫描直到遇到不以“张”开头的条目为止效率很高。但是当你使用WHERE name LIKE ‘%伟’或WHERE name LIKE ‘%国%’时问题就来了。索引不知道哪些值的中间或结尾包含“伟”或“国”。数据库无法利用索引的有序性进行快速定位它别无选择只能从头到尾扫描整个索引全索引扫描或者整个表全表扫描逐条记录去判断是否匹配。当表数据量达到百万、千万级时这种查询的耗时将是灾难性的。3.2 核心优化方案与实战技巧优化LIKE查询核心思路就一条尽量避免以通配符%开头。1. 最优先方案调整查询模式使用前缀匹配如果业务允许这是最有效的办法。与产品经理或业务方沟通将搜索框的默认行为或高级搜索选项改为“前缀匹配”。优化前SELECT ... WHERE product_name LIKE %手机%优化后SELECT ... WHERE product_name LIKE 手机%仅仅是把开头的%去掉就可能让查询从秒级降到毫秒级前提是product_name字段上有索引。2. 利用覆盖索引减少回表即使必须使用LIKE ‘%xx%’也可以通过覆盖索引来提升性能。覆盖索引是指一个索引包含了查询所需要的所有字段。-- 假设在 product_name 上有一个单列索引 SELECT product_id, product_name FROM products WHERE product_name LIKE %旗舰%; -- 假设有一个联合索引 (product_name, price, stock) SELECT product_name, price FROM products WHERE product_name LIKE %旗舰%;在第二个例子中查询的字段product_name和price都包含在联合索引(product_name, price, stock)里。虽然LIKE ‘%旗舰%’仍然需要扫描整个索引树但数据库引擎只需要读取索引文件而无需再根据主键ID去“回表”查询数据行减少了磁盘I/O速度会快很多。3. 使用全文索引应对复杂文本搜索对于大段文本如文章内容、产品描述的搜索LIKE是完全不合适的。MySQL 提供了专门的全文索引FULLTEXT Index适用于MyISAM和InnoDB引擎5.6。创建全文索引ALTER TABLE articles ADD FULLTEXT INDEX ft_idx_content (content);使用 MATCH...AGAINST 查询SELECT * FROM articles WHERE MATCH(content) AGAINST(数据库优化 IN NATURAL LANGUAGE MODE);全文索引不仅速度快还支持自然语言模式、布尔模式等高级搜索功能能按相关性排序是替代LIKE ‘%...%’进行文本搜索的终极方案。4. 引入搜索引擎Elasticsearch/Solr当数据量极大且搜索需求复杂如分词、同义词、高亮、聚合统计时应将搜索业务剥离使用 Elasticsearch 或 Solr 等专业搜索引擎。它们基于倒排索引是为海量数据检索而生的。数据库只作为源数据存储通过同步机制将数据导入搜索引擎由搜索引擎来承担复杂的查询压力。5. 其他辅助优化技巧函数索引MySQL 8.0 如果你经常需要查询某个字段的反转内容可以创建一个函数索引。例如为了优化WHERE content LIKE ‘%abc’可以创建一个反转字符串的索引。CREATE INDEX idx_reverse_content ON your_table ((REVERSE(content))); -- 查询时也使用反转函数 SELECT * FROM your_table WHERE REVERSE(content) LIKE REVERSE(%abc); -- 实际是 ‘cba%’合理的数据类型 确保用于LIKE查询的字段是CHAR/VARCHAR/TEXT等字符串类型而不是数字或日期类型避免隐式转换导致索引失效。控制结果集大小 务必结合LIMIT子句避免一次返回过多数据。同时考虑是否真的需要SELECT *只选择必要的字段。4. 不同场景下的 LIKE 查询实战与避坑指南掌握了原理和优化策略我们来看看在实际开发中LIKE如何与其他 SQL 元素配合以及有哪些常见的“坑”。4.1 结合其他条件与排序LIKE可以和其他WHERE条件通过AND、OR组合使用也可以和ORDER BY、GROUP BY一起使用。-- 组合查询查找状态为活跃且姓名包含“明”的用户按注册时间倒序只取前10条 SELECT user_id, name, email FROM users WHERE status active AND name LIKE %明% ORDER BY registered_at DESC LIMIT 10; -- 使用 OR查找邮箱是 gmail.com 或者用户名包含‘admin’的管理员 SELECT * FROM admins WHERE email LIKE %gmail.com OR username LIKE %admin%;注意当OR条件中有一个条件无法使用索引时整个查询可能无法有效利用索引。对于上述OR的例子如果username有索引而email没有优化器可能选择全表扫描。这种情况下有时拆分成两个查询用UNION合并效果更好。4.2 NULL 值的处理这是一个容易被忽略的点LIKE无法匹配到NULL值。SELECT * FROM table WHERE column LIKE %something%;如果某条记录的column字段是NULL那么它不会被上述查询条件选中。这与NULL在 SQL 中的三值逻辑TRUE, FALSE, UNKNOWN有关任何与NULL的比较结果都是UNKNOWN。如果需要包含NULL必须显式添加条件SELECT * FROM table WHERE column LIKE %something% OR column IS NULL;4.3 字符集与大小写敏感问题LIKE的匹配行为受数据库**字符集Charset和排序规则Collation**的影响。大小写敏感如果排序规则是utf8mb4_bin或xxx_cscs 表示 case-sensitive那么LIKE ‘a%’不会匹配到“Apple”。如果是utf8mb4_general_ci或xxx_cici 表示 case-insensitive则会匹配。通常我们使用ci规则实现不区分大小写的搜索。多字节字符对于中文等字符一个_通配符匹配一个字符如一个汉字而不是一个字节。这点在大多数现代字符集如UTF-8下是符合直觉的。实操心得在创建数据库或表时最好显式指定统一的字符集和排序规则避免后续出现乱码或匹配不一致的问题。推荐使用utf8mb4字符集和utf8mb4_unicode_ci或utf8mb4_general_ci排序规则以支持完整的 Unicode 并实现不区分大小写的比较。4.4 在应用程序中构建 LIKE 参数的安全隐患这是安全层面的一个重要坑。绝对不要直接在应用程序中拼接字符串来构造LIKE语句# 危险SQL注入漏洞 user_input request.get(keyword) sql fSELECT * FROM products WHERE name LIKE %{user_input}%如果用户输入keyword为‘%; DROP TABLE users; --拼接后的 SQL 将变成灾难。正确的做法永远是使用参数化查询Prepared Statements。# 安全使用参数化查询 user_input request.get(keyword) search_pattern f%{user_input}% sql SELECT * FROM products WHERE name LIKE %s cursor.execute(sql, (search_pattern,))在参数化查询中数据库驱动会正确处理参数中的特殊字符包括通配符%和_将它们作为字面值的一部分进行匹配从而从根本上杜绝 SQL 注入。5. 超越 LIKE更高效的模糊查询替代方案当LIKE尤其是前导通配符查询成为性能瓶颈时我们必须考虑其他方案。5.1 正则表达式 REGEXP / RLIKEMySQL 支持REGEXP或RLIKE操作符进行正则表达式匹配功能比LIKE强大得多。-- 查找名字以‘张’、‘王’、‘李’开头的人 SELECT * FROM persons WHERE name REGEXP ^(张|王|李); -- 查找邮箱格式不正确的记录简易版 SELECT * FROM users WHERE email NOT REGEXP ^[A-Za-z0-9._%-][A-Za-z0-9.-]\\.[A-Za-z]{2,}$;注意事项性能REGEXP通常比LIKE更慢因为它实现更复杂。它同样无法使用普通的B-Tree索引。在MySQL 8.0中可以针对确定性的表达式创建函数索引来加速某些REGEXP查询但场景有限。语法它使用的是POSIX风格的正则与编程语言中常见的Perl风格略有不同例如转义需要两个反斜线\\。用途适用于复杂的模式验证或匹配不适用于需要高频、大数据量的简单模糊搜索。5.2 全文索引与 MATCH...AGAINST如前所述这是处理自然语言文本搜索的官方解决方案。除了速度快它还有两大优势相关性排序结果会按与搜索词的相关性自动评分你可以ORDER BY这个评分。停用词处理会自动忽略“的”、“了”、“a”、“the”这类常见但无实际搜索意义的词。配置注意全文索引有最小词长ft_min_word_len等配置参数需要根据语言调整。对于中文默认设置不适用因为中文没有空格分词。你需要配合中文分词插件如ngram来使用。-- 创建 ngram 全文索引 (MySQL 5.7) CREATE TABLE articles ( id INT PRIMARY KEY, title TEXT, content TEXT, FULLTEXT INDEX ft_idx (title, content) WITH PARSER ngram ) ENGINEInnoDB;5.3 第三方搜索引擎集成对于电商、内容平台等搜索为核心功能的业务Elasticsearch 是行业标准。其核心优势包括近实时搜索数据变更后秒级可查。强大的分词器内置多种语言分词对中文有IK等优秀分词器支持。丰富的查询DSL支持模糊查询、短语匹配、范围查询、聚合分析等。高亮与纠错直接返回高亮片段和搜索建议。典型的架构是业务数据写入 MySQL同时通过 Canal/Debezium 等工具监听 MySQL 的 Binlog将数据变更同步到 Elasticsearch。查询请求直接发给 Elasticsearch。6. 常见问题排查与实战技巧实录在实际开发和运维中关于LIKE的“怪事”不少这里记录几个典型案例和解决方法。6.1 查询结果不符合预期检查空格和不可见字符用户反馈搜索“手机”搜不到一个名为“智能手机 ”的商品。肉眼看起来没错但查询LIKE ‘%手机%’就是匹配不上。问题根源商品名末尾可能有多余的空格全角或半角、制表符或换行符。排查与解决使用HEX()函数查看字段的十六进制表示SELECT name, HEX(name) FROM products WHERE id xxx;。看看末尾是否有20半角空格、C2A0UTF-8中的不间断空格或0A换行。在查询前使用TRIM()函数清理数据或查询条件。但更建议在数据录入层就做好清洗。-- 临时解决方案查询时同时匹配清理后的值 SELECT * FROM products WHERE TRIM(name) LIKE %手机%; -- 根本解决更新数据并约束应用层和数据库层 UPDATE products SET name TRIM(name); ALTER TABLE products MODIFY name VARCHAR(100) NOT NULL DEFAULT ; -- 应用层在保存前调用 trim()6.2 明明有索引为什么 LIKE 查询还是慢除了前面说的前导%问题还有以下可能索引选择性太差如果某个字段的值只有几种如gender只有‘男’‘女’那么在这个字段上建索引并使用LIKE查询数据库优化器可能会认为全表扫描比走索引回表更划算从而放弃使用索引。可以通过SHOW INDEX FROM table_name查看索引的Cardinality基数这个值越接近表总行数索引选择性越好。数据类型不匹配如果字段是字符串类型但查询时传入的是数字或者反之会导致隐式类型转换使索引失效。-- 假设 product_code 是 VARCHAR但有索引 SELECT * FROM products WHERE product_code LIKE 12345; -- 错误数字12345被隐式转成字符串但可能导致索引失效 SELECT * FROM products WHERE product_code LIKE 12345; -- 正确使用了函数或表达式在索引字段上使用函数会使索引失效。SELECT * FROM products WHERE UPPER(name) LIKE %PHONE%; -- 索引失效 -- 如果必须这样做考虑在UPPER(name)上创建函数索引(MySQL 8.0)或者存储一个统一大写的冗余字段并为其建索引。6.3 如何对 LIKE 查询进行性能监控与调优使用 EXPLAIN 分析在查询前加上EXPLAIN关键字查看 MySQL 的执行计划。重点关注type列。type: index表示全索引扫描如果索引是覆盖索引可能还可以接受。type: ALL表示全表扫描对于大表这是红色警报。possible_keys和key列会显示可能用到的索引和实际用到的索引。如果key为NULL说明没用到索引。开启慢查询日志在 MySQL 配置文件my.cnf/my.ini中设置slow_query_log ON并设定一个合理的long_query_time如2秒。定期分析慢日志找出最耗时的LIKE查询。使用性能模式Performance SchemaMySQL 5.6 提供了更细粒度的性能监控工具。可以查询events_statements_summary_by_digest表来查看不同SQL模式的性能统计找出高频且低效的LIKE查询模式。我个人在实际操作中的一个习惯是对于任何新上线的带有搜索功能的接口如果查询条件包含LIKE我一定会用生产环境类似的数据量进行压力测试并用EXPLAIN查看执行计划。对于核心的搜索功能如果数据量增长预期明确我会在项目初期就和技术团队讨论是否引入 Elasticsearch避免后期重构的被动。数据库的LIKE就像一把瑞士军刀简单场景下非常方便但面对复杂任务时选用更专业的工具才是明智之举。