作为一个经常跟 MySQL 打交道的人我太懂这种痛点了搜索结果排在前面的一定是最匹配的才对可默认情况下 LIKE 查询出来的结果顺序往往跟用户想要的“相关度”半毛钱关系都没有。标题里提到的“order by 根据 like 查找关键字段设置权重排序”其实就是搜索场景里最经典的一个需求——我得让标题字段命中的排最前、标签字段命中的次之、正文内容命中的垫底还得让从头匹配的比中段匹配的优先。“权重排序”四个字看着简单真在 SQL 里落地还是有挺多细节可以抠的。这篇文章我就结合自己实际项目里的踩坑和优化经验把 MySQL 下基于 LIKE 匹配结果做权重排序的完整方案拆开讲清楚从最基础的 ORDER BY 搭配 CASE WHEN 写法到多字段、多关键词、性能优化和常见坑一步到位拿来就能用。1. 先拆解需求权重排序到底在排什么1.1 一个最简单的场景看明白问题先摆一个最常见的例子商品搜索。假设有一张 products 表里面有 id、name、tags、description 四个字段用户在小框框里输入“无线鼠标”四个字预期结果是商品名字直接叫“无线鼠标”的排最前面名字带“无线鼠标”前缀的排第二tags 标签里含“无线鼠标”的排第三description 里好不容易提到一句“无线鼠标”的排最后。可如果直接写SELECT * FROM products WHERE name LIKE %无线鼠标% OR tags LIKE %无线鼠标% OR description LIKE %无线鼠标%MySQL 会把三个字段命中的所有记录都捞出来然后按主键或者存储顺序返回运气好点它能按 id 倒序排大多数情况下完全没有任何“相关度”可言。用户搜“无线鼠标”第一个出结果的是一个 description 里顺带提了一嘴的配件名字完全叫“无线鼠标”的正主反而不知道被挤到第几页去了——这种体验放线上就是赤裸裸的流失。1.2 权重排序的底层逻辑权重排序本质上是给每条命中的记录算一个“匹配分”然后用 ORDER BY 把分数降序排。分数怎么算两个维度字段权重和匹配位置权重。字段权重说的是不同字段在业务里的地位不同商品名比标签重要、标签比描述重要这是产品侧的定级。匹配位置权重说的是关键词在字段里出现的位置不同从开头匹配比从中间匹配更精准全字段精确相等又比单纯前缀匹配更强。这两个维度的得分叠加在一起才是一条记录的综合匹配分。用生活化的类比来说就像招人筛简历学校背景是硬权重字段权重专业对口程度是软权重匹配位置权重两个维度各自打分、加总排序。2. 核心武器ORDER BY 里的 CASE WHEN 魔法2.1 从 ORDER BY 1, 2, 3 到 ORDER BY 表达式很多人脑子里对 ORDER BY 的印象还停留在“ORDER BY 列名”或者“ORDER BY 数字序号”其实 ORDER BY 后面跟的可以是一个完整的表达式。既然是表达式那就能用函数、能算数、能做逻辑判断。CASE WHEN 正是做逻辑判断的利器它能在 ORDER BY 里对每一行算出一个分数来。举一个最基础的例子只看 name 字段让完全相等的排最前、前缀匹配的排第二、包含匹配的排最后SELECT * FROM products WHERE name LIKE %无线鼠标% ORDER BY CASE WHEN name 无线鼠标 THEN 0 WHEN name LIKE 无线鼠标% THEN 1 ELSE 2 END;这里 ORDER BY 排的是 0、1、2 三个档位升序排列就刚好是精确匹配最前、前缀匹配居中、包含匹配垫底。思路一下就通了CASE WHEN 负责分档ORDER BY 负责用档位排序。2.2 多字段叠加把匹配分加在一起才科学只看一个字段是不够的真实业务里 WHERE 条件往往横跨好几个字段。这时候不能在每个字段上单独分档排序因为单字段档位没法横向比较——name 的 0 档和 tags 的 0 档到底谁该排前面正确的做法是给每个字段的每次匹配赋予不同的分值然后把一条记录上所有的命中得分加起来用一个总分去排序。来个具体例子SELECT * FROM products WHERE name LIKE %无线鼠标% OR tags LIKE %无线鼠标% OR description LIKE %无线鼠标% ORDER BY ( (CASE WHEN name 无线鼠标 THEN 8 ELSE 0 END) (CASE WHEN name LIKE 无线鼠标% THEN 6 ELSE 0 END) (CASE WHEN name LIKE %无线鼠标% THEN 4 ELSE 0 END) (CASE WHEN tags LIKE %无线鼠标% THEN 2 ELSE 0 END) (CASE WHEN description LIKE %无线鼠标% THEN 1 ELSE 0 END) ) DESC;分数的设计逻辑很直白名字精确匹配给 8 分、前缀给 6 分、包含给 4 分tags 命中给 2 分description 命中给 1 分。若有一条记录名字精确匹配总分是 8另一条记录名字只是包含、但 tags 和 description 都命中了总分是 4217。两条记录的高下立刻见分晓主键、存储顺序啥的完全不影响结果了。2.3 为什么用 DESC 不用 ASC上面的示例用的是 DESC 降序因为分数越大代表匹配度越高。这个细节新手经常搞反一排序发现“咦怎么完全相等的跑最后去了”十有八九是把 DESC 写成了 ASC。真要说翻转其实也行把 CASE WHEN 里的分值改成负数再 ASC但那样读起来绕得很没必要。默认记住“权重分数 DESC”就完事。3. 自己动手写一个完整场景的权重排序实现3.1 正式环境的数据表结构为了不整那些花里胡哨的玩具示例我用一个稍微贴近真实业务的数据模型来讲文章搜索场景表名 article字段有 id、title、summary、content。用户搜一个关键词希望标题命中的排最前、摘要命中的次之、正文命中的最后并且在同一个字段内部越靠前出现的匹配越优先。CREATE TABLE article ( id INT PRIMARY KEY AUTO_INCREMENT, title VARCHAR(200) NOT NULL COMMENT 文章标题, summary VARCHAR(500) COMMENT 摘要, content TEXT COMMENT 正文, created_at DATETIME DEFAULT CURRENT_TIMESTAMP ) ENGINEInnoDB DEFAULT CHARSETutf8mb4;3.2 单关键词的完整 SQL 写法搜索词用一个变量来代替方便在代码里拼参数的时候看得清楚。这里假设用户搜索的是“索引优化”SET kw 索引优化; SELECT id, title, summary, ( (CASE WHEN title kw THEN 10 ELSE 0 END) (CASE WHEN title LIKE CONCAT(kw, %) THEN 8 ELSE 0 END) (CASE WHEN title LIKE CONCAT(%, kw, %) THEN 6 ELSE 0 END) (CASE WHEN summary LIKE CONCAT(%, kw, %) THEN 4 ELSE 0 END) (CASE WHEN content LIKE CONCAT(%, kw, %) THEN 2 ELSE 0 END) ) AS score FROM article WHERE title LIKE CONCAT(%, kw, %) OR summary LIKE CONCAT(%, kw, %) OR content LIKE CONCAT(%, kw, %) ORDER BY score DESC, created_at DESC;注意几个点用 CONCAT 拼 LIKE 的模糊匹配模板比直接在 SQL 里写死字符串干净也更安全因为参数化查询的时候不容易拼出语法错误。score 作为一个计算列出现在 SELECT 里同时在 ORDER BY 里直接引用。MySQL 的 SELECT 别名是可以被 ORDER BY 引用的这一点省了又写一遍表达式的功夫。排序的次级条件是 created_at DESC意思是分数一样的时候新的文章排前面。这是我实际项目里比较喜欢补的一个细节——分数相同的情况其实非常常见不加次级排序条件的话两次查询结果顺序可能都不一样分页会出问题。3.3 多关键词的权重累加处理单关键词能跑通之后多关键词的搜索需求马上就会来。用户可能输入“MySQL 索引优化 慢查询”期望的是 SQL 能把这三个词都拆出来按命中数量加权排序也就是包含关键词数量越多的文章排越前。这种需求有两种实现路线。第一路线是在 SQL 里写死多个 LIKE 条件每个关键词都做一遍字段权重判断。第二路线是引入一个关键词匹配次数字段COUNT 一下命中了几个词。我比较推荐的做法是分开处理每个词单独加分SET kw1 MySQL; SET kw2 索引优化; SET kw3 慢查询; SELECT id, title, ( (CASE WHEN title LIKE CONCAT(%, kw1, %) THEN 5 ELSE 0 END) (CASE WHEN title LIKE CONCAT(%, kw2, %) THEN 5 ELSE 0 END) (CASE WHEN title LIKE CONCAT(%, kw3, %) THEN 5 ELSE 0 END) (CASE WHEN summary LIKE CONCAT(%, kw1, %) THEN 2 ELSE 0 END) (CASE WHEN summary LIKE CONCAT(%, kw2, %) THEN 2 ELSE 0 END) (CASE WHEN summary LIKE CONCAT(%, kw3, %) THEN 2 ELSE 0 END) (CASE WHEN content LIKE CONCAT(%, kw1, %) THEN 1 ELSE 0 END) (CASE WHEN content LIKE CONCAT(%, kw2, %) THEN 1 ELSE 0 END) (CASE WHEN content LIKE CONCAT(%, kw3, %) THEN 1 ELSE 0 END) ) AS score FROM article WHERE title LIKE CONCAT(%, kw1, %) OR title LIKE CONCAT(%, kw2, %) OR title LIKE CONCAT(%, kw3, %) OR summary LIKE CONCAT(%, kw1, %) OR summary LIKE CONCAT(%, kw2, %) OR summary LIKE CONCAT(%, kw3, %) OR content LIKE CONCAT(%, kw1, %) OR content LIKE CONCAT(%, kw2, %) OR content LIKE CONCAT(%, kw3, %) ORDER BY score DESC, id DESC;这个方案的思路是每个关键词命中都单独计分title 命中一次加 5 分summary 命中一次加 2 分content 命中一次加 1 分。三个词全在标题里出现的记录能拿到 15 分只有两个词在正文的只能拿 2 分高下立判。实际项目中如果关键词数量多了这种全展开的 SQL 会变得很长但我建议先保证正确性再谈优雅展开写其实是最容易排查问题的。3.4 加权还要算上位置LOCATE 函数的妙用前面说到“匹配位置越靠前越优先”但 LIKE 的 CASE WHEN 分档其实只能粗略地分前缀和非前缀。想要更精细的位置权重就得请 LOCATE 函数登场。LOCATE(substr, str) 返回 substr 在 str 中出现的位置没找到返回 0。所以我可以把位置因素转化成连续分数位置越靠前得分越高。举例来说如果关键词在 title 中从第 1 个字符开始出现给 100 分从第 10 个字符开始出现给 90 分位置越靠后分数递减。实现方式SELECT id, title, ( CASE WHEN LOCATE(kw, title) 0 THEN GREATEST(100 - LOCATE(kw, title) 1, 1) ELSE 0 END ) AS position_score FROM article WHERE title LIKE CONCAT(%, kw, %) ORDER BY position_score DESC;GREATEST(100 - LOCATE(kw, title) 1, 1) 的意思是关键词出现在第一个位置得 100 分出现在第二个位置得 99 分以此类推最低兜底 1 分不会出现负数。这样“索引优化”出现在标题开头的文章就比同样命中标题但关键词出现在后半段的文章分数高排序自然靠前。不过说实话LOCATE 函数在 WHERE 条件里用了以后索引就完全指望不上了所以我的实践是LOCATE 一般只在 ORDER BY 的权重计算里用WHERE 过滤还是交给 LIKE 去处理这样至少保证 WHERE 里的 LIKE 还能有机会走索引前缀匹配场景下。4. 性能调优和隐藏大坑4.1 LIKE 的索引失效问题很多同学一听到 LIKE %关键词% 就条件反射地说“索引失效”。这话一半对一半不对。前模糊 LIKE 关键词% 是可以走索引的后模糊 LIKE %关键词 和双向模糊 LIKE %关键词% 都是无法走索引的只能全表扫描。权重排序这个场景里WHERE 条件几乎必然是双向模糊因为用户输入的词可能出现在字段的任意位置。全表扫描在数据量小的时候无所谓但一旦表里数据过了百万级每次搜索都扫一遍全表的话慢查询日志基本就要炸了。我踩过这个坑一个文章表 200 万数据用户搜了个热门词直接 3 秒多才返回接口超时。解决方案分几个层级小数据量、并发不高的场景直接忍受全表扫描加好 LIMIT 限制返回行数别把所有结果都捞出来返回给前端。数据量中等可以考虑用前缀索引也就是字符串字段拿前 N 个字符建索引但 LIKE %关键词% 依然很难吃到这个索引的红利。数据量大的场景要么走 MySQL 全文索引5.7 以后支持中文分词但效果一般要么引入 Elasticsearch 之类的搜索引擎把搜索压力从数据库剥离出去。还有个偏方是冗余字段比如单独存一个 keyword_text 字段把多个需要搜索的字段拼接进去然后建 FULLTEXT 索引用 MATCH AGAINST 去查。4.2 COLLATE 和大小写带来的排序不一致MySQL 的字符串比较、排序和 COLLATE排序规则关系很大。utf8mb4_general_ci 这个排序规则里ci 代表 case insensitive也就是不区分大小写MySQL 5.7 默认就是它。而 utf8mb4_bin 是二进制比较区分大小写。在 Linux 上 MySQL 默认大小写敏感的表名、列名已经够让人头大了字符串排序规则这里又可能坑一把两个不同的 COLLATE 下排序结果可能完全不一样。权重排序里 CASE WHEN 的等值判断也受 COLLATE 影响。如果你希望“MySQL”和“mysql”算同一个词那用默认的 utf8mb4_general_ci 就对你啥也不用做。但如果你想要精确区分大小写得在字段上显式指定 COLLATE utf8mb4_bin或者在 CASE WHEN 比较的时候临时指定二进制比较(CASE WHEN BINARY title BINARY kw THEN 10 ELSE 0 END)这个细节我在一次处理英文产品名搜索时遇到过产品名有大小写品牌规范但搜索的人不一定按规范输入业务上又要求必须匹配到大小写完全一致的才给最高分靠 BINARY 关键字强行区分就对了。4.3 处理 NULL 值防止权重计算变成 NULLMySQL 里 NULL 参与任何算术运算结果都是 NULL。如果 title、summary 这些字段允许为 NULL那么 (CASE WHEN ... THEN 分 ELSE 0 END) 本身没问题因为 ELSE 0 兜底了。可一旦你在多个分数之间做加法比如 (CASE WHEN title LIKE ... THEN 4 ELSE 0 END) (CASE WHEN summary LIKE ... THEN 2 ELSE 0 END)只要其中任何一个 CASE 表达式落到 ELSE 0结果就是数字没有 NULL 问题。真正的问题出在 WHERE 条件里如果 title 为 NULLtitle LIKE %关键词% 的结果不是 FALSE 而是 NULLNULL 在 WHERE 里会被当成不成立过滤掉这个行为其实是符合预期的。但如果你在 SELECT 里直接算 LOCATE(kw, NULL)返回值是 NULL然后 GREATEST(NULL, 1) 的结果也是 NULL再参与加法整个 score 就变成 NULL 了。这时候 ORDER BY score DESC 里NULL 会被排在非 NULL 值的后面排序直接乱掉。我的习惯是权重计算里每一段 CASE WHEN 都显式写上 ELSE 0同时如果用了 LOCATE包一层 IFNULL(LOCATE(kw, title), 0)。4.4 分页查询里的隐忧ORDER BY 的稳定性权重排序里score 相同的记录可能非常多。比如 100 条记录都只在 content 里命中一次关键词分数全是 2 分这时候 ORDER BY score DESC 根本没法在这 100 条里排出稳定次序。如果接着用 LIMIT 10 OFFSET 0 取第一页再 LIMIT 10 OFFSET 10 取第二页这两次查询的中间 10 条可能完全对不上——因为 MySQL 在排序键相同的情况下返回顺序是不确定的。解决这个问题的方法就是在 ORDER BY 里附加一个唯一且稳定的次级排序键最常用的就是主键 idORDER BY score DESC, id DESC这样即使分数相同id 也能兜底排序保证分页结果前后一致。这个坑很多人排查半天都发现不了因为单页查询看着一切正常翻到第二页才发现数据重复或遗漏。我建议所有 ORDER BY 含计算表达式的查询一律加一个主键作为末级排序条件。4.5 WHERE 和 ORDER BY 的匹配一致性还有一个容易被忽视的细节WHERE 条件和 ORDER BY 里的 CASE WHEN 判断条件必须保持一致。举个例子WHERE 里只过滤了 title LIKE %关键词%但 ORDER BY 里却计算了 title、summary、content 三个字段的分数。那结果就是summary 和 content 里命中的记录根本没进入结果集但它们的权重分数逻辑却写在 ORDER BY 里——这不算错误但分数整体会比预期低因为能拿到分的记录都只是 title 命中的。反过来更严重的问题是WHERE 过滤了三个字段但 ORDER BY 只算了 title 的分数那搜索结果里可能出现 summary 命中、title 完全没匹配的记录它的 score 是 0却排序排在一些 title 匹配度不高的记录后面。这种逻辑混乱一旦上线产品经理拿真实数据一对比就会发现排序完全不符合预期。我的习惯是WHERE 条件的每个字段在 ORDER BY 里都要有对应的权重分两边严丝合缝别省。5. 常见问题排查与方案速查5.1 问题实录为什么我的 ORDER BY 没有按权重生效有位同事遇到过这种情况SQL 写得完全正确CASE WHEN 分档也没问题但执行出来结果顺序纹丝不动看起来跟没排序一样。排查下来发现是查询里 SELECT 选择了 score 别名但 ORDER BY 写的是中文逗号“”MySQL 解析不了中文标点就直接忽略子句其实不是。真实原因是他在 ORDER BY 里写成了ORDER BY score DESC, created_at DESC,末尾多了一个逗号MySQL 抛错但应用层捕获了错误之后做了一个降级处理把 SQL 退化成无排序版本。这个例子虽然有点极端但背后的排查思路很典型先单独在命令行跑一遍 SQL 确认排序有没有生效再去看应用代码层是不是吞掉了异常。5.2 问题实录LIKE 匹配到了但权重分数是 0有个同事反馈说“关键词明明在 title 里但权重分数是 0”。后来查看数据发现 title 字段的值前后带着空格LIKE %关键词% 依然能匹配到但 CASE WHEN name 关键词 这种精确比较就匹配不上了因为实际值是 关键词 带空格。解决方案是入库前做去空格处理或者查询条件上用 TRIM 函数。这个坑在用户手工录入的数据里太常见了务必注意。5.3 问题实录性能从 100ms 退化到 2s权重排序方案上线初期跑得飞快数据量涨到一定程度突然慢得离谱。EXPLAIN 一看type 列从 ref 变成了 ALLExtra 列出现了 Using filesort。原因很简单表数据量大了以后LIKE %关键词% 的过滤能力下降命中的记录数量暴涨filesort 要排序的行数也跟着暴涨。优化手段在不改变业务逻辑的前提下给 WHERE 条件增加一些辅助过滤条件比如只查最近一年的文章缩小扫描范围。给可能出现排序的字段建联合索引即使 LIKE 走不了索引filesort 的压力也能减轻。最彻底的办法还是上专门搜索引擎MySQL 做这个活儿干到两百万条数据基本就到顶了。5.4 速查表权重分值设计参考不同业务场景对排序的敏感度不同我整理了一个参考分值表可以根据实际业务调整匹配类型推荐分值说明主字段精确匹配100主字段就是 title 这种最核心的字段主字段前缀匹配80以关键词开头主字段包含匹配60关键词出现在中间次字段包含匹配40tags、category 之类的次要字段辅字段包含匹配20summary、excerpt 这样的描述字段正文包含匹配10content 这种大文本字段这个分值表不是定死的业务权重不同就得调整。比如在电商搜商品品牌字段的权重可能比描述字段高出好几倍在文档站搜文章标题和摘要的差距往往没那么大。我建议测试的时候拿真实搜索词跑一遍结果看看前三页是不是符合产品预期再回来调分别拍脑袋。5.5 经验心得权重排序之外我还会做什么做了几年搜索相关的功能我的体会是权重排序只是搜索优化里的第一层。后续还能做的有很多比如搜索词的纠错和同义词扩展用户在搜索“笔记本”的时候把“手提电脑”也纳入匹配。这个在 SQL 层面做还行但词库维护成本不低。搜索热度加权。同一篇文章如果最近一周的浏览量特别高可以在权重分后面加一个人气分让热门内容在相同匹配度下排在前面。这个实现思路很简单多加一个 CASE WHEN 判断浏览量区间就行了。搜索结果去重。有些文章内容相近会同时在多个字段命中需要按业务规则做去重避免用户在结果页看到好几篇一模一样的文章。如果项目的数据量还在可控范围内MySQL 做这套权重排序完全够用。数据量一旦上去或者搜索词复杂度上了档次再考虑引入专业搜索引擎也不迟。但核心的“权重分”概念在搜索引擎里面依然适用换个引擎你的思路照样能用。6. 结尾小分享有时候我会在产品群里看到有人问“为什么 MySQL 搜索结果不准”其实答案往往非常简单——你没告诉它什么叫“准”。权重排序本质上就是把你脑子里的业务规则翻译成 SQL 能读懂的分值逻辑。用得久了你会发现这不只是技巧问题更是产品思维问题你得清楚业务上到底哪个字段更重要、哪种匹配更值得被优先展示。根据我自己的实操经验还有两条补充建议送给大家上线前一定用真实数据测试排序效果最好拿几十个高频搜索词过一遍肉眼核对前三页的结果是否合理这个步骤看着笨却比任何理论推导都管用。把权重分值设计成常量统一放在代码配置里管理别散落在各个 SQL 里后期要调权重的时候你会感谢当初的自己。搜索排序这条路可以走很深但 MySQL 里的这一步做扎实了后面换什么方案都不慌。