MySQL sorting by conventional sorting, custom sorting, and sorting by Chinese pinyin letters

MySQL regular sorting, custom sorting, and sorting by Chinese pinyin letters. When writing actual SQL, we sometimes need to sort the conditional set.

Here are 3 commonly used sorting methods, mark them down

1. Conventional sorting ASC DESC

ASC Positive Sequence

DESC Flashback

-- No need to say more here

2. Custom sorting

Custom sorting is to sort according to the specific string (number) order you want.

Mainly use the function FIELD(str,str1,str2,str3,...)

MySQL custom sorting, str is compared with str1, str2, str3..., and output in the order of str1, str2, str3... If str is null or does not exist in str1, str2, str3..., the sequence is 0.

eg:

SELECT * FROM TEST ORDER BY FIELD(value,'test1','test2','test3','test4') ASC/DESC

eg2:

SELECT * FROM TEST WHERE VALUE IN('test1','test2','test3','test4') ORDER BY FIELD(value,'test1','test2','test3','test4') ASC/DESC -- ensure that only those that meet the conditions are sorted

3. Sort by Chinese Pinyin

If the table field uses GBK encoding, we can directly order by value, because GBK itself is sorted according to the pinyin letters ABCDEFGHIGK..., when the first digit is the same, the second digit will be compared, and so on. If the table field uses UTF-8 encoding, we usually encode it, so we can use MySQL's convert method to convert gbk for sorting.

eg:

SELECT * FROM TEST ORDER BY CONVERT(value USING GBK) ASC/DESC

The above is what I introduced to you about MySQL regular sorting, custom sorting and Chinese pinyin alphabet sorting. I hope it will be helpful to you. If you have any questions, please leave me a message and I will reply to you in time. I would also like to thank everyone for their support of the 123WORDPRESS.COM website!