如何实现MySQL查询结果按字符串在title和translation字段的位置排序？

一、实现目标排序的SQL语句 #

要实现优先按查询串在title中的位置排序，其次按在translation中的位置排序，需要同时计算两个字段的匹配位置，并且处理仅在translation中匹配的记录（此时title中的位置为0，会干扰排序逻辑）。

修正后的SQL如下（以查询串all为例）：

SELECT 
  title, 
  translation, 
  INSTR(title, 'all') AS title_pos,
  INSTR(translation, 'all') AS trans_pos
FROM `dictionary` 
WHERE (title LIKE '%all%' OR translation LIKE '%all%')
ORDER BY 
  -- 优先将title中包含查询串的记录排在前面
  CASE WHEN INSTR(title, 'all') > 0 THEN 0 ELSE 1 END,
  -- 再按title中的匹配位置升序（位置数值越小，出现越早）
  title_pos,
  -- 最后按translation中的匹配位置升序
  trans_pos;

逻辑说明 #

• 用CASE语句区分两类记录：title中匹配查询串的排前面，仅translation匹配的排后面，避免后者因title_pos=0抢占前列。
• 对title匹配的记录，按匹配位置升序排列，保证出现位置越靠前的结果越优先。
• 对仅translation匹配的记录，按其内部的匹配位置升序排列。

二、变音符号异常的原因解释 #

你遇到的含变音符号结果异常，核心原因是MySQL字符集与排序规则的影响：

• 若表/字段使用utf8_general_ci这类「不区分变音」的排序规则，INSTR和LIKE会将带变音的字符（如ä）等价为普通字符（如a），导致匹配位置计算偏离预期。
• 若字段采用单字节编码（如latin1），而变音字符实际为多字节存储，会直接导致INSTR计算的位置偏移错误。

验证字段字符集与排序规则的命令：

SHOW FULL COLUMNS FROM dictionary LIKE 'title';

解决思路：若需要精确匹配变音，可将字段排序规则改为区分变音的类型，比如utf8mb4_bin或utf8mb4_unicode_ci（部分MySQL版本中utf8mb4_unicode_ci默认区分变音）。

内容的提问来源于stack exchange，提问作者biedert