MySQL IN conditional statement sorting method

This article will introduce the sorting method of MySQL IN conditional statements IN detail. If you need to learn more, please refer.

There is a scenario where the primary key of a table with tens of thousands of records is id. I want to retrieve several records with IDs of 30, 20, 80, and 40 from the table.

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

So I write SQL like this:

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

The result is that it is not returned in the order I gave.

What should I do?

The FIELD () function is 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:

The Code is as follows:	Copy code
SELECT * FROM my_table WHERE id IN (30, 20, 80, 40) order by field (id, 30, 20, 80, 40 );

The sorting process is:

Find the id of the selected record in the FIELD list, and return the position based on the position.

This usage will lead to Using filesort, which is a very inefficient sorting method. Unless the data changes frequently or has a long cache, we do not recommend that you sort the data in this way.

It is a good optimization solution to rearrange the results returned by MySQL in the order of IDs in the memory using PHP.