SELECT
drug_name,
pinyin
FROM
tbl_drug
WHERE
drug_name LIKE '%阿%'
OR pinyin LIKE '%阿%'
ORDER BY
LOCATE( "阿", pinyin ),
LOCATE( "阿", drug_name ),
LENGTH( pinyin );
like效率没有instr函数效率高
可以优化如下
SELECT * FROM user2 WHERE INSTR(NAME,'张')>0 ORDER BY LENGTH(NAME)