How does ORDER BY FIELD() in MySQL work internally
For the record
SELECT * FROM mytable WHERE id IN (1,2,3,4) ORDER BY FIELD(id,3,2,1,4);
should work as well because you do not have to order the list in the WHERE
clause
As for how it works,
FIELD() is a function that returns the index position of a comma-delimited list if the value you are searching for exists.
- IF id = 1, then FIELD(id,3,2,1,4) returns 3 (position where 1 is in the list)
- IF id = 2, then FIELD(id,3,2,1,4) returns 2 (position where 2 is in the list)
- IF id = 3, then FIELD(id,3,2,1,4) returns 1 (position where 3 is in the list)
- IF id = 4, then FIELD(id,3,2,1,4) returns 4 (position where 4 is in the list)
- IF id = anything else, then FIELD(id,3,2,1,4) returns 0 (not in the list)
The
ORDER BY
values are evaluated by what FIELD() returns
You can create all sorts of fancy orders
For example, using the IF() function
SELECT * FROM mytable
WHERE id IN (1,2,3,4)
ORDER BY IF(FIELD(id,3,2,1,4)=0,1,0),FIELD(id,3,2,1,4);
This will cause the first 4 ids to appear at the top of the list, Otherwise, it appears at the bottom. Why?
In the ORDER BY
, you either get 0 or 1.
- If the first column is 0, make any of the first 4 ids appear
- If the first column is 1, make it appear afterwards
Let's flip it with DESC in the first column
SELECT * FROM mytable
WHERE id IN (1,2,3,4)
ORDER BY IF(FIELD(id,3,2,1,4)=0,1,0) DESC,FIELD(id,3,2,1,4);
In the ORDER BY
, you still either get 0 or 1.
- If the first column is 1, make anything but the first 4 ids appear.
- If the first column is 0, make the first 4 ids appear in the original order
YOUR ACTUAL QUESTION
If you seriously want internals on this, goto pages 189 and 192 of the Book
for a real deep dive.
In essence, there is a C++ class called ORDER *order
(The ORDER BY
expression tree). In JOIN::prepare
, *order
is used in a function called setup_order()
. Why in the middle of the JOIN
class? Every query, even a query against a single table is always processed as a JOIN (See my post Is there an execution difference between a JOIN condition and a WHERE condition?)
The source code for all this is sql/sql_select.cc
Evidently, the ORDER BY
tree is going to hold the evaluation of FIELD(id,3,2,1,4)
. Thus, the numbers 0,1,2,3,4 are the values being sorted while carrying a reference to the row involved.