MySQL 关键字模糊匹配按照匹配度排序-蒲公英云

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

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

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

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

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

效果如下

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

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

效果如下

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

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，会被优化器干掉

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

方式四、先根据关键字所在字段 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

效果如下

延伸版：

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 );