A brief tutorial on ordering Mysql query results by value _mysql

How do you sort MySQL query results? This is a lot of people have mentioned the problem, the following teach you how to the MySQL query results by a certain value, if you are interested, may wish to see.

Before there is a feature modification that requires MySQL query results:

ID Name * * *

1 Lucy ...

3 Lucy ...

2 Lily ...

4 Lucy ...

The name for Lucy's priority row in front, baffled, some people may say simple union or get a temporary table or something, actually I have thought about it, but the SQL logic itself is a lot of (above is just a simple example), then the Union or temporary table may be around a big detour, Later I saw an article trying to add order by Find_in_set (name, ' Lucy '), and the result was Lucy all in the following, then I changed to ORDER by Find_in_set (name, ' Lucy ') DESC implementation result

ID Name * * *

1 Lucy ...

3 Lucy ...

4 Lucy ...

2 Lily ...

Basic implementation, but a little unsure of the mood, check the MySQL document found Find_in_set syntax

Find_in_set (Str,strlist)  
 

If string str is in the string column datasheet strlist that consists of n chains, the return value ranges from 1 to N. A String column data table is a string of strings that are separated by "," symbols. If the first argument is a constant string, and the second is the Type SET column, the Find_in_set () function is optimized, using bit computations. If STR is not strlist or strlist is an empty string, the return value is 0. If any of the arguments are null, the return value is null. This function will not work correctly when the first argument contains a comma (",")

mysql> SELECT find_in_set (' B ', ' a,b,c,d '); 
 
    -> 2 

Look at this, I'm guessing why I'm going to add desc. Find_in_set return value is, when there is Lucy, return to his position, no time for 0, empty time null, so sort for 1,1,1,0, if added on the column for

ID Name Find_in_set * *

1 Lucy 1 ...

3 Lucy 1 ...

2 Lily 0 ...

4 Lucy 1 ...

The table structure is as follows:

Mysql> select * from test; 

+----+-------+ 
| id | name  | 
+----+-------+ 
|  1 | test1 | 
|  2 | test2 | 
|  3 | test3 | 
|  4 | test4 | 
|  5 | Test5 | 
+----+-------+ 

Execute the following sql:

Mysql> SELECT * FROM Test where ID in (3,1,5); 

+----+-------+ 
| id | name  | 
+----+-------+ 
|  1 | test1 | 
|  3 | test3 | 
|  5 | Test5 | 
+----+-------+ 
3 rows in Set (0.00 sec) 

The result of this select in MySQL is automatically sorted by ID,

If we

SELECT * FROM table where ID in (3,6,9,1,2,5,8,7);

This situation is taken out, in fact, id or press 1,2,3,4,5,6,7,8,9, sorted, but if we really want to press in the order in which to do? Can SQL be completed? Do you need to take it back and have a foreach?
Actually, it can be.

SELECT * FROM table where ID in (3,6,9,1,2,5,8,7) Order by field (id,3,6,9,1,2,5,8,7);

The order of the coming out is the specified order.
With regard to the efficiency of this sort,
Some articles point out:

FIELD (STR,STR1,STR2,STR3,...)
Returns the index (position) of Str in the STR1, str2, STR3, ... list. Returns 0 If Str is not found.

Sorting process: Finds the ID of the selected record in the FIELD list and returns the position, sorted by position.
This usage can result in a Using filesort, which is a very inefficient sort of method. This is not recommended unless the data changes at a very low frequency or has a long cache.
The authors recommend that you sort by yourself in your program code.
But some people say this sort of thing doesn't have any performance bottlenecks.
Specifically, test yourself.