Shulou(Shulou.com)05/31 Report--

Editor to share with you the example analysis of Order By multi-field sorting rules in MySQL, I believe that most people do not know much about it, so share this article for your reference. I hope you will gain a lot after reading this article. Let's learn about it together.

MySql order by single field

Build a test table as follows:

CREATE TABLE `a` (`code` varchar, `name` varchar) ENGINE=InnoDB DEFAULT CHARSET=utf8;INSERT into a values ('Middle one','I'); INSERT into a values ('Secondary two', 'you'); INSERT into a values ('Senior one','I am'); INSERT into a values ('Senior two','We'); INSERT into a values ('Senior two','my')

The test statement is as follows:

-- hex (): get hexadecimal bytecode select name, hex (name) from an order by name desc

The results are as follows:

Namehex (name) my E68891E79A84 I am E68891E698AF we E68891E4BBAC I E68891 your E4BDA0

Obviously, the sort in MySQL is sorted by bytecode. When the first word is the same, compare the bytecode of the second word, and so on.

MySql order by multi-field

There is a comparison, there is an idea, there is a comparison, there is progress, so we first list the descending sort results of a single field, and then look at the descending sort of gas deposits of the two fields, from which we can analyze the truth.

-- sort descending by name select * from an order by name desc;-- sort descending by code select * from an order by code desc

Order by name desc on the left and order by code desc on the right

Codename high two, my first year of high school, I am in the second year of high school, we are in the first year of high school, you are in the second year of high school, you are in the second year of high school, my first year of high school, I am the second year of high school, you are the first one of me.

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

Let's take a look at the sorting of multiple fields.

-- sort in descending order according to code, name, select * from an order by code, in name desc;codename, I am in the second year of high school, you are in the first year of high school, I am the second year of high school.

The results are as follows: first of all, thanks to qq_27837327 and MjayTang, I have a test here, the original text said that this sql ranking is invalid is wrong. In fact, order by code,name desc is equivalent to order by code asc, name desc.

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

Let's look at the following sentence

-- sort select * from an order by code desc by code and name, name desc;-- the effect of this statement is equivalent to the following statement, where 1 and 2 correspond to code, name select code, name from an order by 1 desc, 2 desc;codename, my second year of high school, my second year of senior high school, I am the second year of senior high school, I am the second grade of you, I am the second year of high school.

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

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

Select * from an order by concat (code,name) desccodename my sophomore year of senior high school I am the second year of senior high school I am the second year of high school you are one of me.

Obviously, in this test example, the effect of order by concat (code, name) desc is equivalent to that of order by code desc, name desc.

The above is all the content of the article "sample Analysis of Order By Multi-field collation in MySQL". Thank you for reading! I believe we all have a certain understanding, hope to share the content to help you, if you want to learn more knowledge, welcome to follow the industry information channel!

Welcome to subscribe "Shulou Technology Information " to get latest news, interesting things and hot topics in the IT industry, and controls the hottest and latest Internet news, technology news and IT industry trends.