SQL中用CASE WHEN给关键词匹配打分，应为每个条件返回数值并显式写ELSE 0，用+连接单行多条件得分，避免NULL导致计算中断；需统一大小写、转义通配符，并在WHERE中前置模糊筛选以提升性能。
SQL里用CASE WHEN给关键词匹配打分，怎么写才不翻车
直接说结论：用 CASE WHEN 给不同匹配条件赋权重值，再用 SUM() 或 COALESCE() 聚合，最后 ORDER BY 排序。但别直接套模板——字段是否允许 NULL、重复匹配是否叠加、大小写敏感性，都会让结果跑偏。
常见错误是把多个 CASE WHEN 写成并列的独立字段（比如 SELECT c1, c2, score1, score2），然后想靠 ORDER BY score1 + score2 排序，结果发现 NULL 值拖垮整个加法（NULL + 1 = NULL）。
- 所有参与加权的
CASE WHEN必须返回数值类型，显式补ELSE 0 - 如果同一行可能命中多个条件（比如标题和正文都含关键词），用
SUM()聚合多行得分更安全（需配合GROUP BY）；单行内叠加则用+连接多个CASE表达式 - MySQL 默认大小写不敏感，PostgreSQL 敏感——用
LOWER()统一转换最省心
标题精确匹配比模糊匹配高多少分才合理
没有标准答案，但经验值是：精确匹配（=）给 10 分，前缀匹配（LIKE 'xxx%'）给 6 分，全文包含（LIKE '%xxx%'）给 3 分，字段为空或未匹配给 0 分。关键不是数字本身，而是梯度要拉开——否则排序结果和随机差不多。
示例（PostgreSQL）：
SELECT id, title, content,
(CASE WHEN title = '数据库优化' THEN 10
WHEN title LIKE '数据库优化%' THEN 6
WHEN title LIKE '%数据库优化%' THEN 3
ELSE 0 END)
+ (CASE WHEN content LIKE '%数据库优化%' THEN 2
ELSE 0 END) AS score
FROM articles
WHERE title LIKE '%数据库优化%' OR content LIKE '%数据库优化%'
ORDER BY score DESC, id;
注意这里没用 OR 拼 WHERE 条件来兜底，而是先用模糊条件筛出候选集，再算分——避免全表扫描时每个行都执行两次 CASE 计算。
为什么ORDER BY里不能直接写CASE WHEN表达式
能写，但不推荐。原因有二：一是可读性差，二是难以复用。更麻烦的是，某些旧版 MySQL（5.7 以前）在 ORDER BY 里引用带函数的列别名会报错（Unknown column 'score' in 'order clause'）。
安全做法始终是：在 SELECT 中定义别名，在 ORDER BY 中直接引用该别名。如果必须动态计算，确保 DBMS 支持（如 PostgreSQL 允许 ORDER BY (CASE ...)，但 SQLite 不支持复杂表达式）。
- 别在
ORDER BY里重复写一遍冗长的CASE WHEN逻辑 - 别依赖列序号（
ORDER BY 5）——加个字段就崩 - 如果得分字段要用于分页（
LIMIT/OFFSET），务必确认排序稳定性（加id等唯一字段兜底）
搜索词带通配符或特殊字符时CASE WHEN还管用吗
不管用。比如用户搜 SQL%，你代码里写 LIKE '%SQL%%'，那个结尾的 % 就变成通配符了，实际匹配的是 “SQL 开头的任意字符串”。更糟的是，输入 SQL_ 可能误匹配 “SQLA”“SQLB”。
正确做法是：对用户输入做转义，再拼进 LIKE。各库语法不同：
- PostgreSQL：
ESCAPE ''+REPLACE(keyword, '', '\') - MySQL：
ESCAPE '$'+REPLACE(keyword, '$', '$$') - 通用保底：
title LIKE CONCAT('%', REPLACE(REPLACE(?, '%', '%'), '_', '_'), '%') ESCAPE ''
这时候 CASE WHEN 本身没坏，坏的是你喂给它的字符串。别跳过这步——线上被注入或错配，第一个背锅的就是这个排序逻辑。
