MySQL sorting by General, custom, and Chinese characters

MySQL sorting by General, custom, and Chinese characters

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

The following are three common sorting methods.

1. Regular sorting ASC DESC

ASC forward order

DESC Flashback

-- You don't need to talk about it here.

2. Custom sorting

Custom sorting is based on the specific string (number) order you want.

The main function is to use FIELD (str, str1, str2, str3 ,...)

Custom sorting of MySQL, str and str1, str2, str3... compare and follow str1, str2, str3... if str is null or str1, str2, str3... in, 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 matching conditions are sorted.

3. sort by Chinese pinyin letters

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

Eg:

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

The above section describes MySQL sorting by regular, custom, and Chinese pinyin letters. I hope it will be helpful to you. If you have any questions, please leave a message, the editor will reply to you in a timely manner. Thank you very much for your support for the help House website!