MySQL in condition statement sorting method

There is a scene, a tens of thousands of-record table, the primary key is ID, and I want to take a few records with ID 30,20,80,40 from the table.

Note that 30,20,80,40 is the order I expected, and I want MySQL to return records in this order.

So I write SQL:

The code is as follows	Copy Code
SELECT * FROM my_table WHERE ID in (30, 20, 80, 40);

As a result, he did not return in the order I gave him.

What to do?

The FIELD () function was found.

The code is as follows	Copy Code
FIELD (STR,STR1,STR2,STR3,...) Returns the index (position) of Str in the STR1, str2, STR3, ... list. Returns 0 If Str is not found.

Rewrite the SQL statement to:

The code is as follows	Copy Code
SELECT * FROM my_table the WHERE ID in (m, 30, 20, 80, 40) by FIELD (ID;

The sorting process is:

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 results of the MySQL return, with PHP in memory in the order of the ID sequence to rearrange, is a good optimization scheme.