对于模糊查询的SQL，怎么优先返回等值记录

说明：记录一次SQL改进的方法，希望能对大家有启发。

场景

前端项目有一个输入框，根据输入的银行名称，去模糊查询对应的数据库表，返回结果集，显示到下拉列表中。

因为银行名称字段包括了分行名，所以结果是模糊查询后可能仍有十几万条记录。考虑到效率和安全，在SQL后面用limit做了个限制，假设只返回了前10条。

数据库表如下：

但是这会出现一个问题，如下：

select *
from tb_bank
where bank_name like '%中国银行%'
limit 10;

返回的结果里面，因为做了数量限制，且等值匹配的“中国银行”在数据库表的下面，这样查询的结果就比较离谱了。

结果中没有返回等值匹配的“中国银行”记录，如果用户就想选择这条记录，那么怎么都选不到。

解决

所以，我对上面的SQL做了一点改进。使用传入的银行名称字符数量与数据库表中的银行名称字符数量做一个比值，比值越高，说明匹配度越高，如1，则是等值匹配的。

最后，再根据这个比值降序返回，就达到了我想要的结果。如下：

select *, char_length('中国银行') / char_length(bank_name) as score
from tb_bank
where bank_name like '%中国银行%'
order by score desc
limit 10;

查询结果，等值匹配的在最上面，后面匹配度（即 传入的银行名称字符数 / 数据库中的银行名称字符数）越高，则越靠前，相当于一个简易的算分，分值越高越靠前。

在代码中，则可以换成对应的名称，如下：

select *, char_length(#{bankName}) / char_length(bank_name) as score
from tb_bank
where bank_name like concat('%', #{bankName}, '%')
order by score desc
limit 10;

代码007普通

打赏 收藏 海报 链接