MySQL Order By Multi-Field Sorting Rules Code Example

Say it in advance

On a whim, I want to know what rules are used for MySQL order by sorting? Okay, without further ado, let’s get straight to the point.

MySql order by single field

Create a test table as follows:

CREATE TABLE `a` ( 
 `code` varchar(255) DEFAULT NULL, 
 `name` varchar(255) DEFAULT NULL 
)ENGINE=InnoDB DEFAULT CHARSET=utf8; 

INSERT into a values('中一', ​​'我'); 
INSERT into a values('中二', '你'); 
INSERT into a values('高一', '我是'); 
INSERT into a values('高二', '我们'); 
INSERT into a values('高二', '我的');
 

The test statement is as follows:

-- hex()： Get hexadecimal bytecode select name, hex(name) from a order by name desc

The results are as follows:

Obviously, the sorting in MySQL is based on bytecode. When the first word is the same, the bytecode of the second word is compared, and so on.

MySql order by multiple fields

Only with comparison can we have ideas and make progress. Therefore, we first list the descending sort results of a single field, and then look at the descending sort results of two fields, so that we can analyze the reason.

-- Sort in descending order by name select * from a order by name desc; 
-- Sort in descending order by code select * from a order by code desc;

The left side is the result of order by name desc, and the right side is the result of order by code desc

The result is obvious: when a single field is sorted, the order in which other fields appear is naturally sorted.

Let's look at the sorting of multiple fields.

-- Sort in descending order by code, name select * from a order by code, name desc;

The results are as follows: First of all, thank you qq_27837327 and MjayTang. I tested it here. The original text saying that this sql sort is invalid is wrong. In fact, order by code, name desc is equivalent to order by code asc, name desc

After testing, it was found that the sorting effect of select * from a order by code and name desc is still invalid.

Let’s look at the following statement

-- Sort in descending order by code and name select * from a order by code desc, name desc; 
-- The effect of this statement is equivalent to the following statement, where 1 and 2 correspond to code and name respectively 
select code, name from a order by 1 desc, 2 desc; 

Comparing the descending sorting of single fields of code and name, we can find that when using order by code desc, name desc, MySQL will first sort by code in descending order, and then use name for descending sorting based on the code in descending order.

In addition, we can also use the contat function to concatenate multiple fields and then sort them. But make sure the field cannot be null. Let's take a look at the concat sql statement and results.

select * from a order by concat(code,name) desc 

Obviously, in this test example, order by concat(code, name) desc is equivalent to order by code desc, name desc

Summarize

The above is the full content of this article. I hope it will be helpful to everyone. Welcome to refer to: Several important MySQL variables, Detailed code explanation of the relationship between MySQL master library binlog (master-log) and slave library relay-log, Detailed explanation of MySQL prepare principle, etc. If you have any questions, please leave a message at any time. Everyone is welcome to communicate and discuss.