1. 这个需求背后的真实场景
1.1 什么时候需要给 LIKE 查询加权排序
先说我自己的一个项目经历。某次给一个内容管理系统做搜索功能，表里存了几万条内容，每个内容有标题、摘要、正文、标签四个字段。业务方最开始只提了一个需求：用户在搜索框输入关键词，能搜到相关结果就行。等第一版上线后，使用的人反馈说搜索结果太“死”——明明标题里包含关键词的内容，和只在正文里偶遇关键词的内容混在一起，排序全看主键 ID。这其实就是典型的“查到了但排不好”问题。
LIKE 查询本身只能回答“哪些行匹配”，它不负责告诉你“谁更匹配”。但业务方通常需要的是“谁最相关”。比如用户搜“闪存盘”这个词，标题直接叫“闪存盘对比评测”的内容，和一篇正文里顺带提了一句“可能对闪存盘的兼容性有影响”的文章，两者匹配程度显然不一样。如果排在前面的是后者，用户大概率会觉得搜索结果质量差，从而转身就走。
所以“根据 LIKE 查找关键字段设置权重排序”这个需求，本质上是给“匹配质量”一个量化分数，再按分数排序。这里的核心思路很简单：不同字段的重要程度不同，匹配到重要字段时，权重分更高。
1.2 基础权重排序写法长什么样
最朴素、最好理解的写法是直接在 ORDER BY 里放好几个布尔表达式：
SELECT title, summary, content FROM articles WHERE title LIKE '%闪存盘%' OR tags LIKE '%闪存盘%' OR summary LIKE '%闪存盘%' OR content LIKE '%闪存盘%' ORDER BY (title LIKE '%闪存盘%') DESC, (tags LIKE '%闪存盘%') DESC, (summary LIKE '%闪存盘%') DESC;在 MySQL 中，布尔表达式的结果是 1 或 0，所以(title LIKE '%闪存盘%')在匹配时等于 1，不匹配时等于 0。ORDER BY 按照这个值从大到小排序，相当于把“标题命中”的文章排在最前面，其次是“标签命中”，再往后是“摘要命中”。
这种写法很直白，但它有一个局限性：排序只看“是否命中”，不看“命中了几次”，也不看“是否同时命中多个字段”。也就是说，如果一篇文章标题和摘要都命中了“闪存盘”，另一篇只有标题命中，两者在排序上是一样的。实际业务里这种情况很常见，所以这种基础写法只能应付最原始的排序需求，通常还得多叠加一层权重计算。
2. 核心实现：单关键词多字段加权排序
2.1 用 CASE WHEN 构造权重分
等到我第二次优化搜索排序时，发现单纯列多个布尔表达式不够用。原因很简单：标题命中一条、摘要命中一条的内容，和标题、摘要、正文都命中几条的内容，相关度完全不同。这时候需要算总分。
最常用的做法是借用 CASE WHEN 表达式的语法，给每个字段分配一个固定权重：
SELECT id, title, summary, (CASE WHEN title LIKE '%闪存盘%' THEN 5 ELSE 0 END) + (CASE WHEN tags LIKE '%闪存盘%' THEN 3 ELSE 0 END) + (CASE WHEN summary LIKE '%闪存盘%' THEN 2 ELSE 0 END) + (CASE WHEN content LIKE '%闪存盘%' THEN 1 ELSE 0 END) AS relevance FROM articles WHERE title LIKE '%闪存盘%' OR tags LIKE '%闪存盘%' OR summary LIKE '%闪存盘%' OR content LIKE '%闪存盘%' HAVING relevance > 0 ORDER BY relevance DESC, id DESC;这里我经常用的权重分配是：
- 标题命中：5 分
- 标签命中：3 分
- 摘要命中：2 分
- 正文命中：1 分
为什么这么分配？因为标题往往浓缩了整个内容的核心主题，标签相当于作者手动给出的关键词。一个用户输入“闪存盘”，如果标题、标签都直接命中，那这篇内容大概率就是围绕闪存盘来写的。而正文里出现一次关键词，很可能只是顺带一提，所以权重低。
注意，SELECT 里对 relevance 字段的别名是给 HAVING 和 ORDER BY 用的。MySQL 有一个特性：SELECT 子句中定义的列别名，在 GROUP BY、ORDER BY、HAVING 子句里是可以直接引用的。这个特性在多数关系型数据库里不支持，但 MySQL 支持，这也是为什么 MySQL 做这种加权排序很方便的原因之一。
2.2 权重分还能叠加命中次数
如果业务方进一步说：“标题里出现两次‘闪存盘’的，应该比只出现一次的更强”，那上面的写法就不够了。这时我见过两个思路。
思路一，简单一点，统计字段内出现的次数：
SELECT id, title, (LENGTH(title) - LENGTH(REPLACE(title, '闪存盘', ''))) / LENGTH('闪存盘') AS title_count FROM articles;这段计算的核心原理是：REPLACE(title, '闪存盘', '')会把“闪存盘”三个字全都替换为空串。替换前后的字符长度差，就是所有“闪存盘”三个字的总字符数。再除以“闪存盘”本身的长度（3个字符），就得到了出现次数。这个计数方法不依赖 MySQL 内置的字符串搜索函数，兼容性很好。
然后将出现次数乘以权重：
SELECT id, title, (LENGTH(title) - LENGTH(REPLACE(title, '闪存盘', ''))) / 3 * 5 + (LENGTH(tags) - LENGTH(REPLACE(tags, '闪存盘', ''))) / 3 * 3 + (LENGTH(summary) - LENGTH(REPLACE(summary, '闪存盘', ''))) / 3 * 2 + (LENGTH(content) - LENGTH(REPLACE(content, '闪存盘', ''))) / 3 * 1 AS relevance FROM articles HAVING relevance > 0 ORDER BY relevance DESC, id DESC;思路二，更严谨的做法是按“出现频率”加权。正文往往很长，就算出现 5 次“闪存盘”，相对于几千字的正文来说也只是一个次要话题；标题很短，出现 2 次可能整个标题都在讲闪存盘。所以用出现次数除以字段长度，得到一个“密度”，再用密度乘以权重，这才是更准的相关度指标。
两种思路没有绝对的对错，重点是根据业务判断。我当时做的系统里内容正文普遍很长，所以后来改用了频率加权，效果确实比单纯计数好一些。
3. 深入优化：把权重方案做成可调参数
3.1 权重的选取，本质上是个业务决策
很多初学者会问：权重 5、3、2、1 是怎么定出来的？有没有标准答案？
我的答案是：没有标准，只有经验参考。权重的比例关系，反映的是“你对不同字段重要性的判断”。比如你做的是文章检索，标题肯定最重要；做的是商品搜索，商品名称最重，品牌其次；做的是订单系统，订单号、用户名的权重就需要重新分配。
但有一点是共通的：权重差不能太大，也不能太小。如果标题权重给 10，正文权重给 1，那几乎所有标题命中的结果都会排在正文命中结果前面，两者之间的相关性差异被人为拉得过大。这可能导致用户搜一个长尾关键词时，正文里整段都在讲这个主题、标题反而没有包含的内容，被排在很后面——这显然也不是好体验。我建议权重比率控制在 3:2:1 附近到 8:4:2 之间比较保险，具体要看数据量级和文本长度。
3.2 用 CROSS JOIN 方式维护权重映射，别写死在 SQL 里
当我接手的项目越来越大之后，我慢慢意识到一个设计问题：权重直接写在 SQL 里，改一次权重就得改一次业务代码，重新发布，特别麻烦。更合理的方式是把权重放到一个配置表里，用 JOIN 来取：
CREATE TABLE search_weight ( field_name VARCHAR(20) PRIMARY KEY, weight INT DEFAULT 1 ); INSERT INTO search_weight (field_name, weight) VALUES ('title', 5), ('tags', 3), ('summary', 2), ('content', 1);查询时配合条件聚合或者用 CASE WHEN 配合子查询来动态获取权重。MySQL 原生语法里没有特别优雅的“配置驱动”写法，但可以做一个小技巧：把配置表内容转换成变量，避免多次查询。
SET @weight_title = (SELECT weight FROM search_weight WHERE field_name = 'title'); SET @weight_tags = (SELECT weight FROM search_weight WHERE field_name = 'tags'); SELECT id, title, (CASE WHEN title LIKE '%闪存盘%' THEN @weight_title ELSE 0 END) + (CASE WHEN tags LIKE '%闪存盘%' THEN @weight_tags ELSE 0 END) AS relevance FROM articles HAVING relevance > 0 ORDER BY relevance DESC;虽然还是没有完全脱离 SQL，但至少权重值存放在数据库里。运营人员不用改代码，直接改配置表的内容，就能调整排序效果。这种“数据驱动”的思路在搜索排序里非常实用，尤其是没有专职搜索工程师、只有运维和开发一起维护的系统里。
多关键词的时候，这个思路同样成立。比如用户搜索“闪存盘 SSD”，拆分后两个关键词各自计算一遍权重，最终汇总。常见做法是每个关键词都算一遍总和，再加起来：
SELECT id, title, (CASE WHEN title LIKE '%闪存盘%' THEN 5 ELSE 0 END) + (CASE WHEN title LIKE '%SSD%' THEN 5 ELSE 0 END) + (CASE WHEN summary LIKE '%闪存盘%' THEN 2 ELSE 0 END) + (CASE WHEN summary LIKE '%SSD%' THEN 2 ELSE 0 END) AS relevance FROM articles HAVING relevance > 0 ORDER BY relevance DESC, id DESC;这里要留意一个问题：只给“闪存盘”命中权重、不给“SSD”命中权重，会造成搜索结果偏向某一边。如果用户输入的是两个词，通常意味着两个词同等重要或者接近同等重要。所以在多关键词场景下，权重设计要提前多做几组测试，不能想当然。
4. 性能瓶颈：LIKE 模糊查询的真正代价
4.1 为什么不建议在大表里无脑 OR LIKE
权重排序写得再漂亮，也得考虑性能。MySQL 里的LIKE '%关键词%'，如果通配符放在字符串两边，索引是不起作用的。原因很直观：索引本质上是按前缀顺序排列的，而%关键词%要求字符串中间或结尾匹配，B+ 树的索引结构和这种匹配方式对不上。所以数据库只能做全表扫描，把每一行数据都拉出来，用那行的字段值和关键词做逐字符匹配。
如果是几万行的小表，我实测过，查询时间一般还能控制在几十毫秒到几百毫秒之间。但到几十万行，加上多个字段 LIKE，查询时间就可能膨胀到几秒。这个延迟对后台管理页面也许还能接受，对前端搜索接口来说，基本等于不可用。
常规解法有几种：改成LEFT LIKE前缀匹配以命中索引；或者用全文索引；或者引入外部搜索引擎。考虑到这篇文章讲的是 MySQL 内的做法，我重点讲全文索引。
4.2 全文索引下的权重排序替代方案
MySQL 的 InnoDB 引擎从 5.6 版本开始支持中文全文索引吗？严格说，MySQL 默认的全文索引分词形式对中文不够友好，需要借助 ngram 解析器。如果用 ngram 分词器建全文索引，中文关键词是可以切分和匹配的。
一个典型用法如下：
ALTER TABLE articles ADD FULLTEXT INDEX ft_idx (title, tags, summary) WITH PARSER ngram;然后查询：
SELECT id, title, MATCH(title, tags, summary) AGAINST('闪存盘' IN BOOLEAN MODE) AS relevance FROM articles WHERE MATCH(title, tags, summary) AGAINST('闪存盘' IN BOOLEAN MODE) ORDER BY relevance DESC;MATCH AGAINST会自己计算相关性分值，这个分值综合考虑了词频、字段长度、文档频率等因素，比手写 CASE WHEN 更科学。全文索引还能用IN BOOLEAN MODE指定某些词必须出现、某些词禁止出现，灵活性也高一些。
但要注意，全文索引的相关性排序无法精确控制“标题字段比正文字段权重高”这件事。MySQL 默认的全文索引相关性计算，是把所有索引字段放在一起算的。如果想要字段级别权重的控制，可以把标题、标签、摘要建成三个独立的全文索引，然后在 SELECT 里分别计算 MATCH 分值，再乘上自己的权重：
SELECT id, title, MATCH(title) AGAINST('闪存盘' IN BOOLEAN MODE) * 5 + MATCH(tags) AGAINST('闪存盘' IN BOOLEAN MODE) * 3 + MATCH(summary) AGAINST('闪存盘' IN BOOLEAN MODE) * 2 AS relevance FROM articles HAVING relevance > 0 ORDER BY relevance DESC, id DESC;这种写法在数据量和匹配精度上，比纯 LIKE 好了不少。但代价是索引维护成本和查询复杂度都上去了，而且多 MATCH 子句的解析开销也不低。要不要这么做，得看数据规模和排性能要求。
5. 常见问题与排查技巧实录
5.1 权重排序不生效：HAVING 的坑
我踩过的一个很典型的坑：在 WHERE 里过滤关键词后，SELECT 里计算好了 relevance 别名，然后在 HAVING 里用relevance > 0过滤，结果发现查出来的行数和只写 WHERE 时不一样，有时候甚至全部查不出来。
排查后发现，MySQL 对 HAVING 里的别名引用遵循的顺序是先计算 SELECT 再执行 HAVING，这本身没问题。问题出在 WHERE 条件写得太严格，比如 WHERE 子句里用title LIKE '%闪存盘%' OR content LIKE '%闪存盘%'，然后 HAVING 里又要求relevance > 0，看起来没毛病，但实际上如果内容命中在 tags 字段里，而 WHERE 只考虑了 title 和 content，那这行在 WHERE 阶段就被过滤掉了，HAVING 里算出来永远是 0。
所以：WHERE 里的 OR 字段列表，必须和权重计算涉及的字段完全保持一致。否则就会出现“数据明明有匹配，但被半路枪毙”的情况。
5.2 字符串里中文与时区编码导致 LIKE 匹配错乱
中文系统的 LIKE 匹配偶尔会遇到诡异的结果，比如搜“SSD”能匹配到“SSD固态硬盘”，但搜“固态硬盘”匹配不到，或者某些字符明明在字符串里，LIKE 却查不到。
这种问题大多数出在字符集和排序规则上。数据库连接字符集、表字符集、字段字符集三者要一致，建议统一使用 utf8mb4。如果你用了 utf8mb3（旧称 utf8），部分生僻字、特殊符号就会出问题。此外 LIKE 匹配时，MySQL 会根据排序规则判断大小写和重音。比如在 utf8mb4_general_ci 排序规则下，大小写不敏感；在 utf8mb4_bin 下，大小写敏感。这个差异会影响搜索“ssd”是否能查到“SSD”。
建议检查方式：
SHOW VARIABLES LIKE 'character_set_server'; SHOW VARIABLES LIKE 'collation_server'; SHOW TABLE STATUS LIKE 'articles'\G如果发现表和字段的排序规则不统一，可以用ALTER TABLE ... CONVERT TO CHARACTER SET utf8mb4 COLLATE utf8mb4_bin这类语句统一转换。不过这个操作对大表会产生全表重建，比较重，一般放在低峰期执行。
5.3 ORDER BY 和 WHERE 都用到 LIKE，能否走索引？
很多人问：ORDER BY (title LIKE '%闪存盘%') DESC这一列能不能建索引加速？
结论是不能。因为LIKE '%x%'既可以匹配字符串开头，也可以匹配中间和结尾，索引无法预判结果。MySQL 只能先扫描满足条件的行，再对结果集排序。如果 WHERE 条件本身能命中索引（比如有一个精确匹配的字段），那部分扫描可以快一些；但 ORDER BY 里的 LIKE 表达式本身无法利用索引。
所以性能优化的方向，不要尝试给 LIKE 表达式建索引，而应该尝试以下两个思路。一是缩小扫描范围，比如用 OR 里加一个“能走索引”的精确条件，先缩小候选集，再在候选集里做 LIKE 和加权。二是改用全文索引，从根本上避免前缀匹配失效的问题。
5.4 分页参数变化导致排序不稳定
权重搜索场景下，如果多个结果权重相同，排序顺序可能会随着分页变化而跳变。原因是 MySQL 不会保证等值的记录顺序稳定。为了让分页稳定，排序里除了 relevance，建议再加上一个唯一字段，比如主键 ID：
ORDER BY relevance DESC, id DESC;加上之后，即使在多并发或数据更新场景下，同一页返回的内容也不会来回跳动。我遇到过一次线上反馈“搜索翻页时结果重复”，排查后就是只按 relevance 排序，大量结果权重相等，数据库每次拿到的主键顺序不确定，导致第一页和第二页有重叠。加了一个 ID 排序后，问题就消失了。
6. 一个可以快速复制的完整示例
6.1 建表和初始化数据
为了更直观，我给一个可以直接跑的例子。假设我们做的是一个商品信息表，字段包括商品名、品牌、标签、描述：
CREATE TABLE products ( id INT PRIMARY KEY AUTO_INCREMENT, product_name VARCHAR(100) NOT NULL, brand_name VARCHAR(50) DEFAULT '', tags VARCHAR(200) DEFAULT '', description TEXT ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4; INSERT INTO products (product_name, brand_name, tags, description) VALUES ('闪存盘128GB USB3.0', '闪迪', 'U盘/闪存盘/便携', '高速传输闪存盘，适合日常文件拷贝'), ('机械键盘青轴', '某品牌', '键盘/办公/外设', '青轴段落感明显，适合打字'), ('闪存盘钥匙扣款64GB', '某品牌', 'U盘/闪存盘', '钥匙扣设计，方便随身携带，使用闪存盘存储'), ('移动硬盘1TB', '某品牌', '存储/移动硬盘', '大容量存储，不适合低容量闪存盘场景');6.2 权重查询语句
假设业务方认为：商品名命中权重 5，品牌命中 4，标签命中 3，描述命中 1。那么查询可以写成：
SELECT id, product_name, brand_name, tags, (CASE WHEN product_name LIKE '%闪存盘%' THEN 5 ELSE 0 END) + (CASE WHEN brand_name LIKE '%闪存盘%' THEN 4 ELSE 0 END) + (CASE WHEN tags LIKE '%闪存盘%' THEN 3 ELSE 0 END) + (CASE WHEN description LIKE '%闪存盘%' THEN 1 ELSE 5 END) AS relevance FROM products WHERE product_name LIKE '%闪存盘%' OR brand_name LIKE '%闪存盘%' OR tags LIKE '%闪存盘%' OR description LIKE '%闪存盘%' HAVING relevance > 0 ORDER BY relevance DESC, id DESC;在这个例子里，德美闪存盘钥匙扣款既有商品名命中，也有标签命中，得分是 5 + 3 = 8；0053款在名称和描述命中，得分是 5 + 1 = 6；移动硬盘只有描述里出现了“闪存盘”，得分是 1。所以搜索“闪存盘”时，结果就能明显体现出“谁更相关”了。
6.3 扩展到多关键词
如果想支持“闪存盘 128GB”这种多关键词，可以将示例稍微改一下，每个关键词计算一套子得分，再求和：
SELECT id, product_name, (CASE WHEN product_name LIKE '%闪存盘%' AND product_name LIKE '%128GB%' THEN 10 WHEN product_name LIKE '%闪存盘%' THEN 5 WHEN product_name LIKE '%128GB%' THEN 5 ELSE 0 END) + (CASE WHEN tags LIKE '%闪存盘%' THEN 3 ELSE 0 END) AS relevance FROM products WHERE product_name LIKE '%闪存盘%' OR product_name LIKE '%128GB%' OR tags LIKE '%闪存盘%' HAVING relevance > 0 ORDER BY relevance DESC, id DESC;这里加了一个复合条件：商品名同时命中两个词，给 10 分，鼓励结果尽量匹配完整词组，而不是只命中其中一个词。设计权重时要记住一个原则：同时命中多个关键词，应该比命中单个关键词得分更高，否则用户搜长关键词时，单字匹配的结果就会排在最前面，语义不符。
7. 几个严重值得收藏的实战建议
7.1 关键词长度和权重的关系
如果用户搜索关键词很短，比如一个字“盘”，那在标题、标签、正文里出现的频率会极高。这种情况下，权重排序的意义会被稀释，因为几乎所有行都会命中。我实际测过，搜“盘”字时，标题命中的结果数可能占到全表的 80%。这时候，简单的字段权重排序基本没有区分度。针对短词，建议把命中次数的统计权重加大，或者引入“完全匹配优先”的规则：字段值和关键词完全相等时给更高的分。
(CASE WHEN title = '闪存盘' THEN 10 WHEN title LIKE '%闪存盘%' THEN 5 ELSE 0 END)这个技巧对长短关键词都有用。“完全等于”比“模糊包含”更能代表精确相关。
7.2 不要忘了 LIKE 的特殊字符转义
LIKE 语句中的百分号%和下划线_是通配符。如果用户搜索的内容里恰好包含这些字符，比如“Python_入门”，LIKE 会把_当成“任意一个字符”来匹配，结果可能匹配出来一堆“PythonX入门”之类的内容。更麻烦的是百分号，会让 LIKE 匹配全表。
处理办法是用 ESCAPE 转义：
WHERE title LIKE '%Python\_入门%' ESCAPE '\\';或者干脆在拼 SQL 前用addcslashes这类函数把特殊字符转义掉。很多搜索引擎系统都踩过这个坑，尤其是在做搜索日志分析时，发现用户输入了含通配符的字符串，导致查询超时。
7.3 排序字段的选择会影响慢查询日志
最后分享一个运维层面的经验。这种带权重排序的 SQL 往往是慢查询日志里的常客。原因不只是字符串的走复杂扫描，还因为每一行都要执行多次 LIKE 和计算表达式。建议把这类查询单独归类，设置独立超时时间和说明注释。如果是用程序框架拼接 SQL，要注意别用 OR 把大量关键词拼在一起；拆分为多次查询后合并结果，有时候反而更快，因为可以分别利用缓存和短查询优势。
我在实际项目里的一个经验是：先用相对高效的过滤条件（比如加一个简单的状态字段、分类字段）缩小候选集到几千行内，再做加权排序。这个优化比任何索引奇技淫巧都管用，也是我调整所有复杂 LIKE 搜索的第一动作。
搜索排序不是一个“写完就完事”的功能，而是一个需要持续看数据、调权重、验证效果的过程。愿所有被排序折磨过的人，都能通过这套思路少走几步弯路。
