1. 项目概述为什么模糊查询是数据检索的“瑞士军刀”在数据库的日常操作里精确匹配查询就像用钥匙开锁你知道要找的“锁”长什么样直接匹配就行。但更多时候我们面对的是“模糊”的需求想找名字里带“张”的所有客户想筛选出产品描述中包含“升级版”的记录或者要从日志里找出所有以“ERROR_”开头的错误信息。这时候精确查询就束手无策了。MySQL中的LIKE关键字配合通配符正是为了解决这类“模糊”需求而生的利器它让数据检索从“非黑即白”的精确匹配进入了可以灵活匹配模式的灰度世界。我处理过不少数据清洗和报表需求模糊查询的使用频率高得惊人。无论是从海量用户昵称中筛选特定群体还是在杂乱的产品SKU中归类甚至是排查日志中的异常模式LIKE都是第一时间会想到的工具。它的语法看似简单但通配符%和_的组合使用以及背后对性能的影响里面有不少门道。理解透了能让你在写查询时既高效又准确避免因为一个通配符位置放错而返回成千上万条不想要的数据或者写出一个让数据库“跑断腿”的低效语句。这篇文章我就从一个多年数据库使用者的角度掰开揉碎了讲讲LIKE和它的两个核心通配符——百分号%和下划线_。我会结合最常见的业务场景用大白话解释它们的工作原理更重要的是分享一些实际开发中积累下来的使用技巧和避坑指南。无论你是刚开始接触SQL还是已经写过不少查询想加深理解相信都能从中找到对你有用的东西。2. LIKE关键字与通配符的核心原理拆解2.1 LIKE的本质模式匹配而非值匹配首先要明确一点WHERE column ‘value’这种是等值匹配数据库会去找完全等于‘value’的行。而WHERE column LIKE ‘pattern’是模式匹配。数据库会拿你给的‘pattern’模式去和字段值逐字符比较看这个字段值是否符合你描述的模式。这个‘pattern’就是普通字符加上通配符组成的字符串。通配符顾名思义就是可以代表通配其他字符的符号。在MySQL的标准LIKE语法中主要就是两个%和_。它们的功能非常专一%百分号代表任意长度的字符串包括零个字符。你可以把它想象成“任意内容”。_下划线代表严格的一个任意字符。你可以把它想象成“一个占位符”。理解了这个本质就能明白为什么LIKE通常比慢。可以利用索引进行快速定位如果是索引列的话而LIKE进行的是模式扫描尤其是当通配符出现在模式开头时索引常常会失效导致全表扫描。2.2 百分号%代表“零个到任意多个字符”百分号%是LIKE查询中最常用、也最“贪婪”的通配符。它的能力范围从“空”到“无限长”的字符串。基本用法示例LIKE ‘张%’匹配以“张”开头的任何字符串。例如“张三”、“张伟”、“张三丰”、“张”本身。LIKE ‘%升级%’匹配在任何位置包含“升级”二字的字符串。例如“系统升级通知”、“一键升级包”、“升级”。LIKE ‘%com’匹配以“com”结尾的任何字符串。例如“google.com”、“test.com”。一个关键特性匹配零个字符。这是容易被忽略的一点。LIKE ‘%’这个模式可以匹配任何字符串包括空字符串’’。因为%代表“零个或多个字符”所以即使字段是空的也符合“零个字符”的条件。LIKE ‘%%’、LIKE ‘%%%’效果完全相同。注意%无法匹配NULL值。NULL在SQL中代表“未知”或“不存在”它不是一个空字符串所以任何LIKE条件包括LIKE ‘%’对NULL值都会返回NULL在WHERE子句中被视为FALSE。如果你想同时查找包含某模式和非NULL的记录需要额外处理WHERE (column LIKE ‘%pattern%’ OR column IS NULL)。2.3 下划线_代表“严格的一个字符”下划线_比%更精确它只占一个坑必须有一个字符在那里不能多也不能少。基本用法示例LIKE ‘_张’匹配以任意单个字符开头后接“张”的两个字字符串。例如“李张”不对这是两个字但“李”是一个字符、“王张”。它不匹配“张”只有一个字也不匹配“欧阳张”三个字。LIKE ‘AB_’匹配以“AB”开头且总长度为3的字符串。例如“ABC”、“AB1”、“AB#”。LIKE ‘_ _’两个下划线匹配恰好为两个字符的字符串。例如“中国”、“A1”、“你好”。组合使用场景当你需要更精确地控制字符位置和数量时_就派上用场了。例如查找所有手机号假设格式已统一LIKE ‘1___________’1后面跟11个下划线共12位。或者查找产品编码格式为“CATE-001-XXX”最后三位是任意字母的记录LIKE ‘CATE-001-___’。2.4 转义通配符当字符本身包含%或_时既然%和_有特殊含义那如果我的数据里本身就包含百分号或下划线字符该怎么查找呢比如想找备注字段里包含“完成度50%”的记录。这时就需要转义。默认情况下MySQL使用反斜杠\作为转义字符。转义查询示例查找包含“50%”的字符串LIKE ‘%50\%%’。这里的\%告诉MySQL这个%不是通配符就是普通的百分号字符。查找包含“file_name”的字符串LIKE ‘%file\_name%’。自定义转义符如果你数据中反斜杠本身也很多或者出于习惯可以使用ESCAPE子句指定其他字符作为转义符。SELECT * FROM products WHERE description LIKE %50!%% ESCAPE !;这条语句中我们指定!为转义符那么!%就表示普通的百分号而末尾的%仍然是通配符。这个功能在处理特定格式的数据时非常有用。3. 核心应用场景与实战案例解析理解了原理我们把它放到真实的业务场景里看。模糊查询绝不是为了“模糊”而模糊它的每一个用法都对应着明确的需求。3.1 场景一用户与内容搜索前缀、中缀、后缀匹配这是最经典的场景几乎每个涉及用户输入的系统都会用到。1. 前缀匹配快速筛选与补全需求用户输入“刘”自动补全或列出所有姓“刘”的用户。查询SELECT username FROM users WHERE username LIKE ‘刘%’;实战心得前缀匹配‘刘%’是LIKE查询中对索引最友好的一种。如果username字段上有普通B-Tree索引MySQL有可能利用索引进行范围扫描Range Scan速度会比全表扫描快很多。在设计搜索框自动补全功能时优先考虑前缀匹配。2. 中缀匹配全局内容检索需求在文章内容或产品描述中搜索包含“区块链”的所有条目。查询SELECT title, content FROM articles WHERE content LIKE ‘%区块链%’;实战心得中缀匹配‘%区块链%’是性能杀手因为它无法利用索引的最左前缀原则。一旦表数据量变大比如超过几十万行这种查询就会非常慢。对于这种全文搜索的需求正确的做法是使用MySQL的FULLTEXT全文索引对于MyISAM和InnoDB表都支持或者引入专业的搜索引擎如Elasticsearch。LIKE ‘%...%’只适合在数据量极小或临时查询时使用。3. 后缀匹配特定格式查找需求找出所有邮箱地址是“company.com”结尾的员工。查询SELECT email FROM employees WHERE email LIKE ‘%company.com’;实战心得后缀匹配‘%company.com’和前缀匹配类似但通常无法有效利用标准索引。因为索引是按照字段值从头开始排序的。针对邮箱后缀、文件后缀这类固定后缀的查询如果性能要求高可以考虑1冗余一个存储“域名”的字段并对其建索引2使用反转函数如WHERE REVERSE(email) LIKE REVERSE(‘company.com’)并对反转后的字段建索引REVERSE(email)但这增加了复杂度。3.2 场景二数据清洗与规范化使用_进行格式校验在数据迁移或清洗过程中LIKE配合_可以快速找出不符合格式要求的数据。案例检查手机号格式假设我们要求手机号必须是11位数字且以1开头。-- 找出可能格式不正确的手机号非11位或非纯数字 SELECT user_id, phone FROM user_contacts WHERE phone NOT LIKE ‘1___________’ -- 先排除标准11位格式 OR phone LIKE ‘%[^0-9]%’; -- 再检查是否包含非数字字符这里用了简单正则思路实际需结合其他函数实操要点NOT LIKE可以用来反选。但注意NOT LIKE同样可能导致全表扫描。对于数据清洗这种一次性或低频任务可以接受但对于线上高频查询要避免对大数据表使用NOT LIKE ‘%...%’。案例查找特定编码模式的产品产品编码规则为大类2字母-子类3数字-序列号4数字例如“EL-001-0001”。-- 查找所有电子类EL开头的产品 SELECT product_code, product_name FROM products WHERE product_code LIKE ‘EL-___-____’; -- 查找所有子类为005的产品 SELECT product_code, product_name FROM products WHERE product_code LIKE ‘%-005-____’;这里第二个查询用了中缀匹配性能可能不佳。如果这类查询频繁更好的设计是将product_code拆分成category、sub_category、serial三个字段分别存储并建立索引。3.3 场景三系统日志分析与监控模式化错误排查系统日志、错误日志通常是文本信息用LIKE进行模式匹配是初级监控的常用手段。需求从应用日志表app_logs中找出今天所有级别为ERROR且信息中包含“Timeout”或“Connection refused”的日志。SELECT log_time, message FROM app_logs WHERE DATE(log_time) CURDATE() AND level ‘ERROR’ AND (message LIKE ‘%Timeout%’ OR message LIKE ‘%Connection refused%’);避坑指南日期范围优化上面的DATE(log_time) CURDATE()会导致索引失效如果log_time有索引。应改为范围查询WHERE log_time CURDATE() AND log_time CURDATE() INTERVAL 1 DAY。OR条件message LIKE ‘%A%’ OR message LIKE ‘%B%’数据库可能需要对message字段进行两次全扫描。对于日志分析这种海量数据场景应考虑将日志实时采集到ELKElasticsearch, Logstash, Kibana或类似的专业日志平台利用倒排索引进行高速全文检索。警惕“%”开头LIKE ‘%Timeout’和LIKE ‘%Timeout%’在日志量巨大时性能极差仅适合在开发环境或小数据量下临时排查问题。3.4 场景四权限管理与数据过滤通配符在业务逻辑中的应用有些系统会将权限规则、数据范围以模式字符串的形式存储运行时用LIKE进行匹配。案例简单的数据权限模型用户有一个“可访问部门路径”字段dept_path格式如“1.5.12.”表示属于公司ID为1下的分公司ID为5下的部门ID为12。要查询该用户权限下的所有员工。-- 假设当前用户的dept_path是 ‘1.5.12.’ SELECT * FROM employees WHERE ‘1.5.12.’ LIKE CONCAT(dept_path, ‘%’); -- 或者查询某个员工是否在该用户权限下 SELECT * FROM employees WHERE dept_path LIKE ‘1.5.12.%’;关键理解这里用到了LIKE的巧妙变形。第一种写法是用一个固定的权限路径去匹配员工表中动态的dept_path前缀。它查找所有dept_path是‘1.5.12.’开头的员工即其所在部门是用户部门的子部门或本部门。第二种写法更直观。但务必注意dept_path字段必须建立索引并且查询模式是‘固定路径%’前缀匹配才能保证性能。如果写成WHERE dept_path LIKE CONCAT(‘%’, user_path, ‘%’)索引将完全失效。4. 性能深度剖析与优化策略很多开发者觉得LIKE慢就一味避免使用。其实关键在于是否用对了地方和方式。下面我们来深入拆解它的性能瓶颈和优化手段。4.1 LIKE查询的索引利用规则这是性能优化的核心。MySQL的B-Tree索引是按照列值排序的像一本字典。LIKE ‘prefix%’前缀匹配这是最佳情况。因为索引是有序的查找以“prefix”开头的记录就像在字典里翻到以“pre”开头的页码范围然后顺序读取即可。这被称为索引范围扫描。LIKE ‘%suffix’后缀匹配索引失效。字典是按首字母排序的你无法快速找到所有以“ing”结尾的单词只能从头到尾翻一遍。这导致全表扫描。LIKE ‘%infix%’中缀匹配索引失效。同样你无法快速定位中间包含“ing”的单词。全表扫描。LIKE ‘_ixed’使用_的固定长度匹配索引失效。即使你知道长度和结尾但开头是任意字符索引无法定位起点。全表扫描。结论只有模式字符串不以通配符开头时标准的B-Tree索引才有可能被有效利用。4.2 针对模糊查询的专项优化方案当你的业务确实无法避免使用LIKE ‘%...%’这类低效查询时可以考虑以下方案1. 使用覆盖索引减少IO即使LIKE ‘%xxx%’无法加速数据定位但如果你的查询只返回索引列本身MySQL可能会选择扫描整个索引文件通常比数据文件小得多而不需要回表查询数据行。这叫做“覆盖索引”。-- 假设在 products 表上有一个索引 idx_name (product_name) -- 低效查询需要回表 SELECT * FROM products WHERE product_name LIKE ‘%Pro%’; -- 高效一些的查询可能走覆盖索引 SELECT product_id, product_name FROM products WHERE product_name LIKE ‘%Pro%’;如果idx_name索引包含了product_id和product_name第二个查询可能只需要扫描索引树速度会快很多。2. 使用全文索引FULLTEXT替代中缀匹配对于文本内容的搜索这是MySQL内置的正解。-- 1. 创建全文索引 ALTER TABLE articles ADD FULLTEXT INDEX ft_idx_content (content); -- 2. 使用 MATCH...AGAINST 进行全文搜索 SELECT * FROM articles WHERE MATCH(content) AGAINST(‘区块链’ IN NATURAL LANGUAGE MODE);全文索引的工作原理是建立单词的倒排索引适合关键词搜索支持自然语言模式和布尔模式功能远比LIKE强大和高效。但注意它有自己的停用词列表对中文分词需要依赖MySQL的ngram解析器从5.7.6开始支持或第三方插件。3. 引入外部搜索引擎对于海量文本、复杂搜索如拼音搜索、同义词、相关性排序Elasticsearch或Solr是更专业的选择。它们专为搜索而生性能和支持的特性远超数据库内置功能。通常架构是将数据库作为数据源通过CDCChange Data Capture工具将数据同步到搜索引擎。4. 冗余字段与反向索引这是一种“空间换时间”和“计算换时间”的思路。冗余字段如前所述将需要后缀匹配的字段如邮箱域名单独提取出来存储并建索引。反向索引存储一个反向的字段。例如对email字段新增一列email_reverse存储REVERSE(email)并对其建立索引。查询时WHERE email_reverse LIKE REVERSE(‘company.com’)就变成了前缀匹配可以利用索引。但这增加了数据维护的复杂性。4.3 编写高性能LIKE查询的实操守则根据我的经验遵循以下守则可以避免大部分性能问题永远警惕以通配符开头的模式这是第一铁律。在设计和评审SQL时看到LIKE ‘%...’或LIKE ‘%...%’必须问自己数据量多大有没有替代方案尽量将通配符放在模式末尾让查询条件满足前缀匹配是保证索引生效的最简单方法。避免在WHERE子句中对字段使用函数或计算WHERE LEFT(column, 3) ‘ABC’或WHERE column LIKE CONCAT(‘%’, variable)如果变量是用户输入模式可能以%开头都会导致索引失效。尽量将计算转移到等式的常量一侧。控制返回列尝试利用覆盖索引只SELECT必要的列特别是当这些列都在同一个索引中时。对于固定格式的精确匹配优先使用或IN例如查找状态为‘A’或‘B’的记录用status IN (‘A’, ‘B’)不要用status LIKE ‘A’ OR status LIKE ‘B’。前者更清晰且对索引的利用方式相同。5. 常见陷阱、疑难排查与进阶技巧即使知道了原理和优化方法实际使用中还是会踩坑。下面是我总结的一些典型问题和进阶用法。5.1 大小写敏感性问题MySQL中LIKE匹配的大小写敏感性取决于列的字符集和排序规则。如果列的排序规则Collation是utf8mb4_bin、latin1_bin等以_bin结尾的那么LIKE是大小写敏感的。‘Apple’和‘apple’不匹配。如果排序规则是utf8mb4_general_ci、utf8mb4_unicode_ci等以_ci结尾的那么LIKE是大小写不敏感的。‘Apple’和‘apple’匹配。排查案例明明数据里有‘MySQL’但LIKE ‘%mysql%’却查不到很可能是因为该列是二进制排序规则大小写敏感。解决方法使用函数强制转换大小写WHERE LOWER(column) LIKE ‘%mysql%’。但注意这会导致索引失效。如果业务需要忽略大小写在建表或修改列时应选择_ci不敏感的排序规则。5.2 NULL值的处理误区如前所述LIKE任何模式都无法匹配到NULL。SELECT * FROM table WHERE column LIKE ‘%’; -- 不会返回 column 为 NULL 的行 SELECT * FROM table WHERE column NOT LIKE ‘%’; -- 同样不会返回 NULL 行返回的是空字符串(‘’)的行如果需要包含NULL必须显式使用IS NULLSELECT * FROM table WHERE column LIKE ‘%pattern%’ OR column IS NULL;5.3 通配符与正则表达式REGEXP的抉择MySQL除了LIKE还支持更强大的正则表达式匹配REGEXP或RLIKE。LIKE ‘1___________’等价于REGEXP ‘^1[0-9]{10}$’。LIKE ‘%test%’等价于REGEXP ‘test’。如何选择简单模式用LIKE如果只是简单的%和_就能解决的问题用LIKE。它的语义更清晰在简单前缀匹配时可以利用索引。复杂模式用REGEXP当模式非常复杂比如需要匹配多种字符集合[a-zA-Z]、重复次数{n,m}、开头结尾锚定^、$、或逻辑|时REGEXP是唯一选择。性能注意REGEXP通常无法使用索引无论模式如何几乎都是全表扫描。它的功能强大是以牺牲性能为代价的切勿在数据量大的表上频繁使用。5.4 在动态SQL与预编译语句中的使用在应用程序中拼接SQL时处理LIKE的通配符需要小心。错误示例Python伪代码search_term request.get(‘keyword’) # 用户输入 “50%” sql f“SELECT * FROM products WHERE name LIKE ‘%{search_term}%’” # 生成的SQL: SELECT ... LIKE ‘%50%%’ - 这个‘%’会被当作通配符用户输入的%和_会干扰模式匹配。正确做法对输入进行转义在将用户输入拼接到LIKE模式前将其中的通配符转义。import re def escape_like_term(term): return re.sub(r‘([%_])’, r‘\\\1’, term) # 在%和_前加上\ safe_term escape_like_term(search_term) sql f“SELECT * FROM products WHERE name LIKE ‘%{safe_term}%’” # 生成的SQL: SELECT ... LIKE ‘%50\%%’ - 正确匹配“50%”使用参数化查询推荐将模式作为一个整体参数传入在模式内部进行转义。# 假设使用PyMySQL pattern f‘%{escape_like_term(search_term)}%’ cursor.execute(“SELECT * FROM products WHERE name LIKE %s”, (pattern,))这样既安全防止SQL注入又清晰。5.5 一个综合案例实现分页模糊查询这是一个常见的业务需求列表页支持按名称搜索并且要分页。-- 假设每页10条查第3页即偏移20条 SELECT * FROM products WHERE product_name LIKE ‘Pro%’ -- 假设是前缀搜索能用索引 ORDER BY create_time DESC LIMIT 10 OFFSET 20;性能瓶颈当OFFSET值非常大时比如翻到第1000页MySQL需要先扫描并丢弃前9990条记录再取10条即使有WHERE条件过滤效率也很低。优化方案使用“游标分页”或“seek method”-- 传统分页慢 SELECT * FROM t WHERE name LIKE ‘A%’ ORDER BY id LIMIT 10000, 20; -- 优化分页快 SELECT * FROM t WHERE name LIKE ‘A%’ AND id 上一页最后一条的id ORDER BY id LIMIT 20;思路是记录上一页最后一条记录的ID或时间戳下一页查询时用WHERE ... AND id :last_id来替代OFFSET。这需要业务逻辑配合并且排序字段必须唯一或能确定顺序。模糊查询LIKE是SQL工具箱里一把不可或缺的螺丝刀它简单、灵活几乎能应对所有非精确匹配的场景。但正如我们看到的它的简单背后藏着性能的陷阱。核心诀窍就是时刻关心通配符的位置前缀匹配是朋友中后缀匹配是敌人对于文本搜索该用全文索引就别吝啬在程序里拼接SQL时别忘了转义那些特殊的百分号和下划线。从我自己的经验来看早期很多慢查询日志里罪魁祸首都是不经意的LIKE ‘%...%’。后来养成了习惯在写任何LIKE之前都会先想想这个字段有索引吗这个模式能让索引生效吗数据量大了会不会撑不住如果答案不乐观就会去推动增加冗余字段、改用全文索引或者调整产品需求。数据库优化往往就是由这些最基础的细节堆砌起来的处理好每一个LIKE系统的响应速度可能就快上一分。 MySQL LIKE模糊查询：通配符%与_的原理、性能优化与实战场景
1. 项目概述为什么模糊查询是数据检索的“瑞士军刀”在数据库的日常操作里精确匹配查询就像用钥匙开锁你知道要找的“锁”长什么样直接匹配就行。但更多时候我们面对的是“模糊”的需求想找名字里带“张”的所有客户想筛选出产品描述中包含“升级版”的记录或者要从日志里找出所有以“ERROR_”开头的错误信息。这时候精确查询就束手无策了。MySQL中的LIKE关键字配合通配符正是为了解决这类“模糊”需求而生的利器它让数据检索从“非黑即白”的精确匹配进入了可以灵活匹配模式的灰度世界。我处理过不少数据清洗和报表需求模糊查询的使用频率高得惊人。无论是从海量用户昵称中筛选特定群体还是在杂乱的产品SKU中归类甚至是排查日志中的异常模式LIKE都是第一时间会想到的工具。它的语法看似简单但通配符%和_的组合使用以及背后对性能的影响里面有不少门道。理解透了能让你在写查询时既高效又准确避免因为一个通配符位置放错而返回成千上万条不想要的数据或者写出一个让数据库“跑断腿”的低效语句。这篇文章我就从一个多年数据库使用者的角度掰开揉碎了讲讲LIKE和它的两个核心通配符——百分号%和下划线_。我会结合最常见的业务场景用大白话解释它们的工作原理更重要的是分享一些实际开发中积累下来的使用技巧和避坑指南。无论你是刚开始接触SQL还是已经写过不少查询想加深理解相信都能从中找到对你有用的东西。2. LIKE关键字与通配符的核心原理拆解2.1 LIKE的本质模式匹配而非值匹配首先要明确一点WHERE column ‘value’这种是等值匹配数据库会去找完全等于‘value’的行。而WHERE column LIKE ‘pattern’是模式匹配。数据库会拿你给的‘pattern’模式去和字段值逐字符比较看这个字段值是否符合你描述的模式。这个‘pattern’就是普通字符加上通配符组成的字符串。通配符顾名思义就是可以代表通配其他字符的符号。在MySQL的标准LIKE语法中主要就是两个%和_。它们的功能非常专一%百分号代表任意长度的字符串包括零个字符。你可以把它想象成“任意内容”。_下划线代表严格的一个任意字符。你可以把它想象成“一个占位符”。理解了这个本质就能明白为什么LIKE通常比慢。可以利用索引进行快速定位如果是索引列的话而LIKE进行的是模式扫描尤其是当通配符出现在模式开头时索引常常会失效导致全表扫描。2.2 百分号%代表“零个到任意多个字符”百分号%是LIKE查询中最常用、也最“贪婪”的通配符。它的能力范围从“空”到“无限长”的字符串。基本用法示例LIKE ‘张%’匹配以“张”开头的任何字符串。例如“张三”、“张伟”、“张三丰”、“张”本身。LIKE ‘%升级%’匹配在任何位置包含“升级”二字的字符串。例如“系统升级通知”、“一键升级包”、“升级”。LIKE ‘%com’匹配以“com”结尾的任何字符串。例如“google.com”、“test.com”。一个关键特性匹配零个字符。这是容易被忽略的一点。LIKE ‘%’这个模式可以匹配任何字符串包括空字符串’’。因为%代表“零个或多个字符”所以即使字段是空的也符合“零个字符”的条件。LIKE ‘%%’、LIKE ‘%%%’效果完全相同。注意%无法匹配NULL值。NULL在SQL中代表“未知”或“不存在”它不是一个空字符串所以任何LIKE条件包括LIKE ‘%’对NULL值都会返回NULL在WHERE子句中被视为FALSE。如果你想同时查找包含某模式和非NULL的记录需要额外处理WHERE (column LIKE ‘%pattern%’ OR column IS NULL)。2.3 下划线_代表“严格的一个字符”下划线_比%更精确它只占一个坑必须有一个字符在那里不能多也不能少。基本用法示例LIKE ‘_张’匹配以任意单个字符开头后接“张”的两个字字符串。例如“李张”不对这是两个字但“李”是一个字符、“王张”。它不匹配“张”只有一个字也不匹配“欧阳张”三个字。LIKE ‘AB_’匹配以“AB”开头且总长度为3的字符串。例如“ABC”、“AB1”、“AB#”。LIKE ‘_ _’两个下划线匹配恰好为两个字符的字符串。例如“中国”、“A1”、“你好”。组合使用场景当你需要更精确地控制字符位置和数量时_就派上用场了。例如查找所有手机号假设格式已统一LIKE ‘1___________’1后面跟11个下划线共12位。或者查找产品编码格式为“CATE-001-XXX”最后三位是任意字母的记录LIKE ‘CATE-001-___’。2.4 转义通配符当字符本身包含%或_时既然%和_有特殊含义那如果我的数据里本身就包含百分号或下划线字符该怎么查找呢比如想找备注字段里包含“完成度50%”的记录。这时就需要转义。默认情况下MySQL使用反斜杠\作为转义字符。转义查询示例查找包含“50%”的字符串LIKE ‘%50\%%’。这里的\%告诉MySQL这个%不是通配符就是普通的百分号字符。查找包含“file_name”的字符串LIKE ‘%file\_name%’。自定义转义符如果你数据中反斜杠本身也很多或者出于习惯可以使用ESCAPE子句指定其他字符作为转义符。SELECT * FROM products WHERE description LIKE %50!%% ESCAPE !;这条语句中我们指定!为转义符那么!%就表示普通的百分号而末尾的%仍然是通配符。这个功能在处理特定格式的数据时非常有用。3. 核心应用场景与实战案例解析理解了原理我们把它放到真实的业务场景里看。模糊查询绝不是为了“模糊”而模糊它的每一个用法都对应着明确的需求。3.1 场景一用户与内容搜索前缀、中缀、后缀匹配这是最经典的场景几乎每个涉及用户输入的系统都会用到。1. 前缀匹配快速筛选与补全需求用户输入“刘”自动补全或列出所有姓“刘”的用户。查询SELECT username FROM users WHERE username LIKE ‘刘%’;实战心得前缀匹配‘刘%’是LIKE查询中对索引最友好的一种。如果username字段上有普通B-Tree索引MySQL有可能利用索引进行范围扫描Range Scan速度会比全表扫描快很多。在设计搜索框自动补全功能时优先考虑前缀匹配。2. 中缀匹配全局内容检索需求在文章内容或产品描述中搜索包含“区块链”的所有条目。查询SELECT title, content FROM articles WHERE content LIKE ‘%区块链%’;实战心得中缀匹配‘%区块链%’是性能杀手因为它无法利用索引的最左前缀原则。一旦表数据量变大比如超过几十万行这种查询就会非常慢。对于这种全文搜索的需求正确的做法是使用MySQL的FULLTEXT全文索引对于MyISAM和InnoDB表都支持或者引入专业的搜索引擎如Elasticsearch。LIKE ‘%...%’只适合在数据量极小或临时查询时使用。3. 后缀匹配特定格式查找需求找出所有邮箱地址是“company.com”结尾的员工。查询SELECT email FROM employees WHERE email LIKE ‘%company.com’;实战心得后缀匹配‘%company.com’和前缀匹配类似但通常无法有效利用标准索引。因为索引是按照字段值从头开始排序的。针对邮箱后缀、文件后缀这类固定后缀的查询如果性能要求高可以考虑1冗余一个存储“域名”的字段并对其建索引2使用反转函数如WHERE REVERSE(email) LIKE REVERSE(‘company.com’)并对反转后的字段建索引REVERSE(email)但这增加了复杂度。3.2 场景二数据清洗与规范化使用_进行格式校验在数据迁移或清洗过程中LIKE配合_可以快速找出不符合格式要求的数据。案例检查手机号格式假设我们要求手机号必须是11位数字且以1开头。-- 找出可能格式不正确的手机号非11位或非纯数字 SELECT user_id, phone FROM user_contacts WHERE phone NOT LIKE ‘1___________’ -- 先排除标准11位格式 OR phone LIKE ‘%[^0-9]%’; -- 再检查是否包含非数字字符这里用了简单正则思路实际需结合其他函数实操要点NOT LIKE可以用来反选。但注意NOT LIKE同样可能导致全表扫描。对于数据清洗这种一次性或低频任务可以接受但对于线上高频查询要避免对大数据表使用NOT LIKE ‘%...%’。案例查找特定编码模式的产品产品编码规则为大类2字母-子类3数字-序列号4数字例如“EL-001-0001”。-- 查找所有电子类EL开头的产品 SELECT product_code, product_name FROM products WHERE product_code LIKE ‘EL-___-____’; -- 查找所有子类为005的产品 SELECT product_code, product_name FROM products WHERE product_code LIKE ‘%-005-____’;这里第二个查询用了中缀匹配性能可能不佳。如果这类查询频繁更好的设计是将product_code拆分成category、sub_category、serial三个字段分别存储并建立索引。3.3 场景三系统日志分析与监控模式化错误排查系统日志、错误日志通常是文本信息用LIKE进行模式匹配是初级监控的常用手段。需求从应用日志表app_logs中找出今天所有级别为ERROR且信息中包含“Timeout”或“Connection refused”的日志。SELECT log_time, message FROM app_logs WHERE DATE(log_time) CURDATE() AND level ‘ERROR’ AND (message LIKE ‘%Timeout%’ OR message LIKE ‘%Connection refused%’);避坑指南日期范围优化上面的DATE(log_time) CURDATE()会导致索引失效如果log_time有索引。应改为范围查询WHERE log_time CURDATE() AND log_time CURDATE() INTERVAL 1 DAY。OR条件message LIKE ‘%A%’ OR message LIKE ‘%B%’数据库可能需要对message字段进行两次全扫描。对于日志分析这种海量数据场景应考虑将日志实时采集到ELKElasticsearch, Logstash, Kibana或类似的专业日志平台利用倒排索引进行高速全文检索。警惕“%”开头LIKE ‘%Timeout’和LIKE ‘%Timeout%’在日志量巨大时性能极差仅适合在开发环境或小数据量下临时排查问题。3.4 场景四权限管理与数据过滤通配符在业务逻辑中的应用有些系统会将权限规则、数据范围以模式字符串的形式存储运行时用LIKE进行匹配。案例简单的数据权限模型用户有一个“可访问部门路径”字段dept_path格式如“1.5.12.”表示属于公司ID为1下的分公司ID为5下的部门ID为12。要查询该用户权限下的所有员工。-- 假设当前用户的dept_path是 ‘1.5.12.’ SELECT * FROM employees WHERE ‘1.5.12.’ LIKE CONCAT(dept_path, ‘%’); -- 或者查询某个员工是否在该用户权限下 SELECT * FROM employees WHERE dept_path LIKE ‘1.5.12.%’;关键理解这里用到了LIKE的巧妙变形。第一种写法是用一个固定的权限路径去匹配员工表中动态的dept_path前缀。它查找所有dept_path是‘1.5.12.’开头的员工即其所在部门是用户部门的子部门或本部门。第二种写法更直观。但务必注意dept_path字段必须建立索引并且查询模式是‘固定路径%’前缀匹配才能保证性能。如果写成WHERE dept_path LIKE CONCAT(‘%’, user_path, ‘%’)索引将完全失效。4. 性能深度剖析与优化策略很多开发者觉得LIKE慢就一味避免使用。其实关键在于是否用对了地方和方式。下面我们来深入拆解它的性能瓶颈和优化手段。4.1 LIKE查询的索引利用规则这是性能优化的核心。MySQL的B-Tree索引是按照列值排序的像一本字典。LIKE ‘prefix%’前缀匹配这是最佳情况。因为索引是有序的查找以“prefix”开头的记录就像在字典里翻到以“pre”开头的页码范围然后顺序读取即可。这被称为索引范围扫描。LIKE ‘%suffix’后缀匹配索引失效。字典是按首字母排序的你无法快速找到所有以“ing”结尾的单词只能从头到尾翻一遍。这导致全表扫描。LIKE ‘%infix%’中缀匹配索引失效。同样你无法快速定位中间包含“ing”的单词。全表扫描。LIKE ‘_ixed’使用_的固定长度匹配索引失效。即使你知道长度和结尾但开头是任意字符索引无法定位起点。全表扫描。结论只有模式字符串不以通配符开头时标准的B-Tree索引才有可能被有效利用。4.2 针对模糊查询的专项优化方案当你的业务确实无法避免使用LIKE ‘%...%’这类低效查询时可以考虑以下方案1. 使用覆盖索引减少IO即使LIKE ‘%xxx%’无法加速数据定位但如果你的查询只返回索引列本身MySQL可能会选择扫描整个索引文件通常比数据文件小得多而不需要回表查询数据行。这叫做“覆盖索引”。-- 假设在 products 表上有一个索引 idx_name (product_name) -- 低效查询需要回表 SELECT * FROM products WHERE product_name LIKE ‘%Pro%’; -- 高效一些的查询可能走覆盖索引 SELECT product_id, product_name FROM products WHERE product_name LIKE ‘%Pro%’;如果idx_name索引包含了product_id和product_name第二个查询可能只需要扫描索引树速度会快很多。2. 使用全文索引FULLTEXT替代中缀匹配对于文本内容的搜索这是MySQL内置的正解。-- 1. 创建全文索引 ALTER TABLE articles ADD FULLTEXT INDEX ft_idx_content (content); -- 2. 使用 MATCH...AGAINST 进行全文搜索 SELECT * FROM articles WHERE MATCH(content) AGAINST(‘区块链’ IN NATURAL LANGUAGE MODE);全文索引的工作原理是建立单词的倒排索引适合关键词搜索支持自然语言模式和布尔模式功能远比LIKE强大和高效。但注意它有自己的停用词列表对中文分词需要依赖MySQL的ngram解析器从5.7.6开始支持或第三方插件。3. 引入外部搜索引擎对于海量文本、复杂搜索如拼音搜索、同义词、相关性排序Elasticsearch或Solr是更专业的选择。它们专为搜索而生性能和支持的特性远超数据库内置功能。通常架构是将数据库作为数据源通过CDCChange Data Capture工具将数据同步到搜索引擎。4. 冗余字段与反向索引这是一种“空间换时间”和“计算换时间”的思路。冗余字段如前所述将需要后缀匹配的字段如邮箱域名单独提取出来存储并建索引。反向索引存储一个反向的字段。例如对email字段新增一列email_reverse存储REVERSE(email)并对其建立索引。查询时WHERE email_reverse LIKE REVERSE(‘company.com’)就变成了前缀匹配可以利用索引。但这增加了数据维护的复杂性。4.3 编写高性能LIKE查询的实操守则根据我的经验遵循以下守则可以避免大部分性能问题永远警惕以通配符开头的模式这是第一铁律。在设计和评审SQL时看到LIKE ‘%...’或LIKE ‘%...%’必须问自己数据量多大有没有替代方案尽量将通配符放在模式末尾让查询条件满足前缀匹配是保证索引生效的最简单方法。避免在WHERE子句中对字段使用函数或计算WHERE LEFT(column, 3) ‘ABC’或WHERE column LIKE CONCAT(‘%’, variable)如果变量是用户输入模式可能以%开头都会导致索引失效。尽量将计算转移到等式的常量一侧。控制返回列尝试利用覆盖索引只SELECT必要的列特别是当这些列都在同一个索引中时。对于固定格式的精确匹配优先使用或IN例如查找状态为‘A’或‘B’的记录用status IN (‘A’, ‘B’)不要用status LIKE ‘A’ OR status LIKE ‘B’。前者更清晰且对索引的利用方式相同。5. 常见陷阱、疑难排查与进阶技巧即使知道了原理和优化方法实际使用中还是会踩坑。下面是我总结的一些典型问题和进阶用法。5.1 大小写敏感性问题MySQL中LIKE匹配的大小写敏感性取决于列的字符集和排序规则。如果列的排序规则Collation是utf8mb4_bin、latin1_bin等以_bin结尾的那么LIKE是大小写敏感的。‘Apple’和‘apple’不匹配。如果排序规则是utf8mb4_general_ci、utf8mb4_unicode_ci等以_ci结尾的那么LIKE是大小写不敏感的。‘Apple’和‘apple’匹配。排查案例明明数据里有‘MySQL’但LIKE ‘%mysql%’却查不到很可能是因为该列是二进制排序规则大小写敏感。解决方法使用函数强制转换大小写WHERE LOWER(column) LIKE ‘%mysql%’。但注意这会导致索引失效。如果业务需要忽略大小写在建表或修改列时应选择_ci不敏感的排序规则。5.2 NULL值的处理误区如前所述LIKE任何模式都无法匹配到NULL。SELECT * FROM table WHERE column LIKE ‘%’; -- 不会返回 column 为 NULL 的行 SELECT * FROM table WHERE column NOT LIKE ‘%’; -- 同样不会返回 NULL 行返回的是空字符串(‘’)的行如果需要包含NULL必须显式使用IS NULLSELECT * FROM table WHERE column LIKE ‘%pattern%’ OR column IS NULL;5.3 通配符与正则表达式REGEXP的抉择MySQL除了LIKE还支持更强大的正则表达式匹配REGEXP或RLIKE。LIKE ‘1___________’等价于REGEXP ‘^1[0-9]{10}$’。LIKE ‘%test%’等价于REGEXP ‘test’。如何选择简单模式用LIKE如果只是简单的%和_就能解决的问题用LIKE。它的语义更清晰在简单前缀匹配时可以利用索引。复杂模式用REGEXP当模式非常复杂比如需要匹配多种字符集合[a-zA-Z]、重复次数{n,m}、开头结尾锚定^、$、或逻辑|时REGEXP是唯一选择。性能注意REGEXP通常无法使用索引无论模式如何几乎都是全表扫描。它的功能强大是以牺牲性能为代价的切勿在数据量大的表上频繁使用。5.4 在动态SQL与预编译语句中的使用在应用程序中拼接SQL时处理LIKE的通配符需要小心。错误示例Python伪代码search_term request.get(‘keyword’) # 用户输入 “50%” sql f“SELECT * FROM products WHERE name LIKE ‘%{search_term}%’” # 生成的SQL: SELECT ... LIKE ‘%50%%’ - 这个‘%’会被当作通配符用户输入的%和_会干扰模式匹配。正确做法对输入进行转义在将用户输入拼接到LIKE模式前将其中的通配符转义。import re def escape_like_term(term): return re.sub(r‘([%_])’, r‘\\\1’, term) # 在%和_前加上\ safe_term escape_like_term(search_term) sql f“SELECT * FROM products WHERE name LIKE ‘%{safe_term}%’” # 生成的SQL: SELECT ... LIKE ‘%50\%%’ - 正确匹配“50%”使用参数化查询推荐将模式作为一个整体参数传入在模式内部进行转义。# 假设使用PyMySQL pattern f‘%{escape_like_term(search_term)}%’ cursor.execute(“SELECT * FROM products WHERE name LIKE %s”, (pattern,))这样既安全防止SQL注入又清晰。5.5 一个综合案例实现分页模糊查询这是一个常见的业务需求列表页支持按名称搜索并且要分页。-- 假设每页10条查第3页即偏移20条 SELECT * FROM products WHERE product_name LIKE ‘Pro%’ -- 假设是前缀搜索能用索引 ORDER BY create_time DESC LIMIT 10 OFFSET 20;性能瓶颈当OFFSET值非常大时比如翻到第1000页MySQL需要先扫描并丢弃前9990条记录再取10条即使有WHERE条件过滤效率也很低。优化方案使用“游标分页”或“seek method”-- 传统分页慢 SELECT * FROM t WHERE name LIKE ‘A%’ ORDER BY id LIMIT 10000, 20; -- 优化分页快 SELECT * FROM t WHERE name LIKE ‘A%’ AND id 上一页最后一条的id ORDER BY id LIMIT 20;思路是记录上一页最后一条记录的ID或时间戳下一页查询时用WHERE ... AND id :last_id来替代OFFSET。这需要业务逻辑配合并且排序字段必须唯一或能确定顺序。模糊查询LIKE是SQL工具箱里一把不可或缺的螺丝刀它简单、灵活几乎能应对所有非精确匹配的场景。但正如我们看到的它的简单背后藏着性能的陷阱。核心诀窍就是时刻关心通配符的位置前缀匹配是朋友中后缀匹配是敌人对于文本搜索该用全文索引就别吝啬在程序里拼接SQL时别忘了转义那些特殊的百分号和下划线。从我自己的经验来看早期很多慢查询日志里罪魁祸首都是不经意的LIKE ‘%...%’。后来养成了习惯在写任何LIKE之前都会先想想这个字段有索引吗这个模式能让索引生效吗数据量大了会不会撑不住如果答案不乐观就会去推动增加冗余字段、改用全文索引或者调整产品需求。数据库优化往往就是由这些最基础的细节堆砌起来的处理好每一个LIKE系统的响应速度可能就快上一分。