1. Vấn đề.

Mình muốn truy vấn 1 số lượng record nhất định trong database rồi sort nó theo thứ tự được set sẵn (không phải là asc hay desc).
Giả sử mình có một bảng users với các trường id, user_name

Giờ mình muốn truy vấn bảng này những record có id = 1, 2, 5 và record trả ra sẽ được sắp xếp sẵn theo thứ tự user_name = B, A, E.

2. Cách giải quyết.

Cách 1: Sử dụng case when để gán lại giá trị cho user_name trước khi sort

SELECT * FROM users 
  WHERE id IN (1, 2, 5)
  ORDER BY 
     CASE 
     WHEN user_name = 'B' THEN 1
     WHEN user_name = 'A' THEN 2
     WHEN user_name = 'E' THEN 3
     ELSE 4 
  END

Cách 2: Sử dụng ORDER BY FIELD của mysql

SELECT * FROM users
  WHERE id IN (1, 2, 5)
  ORDER BY FIELD (user_name, 'B', 'A', 'E')

Có thể thấy cách 2 ngắn gọn hơn rất nhiều nó sẽ đảm bảo những bản ghi có user_name = B > user_name = A > user_name = E
Trong trường hợp có cả những bản ghi có user_name khác B, A, E thì thứ tự sắp xếp của những bản ghi này vẫn theo thứ tự bọn nó được lưu trong database và nằm trên những bản ghi trong ORDER BY FIELD.

3. ORDER BY FIELD hoạt động như thế nào?

Thực chất ORDER BY FIELD hoạt động giống hệt với cách 1 sử dụng case when
Hàm FIELD sẽ trả ra index tương ứng với mỗi giá trị truyền vào. Như vậy các giá trị B, A, E lần lượt sẽ được gán với index 1, 2, 3. Đối với các giá trị không nằm trong list thì sẽ được gán index = 0.

4. Một số cách dùng kết hợp với hàm khác của ORDER BY FIELD.

Sử dụng với IF(): Trong trường hợp trên những bản ghi có trong FIELD được gán giá trị từ 1-n trong khi bản ghi không có trong FIELD sẽ được gán giá trị 0. Như vậy những bản ghi không có trong FIELD sẽ được đẩy lên đầu. Tuy nhiên sẽ có trường hợp ta muốn những bản ghi có trong FIELD hiện lên đầu, ta có thể sử dụng hàm IF() để query như sau:

SELECT * FROM users
ORDER BY IF(FIELD(id, 1, 3, 4)=0, 1, 0), FIELD(id, 1, 3, 4);

Trong đó:

IF(FIELD(id, 1, 3, 4)=0, 1, 0) là viết tắt của

if FIELD(id, 1, 3, 4) == 0
  return 1
else 
  return 0
end

như vậy ta sẽ được sort_index như sau

vì IF được đặt trước nên sẽ ưu tiên sort theo IF trước và các bản ghi có trong điều kiện sẽ được đánh index là 0 và đẩy lên đầu.

*Note: ORDER BY FIELD hay ORDER CASE ... WHEN đều tính toán lại giá trị trước khi sort, order nên nó sẽ không sử dụng index.