Analyze MySQL methods of sorting by regular order, 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 condition set. Below are three commonly used sorting methods. Let’s take a look at them.

MySQL regular sorting, custom sorting and sorting by Chinese Pinyin letters. When writing actual SQL, we sometimes need to perform conditional collections Sort.

The following are three commonly used sorting methods, mark them

1. Conventional sorting ASC DESC

ASC forward order

DESC Flashback

--No need to say more here

2. Custom sorting

Custom sorting is based on the specific order you want. Sort in string (numeric) order.

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

MySQL's custom sorting, compare str with str1, str2, str3..., and compare Output in the order of str1, str2, str3..., if str is null or does not exist in str1, str2, str3..., the sequence will be 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 -- 保证只满足条件的进行排序

3. Sort by Chinese Pinyin alphabet

If the table field uses GBK encoding, we can directly order by value, because GBK itself is sorted by Pinyin letters ABCDEFGHIGK..., when the first digit is the same, the second digit will be compared, so that analogy. If the table fields use UTF-8 encoding, usually we will 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 the detailed content of Analyze MySQL methods of sorting by regular order, custom sorting and sorting by Chinese Pinyin letters. For more information, please follow other related articles on the PHP Chinese website!