MySQL 8.0

By using db<>fiddle, you agree to license everything you submit by Creative Commons CC0.

CREATE TABLE T
	(`UserName` varchar(11))
;
	
INSERT INTO T
	(`UserName`)
VALUES
	('menghsuan'),
	('peihsuanlin'),
	('yahsuan0817'),
	('mohsuan'),
	('kaihsuan'),
	('Hsuan')
;
##直接找前五筆資料,找不到Hsuan
select * from T
where `UserName` like '%Hsuan%'
limit 5


UserName


menghsuan

peihsuanlin

yahsuan0817

mohsuan

kaihsuan


##按照符合度做排序
select * from T
where `UserName` like '%Hsuan%'
order by case 
    when `UserName` = 'Hsuan' then 0
    when `UserName` LIKE 'Hsuan%' then 1
    when `UserName` LIKE '%Hsuan' then 3
    else 2
    end
limit 5







UserName


Hsuan

peihsuanlin

yahsuan0817

menghsuan

mohsuan