MySQL 关键字模糊匹配按照匹配度排序

Myth丶恋晨 2024-03-27 12:46 91阅读 0赞

MySQL 关键字模糊匹配，并按照匹配度排序。

方式一、按照关键字搜索，然后根据关键字所占比例排序

SELECT
    	drug_name,
    	pinyin
    FROM
    	tbl_drug
    WHERE
    	pinyin LIKE '%AM%'
    ORDER BY
    	LENGTH(pinyin);

效果如下

![32431ee766b904e44ddd0625e6d82c29.jpeg][]

方式二、将关键字开头记录排序在前

SELECT
    	drug_name,
    	pinyin
    FROM
    	tbl_drug
    WHERE
    	pinyin LIKE 'AM%'
    UNION
    	SELECT
    		drug_name,
    		pinyin
    	FROM
    		tbl_drug
    	WHERE
    		pinyin LIKE '%AM%';

效果如下

![095b47abd27e79c5a32fac1d0caaa7cb.jpeg][]

方式三、将关键字开头排序在前，再根据匹配度排序记录

SELECT
    	drug_name,
    	pinyin
    FROM
    	(SELECT
    		drug_name,
    		pinyin
    	FROM
    		tbl_drug
    	WHERE
    		pinyin LIKE 'AM%'
    	ORDER BY
    		LENGTH(pinyin)
    	LIMIT 999999) t1
    UNION
    	SELECT
    		drug_name,
    		pinyin
    	FROM
    		(SELECT
    			drug_name,
    			pinyin
    		FROM
    			tbl_drug
    		WHERE
    			pinyin LIKE '%AM%'
    		ORDER BY
    			LENGTH(pinyin)
    		LIMIT 999999) t2
    LIMIT 0,20;

结果如下，这块中文 “（）” 所占长度较大。在此有一个问题，子查询的排序无效，如果子查询中 order by 不带 limit，会被优化器干掉

![42a61c96aba009fa8fce4f200b51e116.jpeg][]

以上方式为网上整理总结，下面这个方式为个人改进

方式四、先根据关键字所在字段 index 排序，然后再根据匹配度排序

SELECT
        drug_name,
        pinyin
    FROM
        tbl_drug
    WHERE
        pinyin LIKE '%AM%'
    ORDER BY
        LENGTH(substring_index(pinyin,'AM',1)),
        LENGTH(pinyin)
    LIMIT 0,20

效果如下

![f6497b8212be04e035d5eda18703a0e9.jpeg][]

延伸版：

SELECT
    	drug_name,
    	pinyin 
    FROM
    	tbl_drug 
    WHERE
    	LOCATE( "阿", pinyin ) > 0 
    	OR LOCATE( "阿", drug_name ) > 0 
    ORDER BY
    	LOCATE( "阿", pinyin ),
    	LOCATE( "阿", drug_name ),
    	LENGTH( pinyin );

结果如下：

![180b065396433c1cb38e7c0061ea54fc.jpeg][]

[32431ee766b904e44ddd0625e6d82c29.jpeg]: https://image.dandelioncloud.cn/pgy_files/images/2024/03/27/55027e3f6cb44e659c397c6f69c08e2b.png
[095b47abd27e79c5a32fac1d0caaa7cb.jpeg]: https://image.dandelioncloud.cn/pgy_files/images/2024/03/27/7b91d4b057794531a7b247af29c8912a.png
[42a61c96aba009fa8fce4f200b51e116.jpeg]: https://image.dandelioncloud.cn/pgy_files/images/2024/03/27/b1d8c6902d86489487b322d4c7c02061.png
[f6497b8212be04e035d5eda18703a0e9.jpeg]: https://image.dandelioncloud.cn/pgy_files/images/2024/03/27/c439dfe4641a47c49a88fc5fe0dfedb3.png
[180b065396433c1cb38e7c0061ea54fc.jpeg]: https://image.dandelioncloud.cn/pgy_files/images/2024/03/27/723101e213b54134a9df40c9cbeb9bab.png