MySQL specifying exact order with WHERE `id` IN (...)

Question

Is there an easy way to order MySQL results respectively by WHERE id IN (...) clause? Example:

SELECT * FROM articles WHERE articles.id IN (4, 2, 5, 9, 3)

to return

Article with id = 4
Article with id = 2
Article with id = 5
Article with id = 9
Article with id = 3

and also

SELECT * FROM articles WHERE articles.id IN (4, 2, 5, 9, 3) LIMIT 2,2

to return

Article with id = 5
Article with id = 9

Update: to be more specific, I want to avoid tampering with the data in parentheses in WHERE articles.id IN (4, 2, 5, 9, 3), since those IDs are dynamic and automatically ordered.

Answer 1

I don't think you can do that. There's no "explicit" ordering going on; the "in" statement simply tells you what to retrieve, and has no information about ordering.

Since the examples you've given have no obvious order, your best bet is to handle this in code after the result is returned.

Answer 2

MySQL only sorts with ORDER BY. This is why it's not terribly uncommon for database tables to have something like an ordinality column.

articles
+----+------------+-------+
| id | ordinality | (...) |
+----+------------+-------+
|  2 |          2 |    '' |
|  3 |          5 |    '' |
|  4 |          1 |    '' |
|  5 |          3 |    '' |
|  9 |          4 |    '' |
+----+------------+-------+

SELECT *
  FROM articles
 WHERE articles.id IN (4, 2, 5, 9, 3)
 ORDER BY articles.ordinality

Answer 3

Yeah, kind of:

SELECT * FROM articles
WHERE articles.id IN (4, 2, 5, 9, 3)
ORDER BY FIND_IN_SET(articles.id, '4,2,5,9,3')

but this is non-standard SQL and smells a bit.