Some lesser-known sorting methods in MySQL

Preface

ORDER BY 字段名升序/降序, I believe that everyone here knows this sorting statement, but when encountering some special sorting, using only the field name cannot meet the needs. Here are some sorting methods I have encountered:

1. Preparation

For better demonstration and understanding, first prepare a student table, add three fields: number, name, and grade, and insert several pieces of data, as shown in the figure:

2. Conditional sorting

Requirement 1: Sort the scores from high to low

Even the aunties selling vegetables on the street can knock, and you can easily complete it by using ORDER BY examScore DESC (as shown in the left picture below).

Requirement 2: Sort the scores from high to low, and those without scores are at the front

Customer experience is the most important. It is normal to make such a request to facilitate the second entry of scores. To achieve this sorting, the above statement cannot be implemented, so conditional sorting is needed. First, determine if the score is empty and assign a maximum value, and then sort. For example ORDER BY IF(examScore IS NULL,101,examScore) DESC can also be easily implemented (as shown in the right figure below).

need	Requirement 1	Requirement 2
Statements	ORDER BY examScore DESC	ORDER BY IF(examScore IS NULL,101,examScore) DESC
Effect

3. Custom sorting

Customers are like gods, and it is not uncommon to see fancy demands. For example, they require Zhang San and Li Si to be at the front, and other students to be ranked from high to low according to their grades. In this case, you need to use custom sorting, and the MySQL built-in FIELD function (returns the index of the corresponding string) can help you achieve it.

Statement 1:
ORDER BY FIELD(studentName,'张三','李四') ASC, examScore DESC;

The result of running the above statement puts Zhang San and Li Si at the end (as shown in the left picture below). Naturally, we should put Zhang San and Li Si at the front and sort them in descending order.

Statement 2:
ORDER BY FIELD(studentName,'李四','张三') DESC, examScore DESC;

The result was exactly what he wanted (as shown in the right picture below).

Statements	Sentence 1	Statement 2
Effect

Later, I found that the FIND_IN_SET function can also be used to achieve this, and after testing with 1 million data records, FIND_IN_SET has better performance.

ORDER BY FIND_IN_SET(studentName,'李四,张三') DESC, examScore DESC;

4. Sorting Chinese characters by their first letters

Some friends may wonder why it is so little known since Chinese characters can be easily sorted using the common ORDER BY 字段ASC .
In fact, when users create table fields using the GBK character set, the problem can be solved by directly using ORDER BY 字段ASC . Some users use the utf8 character set to prevent garbled characters, and simple sorting statements are powerless (as shown in the left picture below). Therefore, it is OK to convert the fields to GBK when sorting (as shown in the right picture below).

Statements	ORDER BY studentName ASC	ORDER BY CONVERT(studentName USING GBK) ASC
Effect

Summarize

This concludes this article about some lesser-known sorting methods in MySQL. For more information about MySQL sorting methods, please search 123WORDPRESS.COM’s previous articles or continue browsing the following related articles. I hope everyone will support 123WORDPRESS.COM in the future!