sql模糊匹配按匹配度匹配的五种方法

上周接了个新需求，写完之后产品让我加一下模糊匹配按照匹配度匹配，查阅相关资料写出了四种，产品直呼过瘾。

1 、order by case 不过在数据量大的时候查询会很慢，甚至有可能崩溃宕机

SELECT * FROM table1 WHERE name LIKE '%张三%' 
ORDER BY(
CASE   
WHEN name LIKE  '张三' THEN 1
WHEN name LIKE  '张三%' THEN 2
WHEN name LIKE  '%张三%' THEN 3
ELSE 4 
END )  

2、UNION ALL 不过如果筛选条件太多的话在mapper文件中写会很麻烦

SELECT * FROM table1 WHERE name LIKE '张三'
UNION ALL
SELECT * FROM table1 WHERE name LIKE '%张三%'

3、length关键字 根据数据的长短进行排序

SELECT  * FROM table1 WHERE name LIKE '%张三%' ORDER BY (LENGTH (name)) 

4、 LOCATE 关键字 不过 select LOCATE(‘张三’, “张三丰”) 结果是1； select LOCATE(‘张’, “张三丰”) 结果也是1

排序的时候先查到哪个数据哪个就放在前面

select LOCATE('张三', "张三丰")  result：1；
SELECT * FROM table1 WHERE name LIKE '%张三%' ORDER BY (SELECT  LOCATE('张三', name))

5、全文索引 score是根据查出数据的长度来进行排序
输入nike dunk 也会查询出 nike XXX dunk

SELECT *, (MATCH(shoes) AGAINST('nike' IN BOOLEAN MODE) - ABS(LENGTH(translate_before) *10)) AS score
FROM table1
WHERE MATCH(shoes) AGAINST('nike' IN BOOLEAN MODE)
ORDER BY score DESC