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

Mysql how to return results according to the order of ID values, many novices are not very clear about this, in order to help you solve this problem, the following editor will explain in detail for you, people with this need can come to learn, I hope you can get something.

A problem encountered today is that there is a query that needs to return the result set according to the specified order of ID values. In fact, it can also be sorted in the program, but I suddenly want to see if I can directly use the Mysql query to return.

Field () function

Mysql provides a function Field () that can customize sorting in the order we give.

Example:

Suppose there is a city information table called regions with the primary key id and a name attribute name. Now you want to query that ID is 2, 3, 1 and return it in this order.

Select id, name from regions;#id name

1 Beijing 2 Shanghai 3 Shenzhen

Use field ()

Select id, name from regions order by field (id, 2, 3, 1); # id name

2 Shanghai 3 Shenzhen 1 Beijing

This achieves the goal of sorting in a custom order.

Performance

Mysql > explain select id from regions order by field (id, 2, 3, 1) +-+ | id | select_type | table | type | | possible_keys | key | key_len | ref | rows | Extra | |-- |-- | | | 1 | SIMPLE | regions | index | NULL | id | 4 | NULL | 3 | Using index | Using filesort | +

Because when we use Order By Field, we specify that the primary key is sorted according to the primary key ID. There is a primary key index of Primary, and he will use id to find records that are equal to 2GI, so we can see that there is Using index in Extra. If you change to another field that does not have an index, it will not have it here. And Order By clause cannot use this index, only Filesort sorting can be used, which is the reason why there is Using filesort in Extra.

The process is roughly as follows:

Scan all leaf nodes sequentially from the first leaf node of the id index

Find the real row data according to the primary key id to the primary key index (clustered index) recorded by each leaf node

Determine whether the row data meets the conditions of id = 2, 3, 1, and if so, take it out and return it.

It is almost necessary to traverse the whole table. Some people say that it looks up the id of the selected records in the FIELD list and returns the location, which is sorted by location.

This usage leads to Using filesort (of course, using Filesort is not necessarily slower and sometimes faster than not using it), which is a very inefficient way of sorting.

Usually the ORDER BY clause is matched with the LIMIT clause, and only part of the line is taken out. It is obviously not an efficient way to sort all rows just to fetch the rows of top1.

The Field () function can help us directly complete some necessary sorting in the database layer, which can simplify the business code, but at the same time, it also has compatibility and performance problems. It is suggested that it can be used in places where the data changes frequently or has a long cache, and in the case of a large amount of data, you can use the database to query the data and sort it in the program.

Is it helpful for you to read the above content? If you want to know more about the relevant knowledge or read more related articles, please follow the industry information channel, thank you for your support.

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.