Sort by order of values in a select statement "in" clause in mysql

I'm selecting a set of account records from a large table (millions of rows) with integer id values. As basic of a query as one gets, in a sense. What I'm doing us building a large comma separated list, and passing that into the query as an "in" clause. Right now the result is completely unordered. What I'd like to do is get the results back in the order of the values in the "in" clause.

I assume instead I'll have to build a temporary table and do a join instead, which I'd like to avoid, but may not be able to.

Thoughts? The size of the query right now is capped at about 60k each, as we're trying to limit the output size, but it could be arbitrarily large, which might rule out an "in" query anyway from a practical standpoint, if not a physical one.

Thanks in advance.

Actually, this is better:

SELECT * FROM your_table
WHERE id IN (5,2,6,8,12,1)
ORDER BY FIELD(id,5,2,6,8,12,1);

heres the FIELD documentation:

http://dev.mysql.com/doc/refman/5.0/en/string-functions.html#function_field

A bit of a trick....

SELECT * FROM your_table
WHERE id IN (5,2,6,8,12,1)
ORDER BY FIND_IN_SET(id,'5,2,6,8,12,1') DESC;

note that the list of ID's in the find_in_set is a string, so its quoted. Also note that without DESC, they results are returned in REVERSE order to what the list specified.

If your query is 60K, that's a sign that you're doing it the wrong way.

There is no other way to order the result set than by using an ORDER BY clause. You could have a complicated CASE clause in your order by listing all the elements in your IN clause again, but then your query would probably be 120K.

I know you don't want to, but you should put the values in the IN clause in a table or a temporary table and join with it. You can also include a SortOrder column in the temporary table, and order by that. Databases like joins. Doing it this way will help your query to perform well.

This is what I get for mysql 8.0. It seems opposite to above answers.

sort in same order as list specified:

SELECT * FROM your_table
WHERE id IN (5,2,6,8,12,1)
ORDER BY FIND_IN_SET(id,'5,2,6,8,12,1');

sort in reverse order as list specified:

SELECT * FROM your_table
WHERE id IN (5,2,6,8,12,1)
ORDER BY FIND_IN_SET(id,'5,2,6,8,12,1') DESC;