MySQL Order By Comma Separated List

Question

I have the following MySQL query

SELECT * FROM Characters WHERE CharacterID IN (30, 29, 1, 292, 51)

Which returns back my list of characters as below

|   CharacterID   |   CharacterName  |
--------------------------------------
|        1        |    Character 1   | 
|       29        |    Character 29  | 
|       30        |    Character 30  | 
|       51        |    Character 51  | 
|      292        |    Character 292 | 

I need it to return back in the same order as the original comma seperated value. So in this specific instance, I would need it to return...

|   CharacterID   |   CharacterName  |
--------------------------------------
|       30        |    Character 30  | 
|       29        |    Character 29  | 
|        1        |    Character 1   | 
|      292        |    Character 292 | 
|       51        |    Character 51  | 

Is there anyway where I can specifically say something like

SELECT * FROM Characters WHERE CharacterID IN (30, 29, 1, 292, 51)
ORDER BY (CharacterID, specifically in the order of (30, 29, 1, 292, 51))

Answer 1

You could use order by FIELD()

SELECT * FROM Characters WHERE CharacterID IN (30, 29, 1, 292, 51)
ORDER BY FIELD(CharacterID, 30, 29, 1, 292, 51)

FIELD() is a function that returns the index position of a comma-delimited list