如何在mysql中實現自然排序

wooyoo發表於2017-04-16
MySql排序

背景

熟悉mysql的同學應該清楚，mysql在對字串做order by排序時是按照字典序進行排序的，但是如果字串中包含數字的話（我們稱這種型別的字串為alphanumeric），僅按照字典序的排序結果對使用者不太友好。我們舉個例子，假設我們在mysql中存了一張files表，裡面記錄了檔案的id以及檔案的name，表裡的資料如下：

id name
1 1測試2
2 測試
3 1
4 1測試12
5 1測試1
6 1測試20

name欄位目前是亂序的，現在我們對該表執行order by查詢，即SELECT * FROM files ORDER BY name：

id name
3 1
5 1測試1
4 1測試12
1 1測試2
6 1測試20
2 測試

我們重點關注以“1測試”開頭的那四個name，他們末尾都帶有了數字，但因為mysql預設採取字典序排序，所以排序的結果是"1" < "12" < "2" < "20"，這顯然不太友好，我們更期望數字部分是根據數字大小排序的。這種非數字部分按照字典序排序，數字部分按照數字大小進行排序的方式我們就稱之為自然排序（natural sort）。

與字典序排序相比，自然排序的結果更加人性化，對使用者更加友好。現代作業系統其實已經實現了檔名的自然排序，我們以Mac為例：

如何在mysql中實現自然排序

現代的程式語言也都內建了自然排序演算法，比如php的natsort方法。但是由於mysql中沒有內建對應的函式，我們只能通過其他的辦法來實現mysql的自然排序。

思路

要想對mysql做一些擴充套件，一共有以下三種方法：

  1. 修改底層原始碼。
  2. 編寫mysql擴充套件（plugin）。
  3. 編寫儲存函式（stored function）。

顯然，要想實現自然排序，我們勢必要對order by做一些手腳。如果是第一種方法，不僅難度大，而且不利於mysql的版本升級。如果是第二種方法，mysql擴充套件又不支援擴充套件語法層面的能力。那麼我們只能採用第三種方法了，也就是儲存函式或者又稱之為UDF。

如果採用儲存函式，那麼其實我們在排序時還是用的字典序，所以我們需要藉助儲存函式將原來待排序的欄位（比如例子中的name欄位）轉換成就算按照字典序排序也能達到自然排序效果的欄位。

我們再來看看自然排序的核心思想：非數字部分按照字典序排序，數字部分按照數字大小排序。所以我們只要將數字部分轉換成可按大小排序的字串即可。我們以上例中的”1測試12“和”1測試2“為例，我們將末尾”12“和”2“轉化為定長的欄位，比如”0000012“和”0000002“。此時”0000012“按照字典序比”0000002“大，這就實現字典序下的自然排序。

好了說了這麼多，show me the code：

DELIMITER ;;
CREATE FUNCTION NatSort (Varstring VARCHAR(50))
RETURNS VARCHAR(1000)
READS SQL DATA
DETERMINISTIC
BEGIN
DECLARE v_length INT DEFAULT 0;
DECLARE v_num VARCHAR(50) DEFAULT '';
DECLARE v_index INT DEFAULT 1;
DECLARE v_result VARCHAR(1000) DEFAULT '';
DECLARE v_flag INT DEFAULT 0;
DECLARE v_char CHAR(1) DEFAULT '';
SET v_flag=0;
SET v_index=1;
SET v_length=CHAR_LENGTH(Varstring);

-- 遍歷字串
WHILE v_index <= v_length DO
    SET v_char = mid(Varstring,v_index,1);
    IF (ASCII(v_char)>=48 AND ASCII(v_char)<=57) THEN
        SET v_num=concat(v_num,mid(Varstring,v_index,1)); -- 獲取字串裡的數字
        SET v_flag = 1;
    ELSE 
        IF v_flag = 1 THEN
            SET v_flag=0;
            SET v_result=concat(v_result,lpad(cast(v_num AS UNSIGNED),10,'0')); -- 將數字轉成定長字串
            SET v_num=''; -- 重置v_num
        END IF;

        SET v_result=concat(v_result, v_char);
    END IF;
    SET v_index = v_index + 1;
END WHILE;

IF v_flag=1 THEN
    SET v_result=concat(v_result,lpad(cast(v_num AS UNSIGNED),10,'0'));
END IF;

RETURN v_result;
END;;複製程式碼

在上述程式碼中，我們將所有數字轉成了共10位的定長字串。我們看一下函式的具體效果，我們執行SELECT *, NatSort(name) as name_sort FROM files ORDER BY name_sort：

id name name_sort
3 1 0000000001
5 1測試1 0000000001測試0000000001
1 1測試2 0000000001測試0000000002
4 1測試12 0000000001測試0000000012
6 1測試20 0000000001測試0000000020
2 測試 測試

從結果中我們可以看到，已經實現了自然排序，經過實際測試，效能還行。

注意和優化

細心的同學可能已經發現了，上述演算法並不完美。我們在程式碼中將數字擴充為了定長為10位的字串，那麼如果原字串中的數字長度大於10位，那麼演算法就失效了。所以在實際使用過程中，要根據具體的業務場景設定定長的位數。

另外，為了提高查詢效能，我們可以事先就將轉換後的字串儲存在表中，這樣就不需要每次查詢時都需要呼叫儲存函式。這也是常用的一種以“空間換時間”的優化手段。

本文首發於www.kissyu.org/2017/04/16/… 轉載請標註作者和來源

相關文章