MySQL 搜索查询按匹配相关性排序

Question

我了解基本的 MySQL 查询，但我不知道如何实现准确且相关的搜索查询。

我的 table 看起来像这样:

   id | kanji
   -------------
   1  | 一子
   2  | 一人子
   3  | 一私人
   4  | 一時
   5  | 一時逃れ

我已经有这个查询:

SELECT * FROM  `definition` WHERE `kanji` LIKE '%一%'

问题是我想对学习到的字符的结果进行排序，一是该查询结果的必需字符。

假设用户知道这些字符:人、子、时

然后，我希望结果按这种方式排序:

   id | kanji
   -------------
   2  | 一人子
   1  | 一子
   4  | 一時
   3  | 一私人
   5  | 一時逃れ

与学习最多的字符匹配的结果应该是第一个。如果可能的话，我想首先显示仅包含已学习字符的结果，然后显示已学习字符和未知字符的混合结果。

我该怎么做？

Answer 1

根据您的偏好，按不匹配字符的数量(增加)排序，然后按匹配字符的数量(减少)排序。

SELECT *,
    (kanji LIKE '%人%')
  + (kanji LIKE '%子%')
  + (kanji LIKE '%時%') score
FROM kanji 
ORDER BY CHAR_LENGTH(kanji) - score, score DESC

<小时/>

或者，执行此操作的关系方法是标准化。像这样创建表:

汉字字符

kanji_id | index | character
----------------------------
      1  |     0 | 一
      1  |     1 | 子
      2  |     0 | 一
      2  |     1 | 人
      2  |     2 | 子
...

然后

SELECT kanji_id,
  COUNT(*) length,
  SUM(CASE WHEN character IN ('人','子','時') THEN 1 END) score
FROM kanji_characters
WHERE index <> 0
  AND kanji_id IN (SELECT kanji_id FROM kanji_characters WHERE index = 0 AND character = '一')
GROUP BY kanji_id
ORDER BY length - score, score DESC

<小时/>

虽然您没有指定在重复字符的情况下应该做什么。上述两种解决方案的处理方式不同。