前几天群里有人抛了个问题一张两百万行的业务表用title LIKE %某个词%查数据页面直接卡了十几秒。看到这个 SQL 的时候我第一反应就是通配符写法出了问题。今天就把 MySQL 里通配符这件事完整聊清楚——包括基本语法、组合技巧、性能原理、坑点排查顺带把大家常搜索的安装配置、连接报错、排序更新这些周边问题也带一遍。这篇文章不是教科书是我在实际项目中踩过坑之后整理出来的实操笔记适合刚接触数据库的开发者、写过 SQL 但被模糊查询坑过的人以及准备数据库面试的同学。1. 通配符到底解决什么问题模糊查询的核心场景与基础规则1.1 通配符的本质让“相等判断”变成“模式匹配”先放下那个卡了十几秒的查询回到最基础的问题上。MySQL 里普通的等值查询用的是比如WHERE name 张三这个很好理解就是严格相等。但业务需求往往不是这样的你可能是搜商品名里包含“手机”的所有记录可能是找所有姓“王”的客户也可能是筛选身份证号里生日是 1990 年的用户。这些情况都无法用等值判断解决MySQL 为此提供了LIKE操作符配合通配符来实现模式匹配。MySQL 中的通配符就两个%匹配任意长度包括零长度的字符串。_匹配任意单个字符。这两个符号用来配合LIKE完成模糊查询。%是最常用的表示“这里可以有任意内容甚至没有内容”。_则严格匹配一个字符位置不能匹配零个字符也不能跳过这个位置直接去匹配后面的内容。举个例子LIKE 张%匹配所有以“张”开头的字符串张、张三、张无忌、张三丰都符合。LIKE %张匹配所有以“张”结尾的字符串。而LIKE %张%只要字符串里出现过“张”就算匹配。_的语义则更精确LIKE 张_匹配“张”后面只有一个字的姓名张三、张四可以张无忌不可以因为“张”后面有两个字。很多初学者会把%理解成“通配任意字符”这个说法不严谨。%匹配的是一整段任意长度的字符串_才匹配单个字符。两者组合使用才能真正满足真实业务里的复杂筛选。顺便提一句这里说的通配符是 MySQL 数据库查询语言里的内容不是 Word 排版里那个查找替换用的*和?别搞混了。1.2 LIKE 与通配符的基本语法结构LIKE的语法非常简单一眼就能看懂SELECT column1, column2, ... FROM table_name WHERE column_name LIKE pattern;核心就是column_name LIKE pattern这一句pattern是由普通字符和通配符组成的匹配模式。普通字符严格匹配通配符负责“变长占位”。直接上几个最常见的业务写法-- 查询所有姓“张”的用户 SELECT * FROM user WHERE real_name LIKE 张%; -- 查询姓名中包含“建国”的用户 SELECT * FROM user WHERE real_name LIKE %建国%; -- 查询手机号第二位是“8”的用户前提是phone字段存的是字符串 SELECT * FROM user WHERE phone LIKE _8%; -- 查询文章标题以“MySQL”开头的文章 SELECT id, title FROM article WHERE title LIKE MySQL%;这里有个非常重要的前提LIKE匹配的对象必须是字符串类型。如果字段是整数类型比如INT、BIGINT直接写LIKE是不行的或者说行为会非常诡异。比如手机号如果存为BIGINT你写WHERE phone LIKE 138%MySQL 会先把phone字段隐式转换为字符串再匹配这个转换过程会让索引彻底失效还可能产生类型转换带来的精度问题。所以需要频繁做模糊匹配的字段设计表结构时就该用VARCHAR并且控制好长度。1.3 模糊查询最典型的业务场景通配符在真实业务里出现的频率远高于很多人的想象。后台管理系统的搜索框、电商平台的商品搜索、运营后台的订单筛选、日志系统的关键字检索背后都离不开模糊查询。以订单系统为例运营同学经常要查“所有备注里包含‘测试’的订单”来做数据清洗这个需求翻译成 SQL 就是WHERE remark LIKE %测试%。再比如客服系统要根据用户留言搜出所有包含“退货”关键字的记录也是LIKE %退货%。但这里就要开始警惕了。LIKE %关键词%这种写法在数据量小的时候毫无感觉表里几百行、几千行数据怎么查都快。可一旦表数据量上百万、上千万这种写法就会变成一个灾难性的全表扫描而且连索引都救不了它。为什么这是第三章要重点讲的性能问题先记住结论%在前面的时候数据库无法高效利用 B 树索引只能硬着头皮把整张表扫一遍。所以用通配符之前先问自己三个问题这个查询是低频的内部操作还是高频的用户请求目标表的数据量大概在什么量级查询条件里的通配符能不能做到“前缀固定、后缀模糊”这三个问题的答案会直接决定你的 SQL 写法。2. LIKE 查询的进阶组合技巧与字符集陷阱2.1 多通配符组合精确控制匹配位置很多人知道%和_单独怎么用但组合起来就不太顺手了。其实通配符可以连续出现在一个模式里也可以出现多次通过位置关系来精确控制匹配条件。举几个组合例子-- 查询姓名以“张”开头、以“三”结尾、中间还有至少一个字的用户 SELECT * FROM user WHERE real_name LIKE 张%三; -- 查询手机号前三位是 138后四位是 0000 的用户 SELECT * FROM user WHERE phone LIKE 138%0000; -- 查询身份证号第 7~14 位是 19900101 的用户出生日期筛选 SELECT * FROM users WHERE id_card LIKE ______19900101%;第三个写法里用了 6 个下划线分别对应身份证号前 6 位地区码然后精确匹配出生日期。这就是_比%更精细的地方——它能把“任意一个字符”这个语义固定住不会因为长度差异产生误匹配。有一点要特别提醒%在匹配时是允许零长度的所以LIKE 张%能匹配到“张”本身。_则不行它必须匹配一个实实在在的字符。如果业务语义是“名字至少比姓氏多一个字”就得用LIKE 王_而不能用LIKE 王%。2.2 通配符与转义当数据里真的含有 % 和 _ 怎么办这是新手最容易翻车的地方也是实际项目里必须面对的问题。假设你要在商品表里查所有“折扣规则”字段中包含5%这个字样的记录-- 直接写会出问题 SELECT * FROM coupon WHERE rule LIKE %5%%;这条 SQL 的意图是“匹配任意内容 5 任意内容 % 任意内容”不MySQL 解析器会把你写的模式按顺序解析为%任意前缀、5、%任意内容、%任意后缀结果就是匹配所有包含数字 5 的记录而不是包含5%字样的记录。%被当作通配符消费掉了。正确做法是使用转义符。MySQL 的LIKE默认用反斜杠\作为转义符你也可以用ESCAPE子句显式指定-- 使用默认反斜杠转义 SELECT * FROM coupon WHERE rule LIKE %5\%%; -- 或者自定义转义符可读性更好 SELECT * FROM coupon WHERE rule LIKE %5#%% ESCAPE #;同样的问题也会出现在_上。比如你想查所有以tb_开头的表名从元数据表里查直接写WHERE table_name LIKE tb_%会把tba、tbb全匹配进来因为_被解释成了“任意单个字符”。正确写法SELECT table_name FROM information_schema.tables WHERE table_schema your_db AND table_name LIKE tb\_% ESCAPE \\;我踩过的坑是第一次在information_schema里做表名筛选没转义结果查出来的表名乱七八糟排查半天才意识到是_的语义问题。这种事确实容易让人记忆深刻。2.3 字符集、排序规则与大小写敏感一个隐蔽的坑MySQL 中LIKE是否区分大小写不取决于你写 SQL 的方式而取决于字段的排序规则Collation。很多人用LIKE abc%查不到ABC开头的记录或者反过来查到了一大堆大小写不一的记录都是排序规则在背后起作用。InnoDB 表最常用的utf8mb4_general_ci和utf8mb4_unicode_ci都是大小写不敏感的ci就是 case-insensitive所以LIKE abc%能匹配ABC、Abc、aBc。但如果字段的排序规则是utf8mb4_bin或utf8mb4_0900_as_cscs即 case-sensitive那么LIKE abc%就只能匹配小写的abc。临时验证这个行为可以这样-- 输出 1说明不区分大小写 SELECT ABC LIKE abc COLLATE utf8mb4_general_ci; -- 输出 0说明区分大小写 SELECT ABC LIKE abc COLLATE utf8mb4_bin;这个知识点在面试里经常被拿出来考在实战中更是能坑你一次。比如你在一个存量系统里用了utf8mb4_bin用户的登录名匹配突然区分大小写了排查起来往往要绕很大一圈。3. 通配符背后的性能逻辑索引失效与最左前缀原理3.1 为什么 % 放在开头会让索引失效现在回到开头那个卡了十几秒的查询。数据两百万行title LIKE %某个词%索引完全帮不上忙这不是偶然的而是由 B 树索引的结构决定的。InnoDB 的索引结构是 B 树叶子节点按索引列的顺序排列。索引能够加速查询靠的是“有序性”——你可以从根节点出发通过对比索引列的值快速定位到某个范围。以字符串列上的普通索引为例所有索引值都是按字典序排列的。现在看三种LIKE写法LIKE 前缀%等价于查询“前缀”到“前缀 任意后缀”这个字典序范围比如张%就是查张到张的最大后缀之间的所有值。这个范围在 B 树里是连续的所以 MySQL 可以用索引范围扫描type 为 range来快速定位。LIKE %后缀要求匹配任意前缀 固定后缀。索引是按整个字符串整体排序的并不是按“后缀”排序的所以无法通过索引的有序性去定位“后缀相同”的记录只能全表扫描。LIKE %包含%前后都有%更是完全排除了使用索引的可能必然全表扫描type 为 ALL。用EXPLAIN一眼就能看出来EXPLAIN SELECT * FROM article WHERE title LIKE MySQL%; -- typerange走索引 EXPLAIN SELECT * FROM article WHERE title LIKE %MySQL%; -- typeALL全表扫 EXPLAIN SELECT * FROM article WHERE title LIKE %MySQL; -- typeALL全表扫所以“通配符导致索引失效”这句话不够准确准确的说法是%如果出现在模式的开头索引失效。%出现在结尾活中间索引依然有发挥的空间。3.2 最左前缀匹配联合索引下的通配符使用法则很多业务表会建联合索引比如用户表的idx_province_city_name (province, city, name)。这种索引的排序规则是先按province排再按同省内的city排最后按同城市内的name排。它遵循最左前缀匹配原则。在这个联合索引下WHERE province 浙江省 AND city 杭州市 AND name LIKE 张%可以完美走索引因为前面的province、city都是等值匹配定位到一个确定的区间name LIKE 张%在这个小范围内继续做前缀匹配依然高效。但WHERE city 杭州市 AND name LIKE %张%就麻烦了因为跳过了province破坏最左前缀索引从第一个字段就断了。这种情况下 MySQL 通常也只会选择全表扫。这给了我们一个重要的优化思路如果你知道业务查询经常要“先按某些等值条件过滤再做某个字段的模糊匹配”那你就应该把等值条件字段放在联合索引前面模糊匹配字段放在最后。这样LIKE 固定值%就能在联合索引的最后一列上发挥范围扫描的作用性能提升非常显著。3.3 实测对比与方案选型什么时候可以接受 %在开头我知道你一定会问那如果业务就是需要LIKE %关键词%卡顿已经是事实了怎么办先说一个务实的原则不要把“能不能走索引”当作唯一标准数据量级才是第一决定因素。我实测过一个单表 5000 行的配置表即使用LIKE %关键词%查询耗时也就是个位数毫秒几乎感知不到。不是所有%在前面的查询都该死小表低并发内部系统这样用完全没问题。真正要警惕的是三类场景一是表数据量大百万以上且高频查询二是查询作为主线上游服务每一毫秒延迟都会被放大三是LIKE %关键词%配合 JOIN 或子查询放大效应更明显。这些场景下不能指望 MySQL 用通配符硬扛必须改方案。可选方案有这么几条路改用全文索引适合文章、标题、正文这种文本内容的模糊搜索性能和匹配精度都远胜LIKE。改用前缀索引如果你只需要匹配“以某词开头”的内容可以把字段内容做截断前缀再用LIKE 前缀%。引入外部搜索引擎Elasticsearch 之类的方案适合非常复杂的搜索需求。这不是本文重点不展开。预先提取关键词存列把需要模糊匹配的内容提前拆解成独立列用等值查询代替模糊查询以空间换时间。还有一个很常见的业务妥协方案把搜索框的输入拆成多个词每个词都用LIKE %词%组合这个做法在数据量小时没问题数据量大了还是要另寻出路。4. 模糊查询的多种替代方案与选型对比4.1 REGEXP正则表达式能做什么 LIKE 做不到的事要说对LIKE最直接的补充就是正则表达式。MySQL 支持REGEXP等价写法RLIKE和 MySQL 8.0 的REGEXP_LIKE()函数它能表达更复杂的模式匹配。比如说你想要“姓名以张或李开头后面至少跟一个字”用LIKE写会比较笨拙需要拼接多个条件SELECT * FROM user WHERE (real_name LIKE 张% OR real_name LIKE 李%) AND CHAR_LENGTH(real_name) 2;用正则就一行SELECT * FROM user WHERE real_name REGEXP ^[张李].;再比如你要查邮箱以a或b开头且后面跟数字的SELECT * FROM user WHERE email REGEXP ^[ab][0-9];但必须清醒正则匹配同样不能利用普通 B 树索引它的性能甚至比LIKE %关键词%还要差因为正则引擎的匹配逻辑更复杂。我通常只在小数据量表、后台批量筛选、或者数据校验场景使用它绝不用在高频核心查询上。4.2 全文索引文本搜索的正确打开方式如果你的表里存的是文章、商品介绍、日志这类长文本你用LIKE %关键词%去搜不仅慢而且搜索结果的相关性排序根本没法做。这个场景应该用全文索引。InnoDB 从 MySQL 5.6 开始支持全文索引建法如下ALTER TABLE article ADD FULLTEXT INDEX ft_title_body (title, body) WITH PARSER ngram;ngram解析器是为了支持中文分词MySQL 8.0 默认内置。查询方式也完全不同不再用LIKE而是用MATCH ... AGAINSTSELECT id, title FROM article WHERE MATCH (title, body) AGAINST (数据库优化 IN NATURAL LANGUAGE MODE);也可以做布尔模式指定必须出现、禁止出现的词SELECT id, title FROM article WHERE MATCH (title, body) AGAINST (数据库 -入门 IN BOOLEAN MODE);表示必须包含-表示必须排除。全文索引的匹配速度、相关性排序能力都远超LIKE但要注意它对短文本帮助有限而且对分词器的依赖很高表里的数据质量差时匹配结果可能看起来不太准确。4.3 函数替代法INSTR、LOCATE 与 POSITION除了LIKEMySQL 还提供了几个判断“字符串包含”的函数常见的是INSTR、LOCATE、POSITION。它们的语义都是“在字符串里找子串找到了返回位置找不到返回 0”。-- 三种写法等价 SELECT * FROM user WHERE INSTR(real_name, 建国) 0; SELECT * FROM user WHERE LOCATE(建国, real_name) 0; SELECT * FROM user WHERE POSITION(建国 IN real_name) 0;这三者等价于LIKE %建国%性能上也没有本质区别同样全表扫描。为什么还要用因为有一个场景LIKE会非常难写当被匹配的字符串里本身含有%或_时用LIKE必须写转义逻辑容易出错而INSTR不涉及通配符语义直接当成普通字符串查找即可。举个例子查包含5%字样的优惠规则SELECT * FROM coupon WHERE INSTR(rule, 5%) 0;这样写明显比转义后的LIKE %5\%%简洁。所以我会在“模式匹配”的场景用LIKE在“字符串包含”的场景优先用INSTR语义更清晰也不会被通配符语义干扰。我整理了一张对比表方便大家选型时快速对照方案典型语法能否用索引匹配能力推荐场景LIKE 前缀固定LIKE abc%可走索引中前缀匹配、搜索框联想LIKE 前后模糊LIKE %abc%否中小数据量的内部筛选REGEXPREGEXP ^a.b否强复杂的模式匹配、数据清洗全文索引MATCH(...) AGAINST(...)是专用索引较强长文本搜索、内容站INSTR/LOCATEINSTR(col,abc)0否简单单纯判断包含避免转义5. 生产环境通配符问题排查实录5.1 一次慢查询定位EXPLAIN 是第一个也是最后一个工具先分享一个实际案例。某运营后台的订单列表页上线初期数据量只有几十万行WHERE remark LIKE %测试%毫秒级返回。过了半年订单量涨到八百多万行页面开始频繁超时。大家第一反应是数据库连接池不够加了机器、加了连接数问题依旧。最后用EXPLAIN看执行计划才发现罪魁祸首就是这条LIKE %测试%typeALL扫描行数接近一千万。这个案例说明一件事很多慢查询的根因不是数据库资源问题而是 SQL 写法让索引无从发挥。遇到慢查询第一件事永远是EXPLAIN不是重启数据库不是加内存不是调连接池。EXPLAIN SELECT * FROM orders WHERE remark LIKE %测试% AND create_time 2024-01-01;看执行计划里的type列如果是ALL说明全表扫描了是range或ref说明索引有发挥作用。然后看rows列估算扫描行数判断 SQL 的代价有多大。另有一个容易被忽视的点如果LIKE %关键词%出现在WHERE条件里而ORDER BY或LIMIT后边又有其他字段优化器可能会在排序上花更多成本。排查时要把整条 SQL 放进来看不能只看WHERE那一段。5.2 通配符与 UPDATE、DELETE 组合的灾难风险模糊查询本身慢只是问题的一个方面更危险的是把通配符用在UPDATE和DELETE语句里。你可能觉得这有什么可聊的但真实案例每天都在发生。有一个流传很广的事故开发人员执行UPDATE orders SET status cancel WHERE order_no LIKE %TEST%本意是清理测试订单结果%TEST%意外匹配到了正式订单导致大量线上数据被改错。原因可能是订单号里恰好有某个字符序列和模式对上了或者测试单号根本被人为改成了正式单号的形态。这个坑要怎么防我现在的做法很简单但也确实有用任何含LIKE的UPDATE/DELETE执行前先跑一遍等价的SELECT看一眼命中的记录数量和样例。涉及生产环境时把整个操作包在事务里执行先SELECT COUNT(*)确认数量再执行更新。条件里至少要带上一个能走索引的等值字段比如create_time范围来缩小波及面不要把匹配完全寄托在LIKE上。规章制度听起来烦琐但每一条都是教训换来的。5.3 高频面试题速查表通配符和模糊查询相关的问题在 MySQL 面试中出现频率非常高这里整理了一个速查表适合面试前快速过一遍面试题核心回答要点LIKE 和 有什么区别是等值匹配LIKE是模式匹配可以配合%和_% 和 _ 的区别%匹配任意长度字符串_匹配单个字符LIKE 查询一定导致索引失效吗不LIKE 前缀%可走索引LIKE %后缀或LIKE %中%才失效为什么 % 在前会导致索引失效B 树索引按完整键值排序无法按字符串后缀定位如何查询包含 % 或 _ 的数据使用ESCAPE转义如LIKE %\%% ESCAPE \\LIKE 区分大小写吗取决于排序规则_ci不区分_bin/_cs区分大数据量下 LIKE %xx% 怎么优化全文索引、分词存储、外部搜索引擎、按前缀拆解REGEXP 和 LIKE 选哪个功能上 REGEXP 更强性能上 LIKE 更好按场景选这里要特别提醒一个细节面试官往往不是要你背概念而是要你手写一条 SQL或者给一条 SQL 让你分析它为什么慢。平时真的跑一跑这些语句、看看执行计划面试时才能答到点子上。6. 环境与运维安装、连接、基础操作中的避坑清单6.1 安装方式的取舍包管理器与 Docker 的选择聊完查询本身回到一个所有开发者都绕不过去的步骤MySQL 环境怎么搭。很多人卡在这一步就花了一下午其实思路不复杂。如果是自己本机开发用最省心的是包管理器安装。Ubuntu/Debian 用aptCentOS 用yum或dnf# Ubuntu/Debian sudo apt update sudo apt install mysql-server # CentOS 7 sudo yum install mysql-server # CentOS 8 sudo dnf install mysql-server安装完成后启动服务sudo systemctl enable mysqld sudo systemctl start mysqld如果是需要统一环境、快速起一套测试库用 Docker 更省事不用处理本机系统依赖docker run -d \ --name mysql8 \ -p 3306:3306 \ -e MYSQL_ROOT_PASSWORDyourpassword \ mysql:8.0版本选择上有一个基本建议新项目直接用 MySQL 8.0没必要再回头看 5.7。8.0 的窗口函数、CTE、更好的字符集支持会让后面开发舒服很多。老项目如果还在 5.7 上跑倒不必强行升级稳定优先。6.2 error 2002 与连接类报错排查套路在所有 MySQL 连接报错里最常见的一个就是ERROR 2002 (HY000): Cant connect to local MySQL server through socket /tmp/mysql.sock (2)这个报错的含义是客户端通过 socket 文件连接本地 MySQL 失败。多数情况下不是密码错了而是mysqld服务没起来或者 socket 文件路径不一致。排查顺序很固定按下面的顺序来# 1. 确认进程是否在跑 ps -ef | grep mysqld # 2. 如果进程不在启动服务 sudo systemctl start mysqld # 3. 如果进程在但还是报错看 socket 路径 mysql -u root -p -e SHOW VARIABLES LIKE socket; # 4. 用 TCP 方式连接跳过 socket 文件问题 mysql -h 127.0.0.1 -P 3306 -u root -p有些时候用localhost连会走 socket用127.0.0.1会走 TCP两者表现可能不同。遇到玄学连接问题换着试一下多半能定位到问题方向。另外还有一类“连接被拒绝”的报错一般是服务没监听 3306 端口或者防火墙拦了用netstat -tlnp | grep 3306可以验证监听状态。6.3 基础操作要点默认值、排序、更新与锁表最后再快速过一遍热搜词里高频出现的基础操作。不少人在这些简单操作上踩过坑值得关注。设置字段默认值为 0建表时直接写DEFAULT 0CREATE TABLE t ( id INT PRIMARY KEY AUTO_INCREMENT, flag INT NOT NULL DEFAULT 0 );UPDATE语句的完整语法要记住顺序不能错UPDATE 表名 SET 列新值 WHERE 条件。容易出错的地方是忘记写WHERE或者在SET和WHERE之间漏了逗号UPDATE users SET status 1, updated_at NOW() WHERE id 123;排序这块搜索列表最常见的组合是“匹配度排序 时间倒序”。如果搜索结果来自LIKE %词%又希望最新发布的靠前SELECT id, title FROM article WHERE title LIKE %MySQL% ORDER BY publish_time DESC, id DESC LIMIT 20;至于锁表这里有个非常现实的操作常识LIKE模糊匹配到大量行时UPDATE操作可能锁住很大范围的记录。尤其是LIKE %xx%这种条件本身是全表扫描更新时行锁的范围会被撑得很大业务高峰期执行这种语句很容易制造锁等待甚至死锁。操作大表前先用SHOW PROCESSLIST看看当前数据库负载再决定要不要跑这句话值得记住。结尾MySQL 里的通配符表面上是%和_两个符号的用法背后牵出来的却是模糊查询的设计思路、索引失效的底层原理、生产环境的风险控制和方法选型。我个人在实际项目里的体会是写 SQL 之前多想一步“这条语句在数据量翻十倍之后还扛不扛得住”能帮你避开绝大多数性能事故。如果你刚开始接触这个主题建议照文中的示例建几张表亲手跑一跑EXPLAIN再试几次ESCAPE转义对通配符的印象会深刻得多。最后再分享一个小技巧把你在项目中用过的每一种模糊查询方案和它对应的数据量级记下来下次遇到类似需求你就不需要再重新踩一遍坑了。