MySQL in sort problem: You can sort by in the order in inside

The MySQL in sort problem is a problem, but when you encounter a problem, you find it, and sometimes it's not what you want. For example, there is a scene, a table with tens of thousands of records, a primary key is an ID, and I want to take a few records from the table with ID 30,20,80,40.

Note that 30,20,80,40, which is the order I expected, I want MySQL to return records in the order that I provide ID.

So I write SQL:

SELECT * FROM my_table WHERE ID in (30, 20, 80, 40);

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

What to do?

The FIELD () function is found.

FIELD (STR,STR1,STR2,STR3,...)

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

http://www.jincon.com/archives/235/

Rewrite the SQL statement to:

SELECT * FROM my_table WHERE ID in ((30, 20, 80, 40) ORDER by FIELD (ID);

The sorting process is:

The ID of the selected record is searched in the FIELD list and returned to the location, sorted by position.

Such usage can lead to Using Filesort, which is a very inefficient sort method. This is not recommended unless the data changes frequently, or if there is a long cache.

It's a good idea to rearrange the results of MySQL back in memory by using PHP in an ID order.

In fact, the individual does not recommend the use of such a way , the database for most of the site is a bottleneck, so more time to reduce the pressure of the server database, with PHP processing it, completely can.

Original source: http://www.zizaiyou.cn