MySQL Order By子句與自然語言排序

在本教學中，您將使用ORDER BY子句了解MySQL中的各種自然排序技術。

下面讓我們使用一個範例資料來開始學習自然排序技術。

假設我們有一個items的表，其中包含兩列：id和item_no。使用以下CREATE TABLE語句建立items表，如下：

CREATE TABLE IF NOT EXISTS items (
    id INT AUTO_INCREMENT PRIMARY KEY,
    item_no VARCHAR(255) NOT NULL
);

我們使用INSERT語句將一些資料插入到items表中：

INSERT INTO items(item_no)
VALUES ('1'),
       ('1C'),
       ('10Z'),
       ('2A'),
       ('2'),
       ('3C'),
       ('20D');

當我們查詢選擇資料並按item_no排序時，得到以下結果：

SELECT 
    item_no
FROM
    items
ORDER BY item_no;

+---------+
| item_no |
+---------+
| 1       |
| 10Z     |
| 1C      |
| 2       |
| 20D     |
| 2A      |
| 3C      |
+---------+
7 rows in set

這不是我們的預期的結果，我們想要看到的結果如下：

+---------+
| item_no |
+---------+
| 1       |
| 1C     |
| 2      |
| 2A       |
| 3C     |
| 10Z      |
| 20D      |
+---------+

這被稱為自然排序。不幸的是，MySQL不提供任何內建的自然排序語法或函式。 ORDER BY子句以線性方式排序字串，即從第一個字元開始的每個字元一次。

為了克服這個問題，首先我們將item_no列分成兩列：prefix 和 suffix。 prefix列儲存item_no的數位部分，suffix列儲存字母部分。然後根據這些列對資料進行排序，如下所示：

SELECT 
    CONCAT(prefix, suffix)
FROM
    items
ORDER BY prefix , suffix;

查詢首先對資料進行數位排序，並按字母順序對資料進行排序。我們就得到預期的結果。

這個解決方案的缺點是必須在插入或更新之前將item_no值分成兩部分。此外，當查詢資料時，必須將這兩列組合成一列。

如果item_no資料格式相當標準，則可以使用以下查詢執行自然排序，而無需更改表的結構。

SELECT 
    item_no
FROM
    items
ORDER BY CAST(item_no AS UNSIGNED) , item_no;

執行上面查詢語句，得到以下結果 -

SELECT 
    item_no
FROM
    items
ORDER BY CAST(item_no AS UNSIGNED) , item_no;

在這個查詢中，首先使用型別轉換將item_no資料轉換為無符號整數。其次，使用ORDER BY子句對數位進行數位排序，然後按字母順序排列。

下面來看看經常要處理的另一個常見的資料。

TRUNCATE TABLE items;

INSERT INTO items(item_no)
VALUES('A-1'),
      ('A-2'),
      ('A-3'),
      ('A-4'),
      ('A-5'),
      ('A-10'),
      ('A-11'),
      ('A-20'),
      ('A-30');

排序後的預期結果如下：

為了得到上面這個結果，可以使用LENGTH函式。請注意，LENGTH函式返回字串的長度。這個做法是首先對item_no資料進行排序，然後按列值排序，如以下查詢：

SELECT 
    item_no
FROM
    items
ORDER BY LENGTH(item_no) , item_no;

執行上面查詢，得到以下結果 -

mysql> SELECT 
    item_no
FROM
    items
ORDER BY LENGTH(item_no) , item_no;
+---------+
| item_no |
+---------+
| A-1     |
| A-2     |
| A-3     |
| A-4     |
| A-5     |
| A-10    |
| A-11    |
| A-20    |
| A-30    |
+---------+
9 rows in set

如下所看到資料就是自然排序的。

但是，如果所有上述解決方案都不適合。則需要在應用程式層執行自然排序。一些語言支援自然排序功能，例如，PHP提供了使用自然排序演算法對陣列進行排序的natsort()函式。

在本教學中，我們已經演示了如何在MySQL中執行自然排序的各種技術。