CarsonIT

技术思绪摘录旅行笔记

数据库查询实现匹配度排序，最匹配的在第一条（MYSQL为例）原创声明

平时需求中，搜索数据，只要求存在关键字搜出来即可，如果需要让你把匹配度按顺序返回呢，你打算怎么实现呢？本文记录了我的实践，如果你有更好方案可以评论给我

需求：首先一张表有五个字段内容需要搜索，无论搜索哪个字段，匹配度要按顺序返回，实现最好的检索效果。

比如以下字段需要检索：

Name：名称

Code：编码

AliasName：别名

MnemonicCode：助记码

PinyinCode：拼音码

1、为了达到更好的搜索效果，对搜索可能性进行排序，比如用户最常用的就是用Name进行搜索，其次是Code，依次排序

2、如果关键字在内容中存在，并且出现在内容中的位置越靠前，权值越高。

你好啊
我是谁，你好啊

比如以上数据中，搜索“啊”，第一条数据中“啊”出现的位置是第三个字符，就比第二条数据匹配度高。

3、数据内容越短，权值越高。

高血压
高血压I型

比如以上数据中，搜索“高血压”，最匹配的明显是第一条数据。

4、现在我们将关键字出现的位置、内容长度进行排序，基本上能实现效果。

但是有个很大的问题：这个字段中，检索不到关键字，其他字段包含关键字，检索不到的字段长度会影响排序位置，直接让检索结果不准确。

我们还需要加一条规则，如果检索不到关键字，这个字段的内容长度要为0，不能影响排序。

综上规则，我们写了一个sql

SELECT Name,Code, AliasName, MnemonicCode, PinyinCode  FROM icd_diagnoses
WHERE SearchCode LIKE '%高血压%'
ORDER BY
LOCATE('高血压', Name),if(LOCATE('高血压', Name)>=0,CHAR_LENGTH(Name),0),
LOCATE('高血压', Code),if(LOCATE('高血压', Code)>=0, CHAR_LENGTH(Code),0),
LOCATE('高血压', PinyinCode),if(LOCATE('高血压', PinyinCode)>=0, CHAR_LENGTH(PinyinCode),0),
LOCATE('高血压', AliasName),if(LOCATE('高血压', AliasName)>=0, CHAR_LENGTH(AliasName),0),
LOCATE('高血压', MnemonicCode),if(LOCATE('高血压', MnemonicCode)>=0, CHAR_LENGTH(MnemonicCode),0);

这个sql中，每个字段，都将关键字匹配位置、内容长度参与排序，位置为0时，长度也为0

转换为EF的写法，如下：

.Where(e => e.SearchCode.Contains(searchCode))
.OrderBy(x => x.Name.IndexOf(searchCode))
.ThenBy(x => x.Name.IndexOf(searchCode) >= 0 ? x.Name.Length : 0)

.ThenBy(x => x.Code.IndexOf(searchCode))
.ThenBy(x => x.Code.IndexOf(searchCode) >= 0 ? x.Code.Length : 0)

.ThenBy(x => x.PinyinCode.IndexOf(searchCode))
.ThenBy(x => x.PinyinCode.IndexOf(searchCode) >= 0 ? x.PinyinCode.Length : 0)

.ThenBy(x => x.MnemonicCode.IndexOf(searchCode))
.ThenBy(x => x.MnemonicCode.IndexOf(searchCode) >= 0 ? x.MnemonicCode.Length : 0)

.ThenBy(x => x.AliasName.IndexOf(searchCode))
.ThenBy(x => x.AliasName.IndexOf(searchCode) >= 0 ? x.AliasName.Length : 0)
;