下面是本人关于mysql 自定义排序（field，INSTR，locate）的一点心得，希望对大家有所帮助
首先说明这里有三个函数（order by field，ORDER BY INSTR，ORDER BY locate）
原表：
id user pass
aaa aaa
bbb bbb
ccc ccc
ddd ddd
eee eee
fff fff
下面是我执行后的结果：
SELECT * FROM `user` order by field(2,3,5,4,id) asc
id user pass
aaa aaa
ccc ccc
ddd ddd
eee eee
fff fff
bbb bbb
根据结果分析：order by field(2,3,5,4,1,6) 结果显示顺序为：1 3 4 5 6 2
SELECT * FROM `user` order by field(2,3,5,4,id) desc
id user pass
bbb bbb
aaa aaa
ccc ccc
ddd ddd
eee eee
fff fff
根据结果分析：order by field(2,3,5,4,1,6) 结果显示顺序为：2 1 3 4 5 6
SELECT * FROM `user` ORDER BY INSTR( '2,3,5,4', id ) ASC
id user pass
aaa aaa
fff fff
bbb bbb
ccc ccc
eee eee
ddd ddd
根据结果分析：order by INSTR(2,3,5,4,1,6) 结果显示顺序为：1 6 2 3 5 4
SELECT * FROM `user` ORDER BY INSTR( '2,3,5,4', id ) DESC
id user pass
ddd ddd
eee eee
ccc ccc
bbb bbb
aaa aaa
fff fff
根据结果分析：order by INSTR(2,3,5,4,1,6) 结果显示顺序为：4 5 3 2 1 6
SELECT * FROM `user` ORDER BY locate( id, '2,3,5,4' ) ASC
id user pass
aaa aaa
fff fff
bbb bbb
ccc ccc
eee eee
ddd ddd
根据结果分析：order by locate(2,3,5,4,1,6) 结果显示顺序为：1 6 2 3 5 4
SELECT * FROM `user` ORDER BY locate( id, '2,3,5,4' ) DESC
id user pass
ddd ddd
eee eee
ccc ccc
bbb bbb
aaa aaa
fff fff
根据结果分析：order by locate(2,3,5,4,1,6) 结果显示顺序为：4 5 3 2 1 6
如我想要查找的数据库中的ID顺序首先是（2,3,5,4）然后在是其它的ID顺序，你首先要把他降序排即（4 5 3 2），然后在 SELECT * FROM `user` ORDER BY INSTR( '4,5,3,2', id ) DESC limit 0,10 或用 SELECT * FROM `user` ORDER BY locate( id, '4,5,3,2' ) DESC 就得到你想要的结果了。
id user pass
bbb bbb
ccc ccc
eee eee
ddd ddd
aaa aaa
fff fff