field

SinceVersion dev

field

description

Syntax

field(Expr e, param1, param2, param3,.....)

在order by子句中，可以使用自定义排序，可以将expr中的数据按照指定的param1，2，3顺序排列。 不在param参数中的数据不会参与排序,将会放在最前面,可以使用asc，desc控制整体顺序。 如果有NULL值，可以使用nulls first，nulls last控制null的顺序

example

2. mysql> select k1,k7 from baseall where k1 in (1,2,3) order by field(k1,2,1,3);
3. +------+------------+
4. | k1   | k7         |
5. +------+------------+
6. |    2 | wangyu14   |
7. |    1 | wangjing04 |
8. |    3 | yuanyuan06 |
9. +------+------------+

11. mysql> select class_name from class_test order by field(class_name,'Suzi','Ben','Henry');
12. +------------+
13. | class_name |
14. +------------+
15. | Suzi       |
16. | Suzi       |
17. | Ben        |
18. | Ben        |
19. | Henry      |
20. | Henry      |
21. +------------+

23. mysql> select class_name from class_test order by field(class_name,'Suzi','Ben','Henry') desc;
24. +------------+
25. | class_name |
26. +------------+
27. | Henry      |
28. | Henry      |
29. | Ben        |
30. | Ben        |
31. | Suzi       |
32. | Suzi       |
33. +------------+

35. mysql> select class_name from class_test order by field(class_name,'Suzi','Ben','Henry') nulls first;
36. +------------+
37. | class_name |
38. +------------+
39. | null       |
40. | Suzi       |
41. | Suzi       |
42. | Ben        |
43. | Ben        |
44. | Henry      |
45. | Henry      |
46. +------------+

keywords

1. field