在管理數據庫時，我們經常遇到需要按嵌入在字符串中的數字進行排序的情況。這在實際應用中尤為常見，比如文件名、代碼版本號等字段中通常包含數字，而這些數字往往是排序的關鍵。本文將詳細介紹如何在MySQL中利用正則表達式提取字符串中的數字并按這些數字進行排序，以一個具體的例子來說明，使得即使是數據庫操作的初學者也能輕松理解和應用。
場景示例
假設你管理的數據庫中有一個表 sys_oss，它記錄了多媒體文件的信息。表中有一個字段 original_name，其中包含了文件的命名信息，格式大致為“中文_數字.mp4”。現在，我們的任務是按照文件名中的數字順序對這些記錄進行排序。
示例數據
讓我們先看幾個 original_name 的示例值：
- 中文_1.mp4
- 中文_12.mp4
- 中文_2.mp4
- 中文_10.mp4
如果按照字符串默認的排序方式，排序結果將會是：
- 中文_1.mp4
- 中文_10.mp4
- 中文_12.mp4
- 中文_2.mp4
這顯然不符合數字自然排序的邏輯，因為字符串排序是按字符編碼順序逐一比較的。我們的目標是按照數字部分的實際數值進行排序，即：
- 中文_1.mp4
- 中文_2.mp4
- 中文_10.mp4
- 中文_12.mp4
使用 REGEXP_SUBSTR 函數提取并排序
在MySQL 8.0及以上版本中，我們可以使用 REGEXP_SUBSTR() 函數來提取字符串中的數字部分。這個函數允許我們使用正則表達式來指定我們想要匹配的模式。在這個例子中，我們使用正則表達式 \\d+ 來匹配一個或多個數字。
以下是完整的SQL查詢，用于實現按數字排序：
SELECT *
FROM sys_oss
WHERE original_name LIKE '%中文%'
ORDER BY CAST(REGEXP_SUBSTR(original_name, '\\d+') AS UNSIGNED);
這條SQL語句做了以下幾件事：
WHERE original_name LIKE '%中文%'：篩選出所有文件名包含“中文”的記錄。REGEXP_SUBSTR(original_name, '\\d+')：從original_name中提取第一組連續的數字。CAST(... AS UNSIGNED)：將提取出的字符串轉換成無符號整數，以便按數字進行排序。
結論
使用 REGEXP_SUBSTR 提取數字并結合 CAST 函數轉換類型，使我們能夠按照數字的實際數值對字符串進行排序。這種技巧不僅適用于文件名，也可以廣泛應用于任何包含數字的字符串字段排序，如訂單編號、版本號等
)
