Vấn đềHãy tưởng tượng bạn cần một danh sách duy nhất các ID danh mục từ bảng sản phẩm có 50.000 hàng, được sắp xếp sao cho các danh mục được cập nhật gần đây nhất xuất hiện trước. Bạn có thể thử một truy vấn như sau:
SELECT DISTINCT category_id
FROM products
ORDER BY updated_at DESC;
MySQL sẽ chặn yêu cầu này ngay lập tức. Thay vì nhận được dữ liệu, bạn sẽ thấy một thông báo lỗi cụ thể:
ERROR 3065 (HY000): Expression #1 of ORDER BY clause is not in SELECT list, references column 'products.updated_at' which is not in SELECT list; this is incompatible with DISTINCT
Tại sao lỗi này xảy raMySQL tuân theo logic tập hợp nghiêm ngặt khi xử lý DISTINCT. Từ khóa này yêu cầu hệ thống gộp các hàng trùng lặp thành một hàng đại diện duy nhất.
Xung đột xảy ra do sự mơ hồ. Nếu bạn có ba hàng với category_id = 5 nhưng có ba dấu thời gian updated_at khác nhau, cơ sở dữ liệu sẽ không biết nên sử dụng dấu thời gian nào để sắp xếp cuối cùng. Việc sắp xếp theo một giá trị ẩn không nằm trong kết quả đầu ra là điều không thể về mặt logic một khi các bản ghi trùng lặp đã bị gộp lại.
Cách khắc phục### Cách 1: Thêm cột sắp xếp vào danh sách SELECTCách nhanh nhất để xóa lỗi này là bao gồm cột bạn muốn sắp xếp vào câu lệnh SELECT. Điều này loại bỏ sự mơ hồ bằng cách biến giá trị sắp xếp thành một phần của tập hợp duy nhất.
SELECT DISTINCT category_id, updated_at
FROM products
ORDER BY updated_at DESC;
Lưu ý: Cách này thường làm thay đổi kết quả của bạn. Nếu một category_id có năm giá trị updated_at khác nhau, bạn sẽ thấy năm hàng cho danh mục đó thay vì một. Chỉ sử dụng cách này nếu bạn thực sự cần hiển thị dấu thời gian.
Cách 2: Sử dụng GROUP BY (Phương pháp được ưu tiên)Tôi khuyên dùng GROUP BY cho 90% các trường hợp này. Nó linh hoạt hơn vì bạn có thể sử dụng các hàm tổng hợp như MAX() để chỉ định chính xác cho MySQL giá trị nào sẽ quyết định thứ tự sắp xếp.
SELECT category_id
FROM products
GROUP BY category_id
ORDER BY MAX(updated_at) DESC;
Truy vấn này thông báo cho cơ sở dữ liệu: "Nhóm mọi thứ theo danh mục, chọn thời gian cập nhật mới nhất từ mỗi nhóm và sắp xếp danh sách theo các dấu thời gian đó." Nó rất chính xác và trả về đúng một hàng cho mỗi danh mục.
Cách 3: Sử dụng truy vấn con (Subquery)Đối với các báo cáo phức tạp mà câu lệnh SELECT chính cần được giữ sạch sẽ, hãy xử lý các phần nặng nhọc trong một truy vấn con trước.
SELECT category_id FROM (
SELECT category_id, MAX(updated_at) as latest_update
FROM products
GROUP BY category_id
) AS sorted_data
ORDER BY latest_update DESC;
Phòng ngừa và các phương pháp hay nhấtLỗi này đã trở thành tiêu chuẩn sau khi MySQL 5.7 giới thiệu các thiết lập sql_mode nghiêm ngặt hơn mặc định. Để tránh lỗi này:
- Ưu tiên GROUP BY: Nếu logic sắp xếp của bạn dựa trên dữ liệu không được hiển thị,
GROUP BYhầu như luôn là công cụ tốt hơn.- Tôn trọng ONLY_FULL_GROUP_BY: Bạn có thể tìm thấy lời khuyên trên mạng bảo tắt chế độ này. Đừng làm vậy. Chế độ nghiêm ngặt giúp ngăn chặn các lỗi dữ liệu tinh vi có thể dẫn đến giá trị báo cáo sai lệch.- Logic tập hợp: Luôn nhớ rằngDISTINCThoạt động trên toàn bộ hàng được trả về. Nếu một cột ẩn với người dùng, nó cũng ẩn với bộ máy sắp xếp.## Kiểm traSau khi áp dụng bản sửa lỗi, hãy kiểm tra hiệu suất bằngEXPLAIN. Bạn nên tránh hiện tượng "filesorts" trên các tập dữ liệu lớn.
EXPLAIN SELECT category_id
FROM products
GROUP BY category_id
ORDER BY MAX(updated_at) DESC;
Nếu cột Extra hiển thị Using filesort trên một bảng có hơn 100.000 hàng, hãy cân nhắc thêm một index tổng hợp (composite index) trên (category_id, updated_at). Điều này cho phép MySQL đọc dữ liệu đã sắp xếp trực tiếp từ index, thường giảm thời gian truy vấn từ vài giây xuống còn vài mili giây.