union和子查詢中order by一起使用導致排序失效問題及解決

2022-12-29 14:02:08

一、前言

分頁查詢的需求如同家常便飯，多數情況下主要利用order by和limit即可實現，有些稍複雜一點的可能需要用到union操作去連線多個子查詢結果集。

然而這三個操作是有一些需要留意的問題，下文將列舉出3個可能碰到的情況。

MySQL版本：5.7.21

簡單的union操作如下：

SELECT * FROM tb_artist WHERE id >= 10
UNION
SELECT * FROM tb_artist WHERE id < 10

其結果為：

若子查詢中包含order by，寫法假設如下：

SELECT * FROM tb_artist WHERE id >= 10 ORDER BY id DESC
UNION
SELECT * FROM tb_artist WHERE id < 10

則語法報錯：1221 - Incorrect usage of UNION and ORDER BY

應當將子查詢括起來，正確寫法如下：

(SELECT * FROM tb_artist WHERE id >= 10 ORDER BY id DESC)
UNION
(SELECT * FROM tb_artist WHERE id < 10)

其結果為：

語法沒問題了，但是注意到子查詢的order by卻沒有效果，故引出下面第二種問題。

解決方式是在子查詢的order by後新增limit操作即可，具體可以limit一個不小於子查詢結果集大小的數值，如下：

(SELECT * FROM tb_artist WHERE id >= 10 ORDER BY id DESC LIMIT 99999)
UNION
(SELECT * FROM tb_artist WHERE id < 10)

其結果為：

該問題與union無直接聯絡，屬於order by及limit本身的注意點。

即，如果SQL中的order by條件比較寬鬆不夠嚴格，或者說是結果集中的每行記錄存在並列或不唯一的次序的話，MySQL可能會隨機給並列記錄行進行排序，特別是排序又分頁查詢配合了limit操作，可能會導致上一頁有的記錄行又出現在了下一頁的結果集之中。

對一些後臺系統的分頁表格中可能感覺不明顯，但對於同樣SQL查詢邏輯的C端下拉選單的效果一目瞭然，特別是對帶有圖片的，很容易看到資料的重複。

解決方案：

所以在做分頁查詢時，要儘量保證每行記錄都有唯一確定的次序，具體做法可以在原有排序條件後新增id或編號等這類唯一值的欄位（索引欄位也可以）。

例如，在藝術家artist建立時間降序排序的基礎上，再對唯一code進行排序：

SELECT * FROM tb_artist WHERE logic_delete = 0 
ORDER BY 
	create_time DESC, 
	code ASC 
LIMIT 0, 5

原因分析：

排序離不開演演算法，在關係型資料庫中，往往會存在多種排序演演算法。通過MySQL的原始碼和官方檔案介紹可以得知，它的排序規律可以總結如下：

所以解決排序分頁資料重複問題有兩種方式，第一種就是，在排序中加上唯一值，比如主鍵 id，這樣由於 id 是唯一的，就能確保參與排序的 key 值不相同；第二種就是避免使用堆排序，讓order by根據索引來排序。

說白了，就是order by後面的欄位要有索引。

另外，使用JPA分頁查詢時，若order by的是非索引欄位，通過檢視JPA的sql發現，不會再去自動在order by後新增索引或id欄位，需要注意。

MySQL5.7 相關檔案：

以上為個人經驗，希望能給大家一個參考，也希望大家多多支援it145.com。