MySQL query results are sorted by a certain value

How can I sort MySQL query results? This is a question raised by many people. The following describes how to sort MySQL query results by a certain value. If you are interested, take a look.

Previously, a function was modified, and MySQL query results were required:

Id name ***

1 lucy...

3 lucy...

2 lily...

4 lucy...

The name of lucy is at the top of the list, which cannot be understood. Some people may say that simple union or a temporary table or something. In fact, I also thought about it, however, there are many SQL logic in itself. The above is just a simple example. If union is used, or temporary tables may be a great detour. Later I saw an article trying to add order by find_in_set (name, 'Lucy '), and the result is lucy all below. Then I change it to order by find_in_set (name, 'Lucy') desc and the result is

Id name ***

1 lucy...

3 lucy...

4 lucy...

2 lily...

Basic implementation, but a little uncertain mood, query mysql documentation found find_in_set syntax

 
 

  
  
FIND_IN_SET(str,strlist)   
  
  
 
 
 

If the string str is in the strlist of the string column data table consisting of N substrings, the return value ranges from 1 to N. A data table in a string column is a string consisting of several self-chains separated by the "," symbol. If the first parameter is a constant string and the second parameter is the type SET column, the FIND_IN_SET () function is optimized and computed in bits. If str is not in strlist or strlist is a null string, the return value is 0. If any parameter is NULL, the return value is NULL. This function cannot run properly when the first parameter contains a comma (,).

 
 

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

After reading this, why do I add desc to my estimation? The find_in_set returned value is: when lucy exists, return its position. If not, it is 0. If it is null, It is null, so the sorting is,. If it is added to the column

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 |  
  
  
+----+-------+  
 
 

Run 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 results of this select statement in mysql are automatically sorted in ascending order of IDs,
However, if I want to execute "select * from test where id in (, 5);", the results are sorted by the conditions in, namely, 3, 1, 5. The expected results are as follows:
Id name
3 test3
1 test1
5 test5

The method is as follows:

 
 

  
  
select * from test where id in(3,1,5) order by find_in_set(id,'3,1,5');  
  
  
select * from test where id in(3,1,5) order by substring_index('3,1,2',id,1);  
 
 

Both

Use functions to query row numbers in MySQL

Non-empty question in MySQL Query

MySQL Date and Time Functions

Two common MySql query time period methods

In-depth parsing of MySQL query Cache Mechanism