MySQL 中真正的字母數字/自然排序 - 為什麼答案總是遞歸？

昨天我嘗試解決 MySQL 中的字母數字排序問題，但失敗了。 （在這裡閱讀那篇文章）

我確實接近了，並且有正確的概念，只是錯誤的執行。

今天，我醒來並頓悟...遞歸。

遞歸的問題在於你必須了解遞歸才能進行遞歸...而我對遞歸的理解不足以在 MySQL 中進行遞歸。

但是，透過Chat Gippity 來回進行一些操作（我的意思是讓它寫出我要求的內容，返回我要求的大約25%，修復它並將其輸入到新的聊天中，這樣就不會出現問題）不要一直重複大約2 小時）我得到了一個有效的答案！

說到重點

願我向您呈現我的絕唱、我的傑作、生活本身的答案（好吧，這是我見過的 MySQL 中真正字母數字排序的唯一有效解決方案）。

WITH RECURSIVE process_numbers AS (
    SELECT 
        data_value,
        data_value AS remaining_data,
        CAST('' AS CHAR(20000)) AS processed_data,
        1 AS iteration
    FROM test_data

    UNION ALL

    SELECT
        data_value,
        CASE 
            WHEN LOCATE(REGEXP_SUBSTR(remaining_data, '[0-9]+'), remaining_data) > 0 THEN
                SUBSTRING(
                    remaining_data,
                    LOCATE(REGEXP_SUBSTR(remaining_data, '[0-9]+'), remaining_data)
                    + LENGTH(REGEXP_SUBSTR(remaining_data, '[0-9]+'))
                )
            ELSE '' 
        END AS remaining_data,

        CONCAT(
            processed_data,
            CASE 
                WHEN LOCATE(REGEXP_SUBSTR(remaining_data, '[0-9]+'), remaining_data) > 0 THEN
                    LEFT(remaining_data, LOCATE(REGEXP_SUBSTR(remaining_data, '[0-9]+'), remaining_data) - 1)
                ELSE remaining_data
            END,
            CASE
                WHEN REGEXP_SUBSTR(remaining_data, '[0-9]+') IS NOT NULL THEN
                    RIGHT(CONCAT('0000000000', REGEXP_SUBSTR(remaining_data, '[0-9]+')), 10)
                ELSE ''
            END
        ) AS processed_data,

        iteration + 1
    FROM process_numbers
    WHERE LENGTH(remaining_data) > 0
          AND iteration < 100
)


SELECT 
    data_value,
    CONCAT(processed_data, remaining_data) AS sort_key
FROM process_numbers
WHERE remaining_data = ""
ORDER BY sort_key;

如果你想嘗試（並嘗試打破它），你可以使用這個資料庫小提琴

那麼這是如何運作的呢？

它完成了我最初想做的事情，取出每組數字並將它們填充到總共 10 位數字。

很明顯，如果你給它幾個包含 11 個連續數字的字串，如果不進行調整，它就無法工作，但除此之外它工作得很好！

你看，MySQL 可以正確地對數字進行排序，即使在字典排序模式下也是如此，但它有一個缺陷。

它將“11”視為小於“2”，因為它一次對一個字元進行排序（有效）。所以「2」比「1」大，所以它排在第一位。然後它檢查下一個字符，此時排序不正確（至少對於數字而言）。

為了更好地理解這一點，想像一下 1 實際上是字母“b”，2 是字母“c”。

這就是MySQL「看到」數字的方式，它們只是另一個字元。

因此，如果我有“bb”和“c”，您會期望“bb”出現在“c”之前。現在將數字交換回去，您就會明白為什麼「11」位於「2」之前。

所以這是一個駭客行為？

是的，我們透過填充將數字「向後」移動來解決這個問題。

回到我們的範例，如果我們將「11」和「2」的長度填滿為 3 並將「a」用作 0，則會發生以下情況：

011 = abb
002 = aac 

注意現在排序的方式：

• 字元 1：「a」比「a」大 - 不，它們是相同的。
• 字元 2：「b」比「a」大 - 是的，將「a」放在「b」之前
• 字元 3：現在無關緊要，我們已經發現了更早發生的不同且更大的事件。

所以按照這個邏輯我們現在有：

002 = aac (the second "a" comes before the second "b" in the next row)
011 = abb

這就是它的工作原理！

你要解釋一下遞歸的事情嗎？

有點。我已經用這個“繞了房子一圈”，我的知識只是表面水平，但我會嘗試一下。

問題在於 RegEx 在 MySQL 中的運作方式。 REGEX_SUBSTR 只會找到一個符合項，然後繼續為找到的所有其他符合項目傳回該符合項。這就是為什麼我昨天的解決方案無法正常運作的原因。

但是 REGEX_REPLACE 有它自己的問題，它似乎沒有正確公開匹配的字串長度（因此我們無法正確地對其進行 LPAD）

這就是為什麼我認為遞歸作為答案。

我可以使用 REGEX_SUBSTR 來獲得正確的填充行為，並且由於 RegEx 的每個循環本質上都是一個新函數調用，因此它不會「記住」上一個匹配項，因此它解決了這個問題。

如果你想簡單了解邏輯，它其實並不像看起來那麼可怕！

• 我們循環給定的字串，尋找任何數字（整個數字，而不僅僅是單一字元）。
• 然後我們將其從剩餘資料中刪除，這樣我們就不會再次匹配它。
• 我們取出剛剛匹配的數字並將其填充為總共 10 位數字。
• 然後我們搜尋字串中的下一個數字部分並重複這個過程，將processed_data建構為我們的最終字串。
• 最後，一旦我們沒有更多的數字需要處理，我們將剩餘的字母加到processed_data的末尾以完成轉換，並將其作為sort_key返回。

然後我們可以在查詢中使用這個 sort_key 來正確排序列。

迭代部分純粹是一個保護工具，以確保它不會完全運行 MySQL 伺服器記憶體不足或在處理足夠複雜的字串時使查詢崩潰（或者邏輯中存在錯誤，這意味著它會永遠遞歸）。

這就是一個包裹！

睡在東西上會帶來新的視角，這不是很有趣嗎？

也許我應該嘗試多相睡眠，這樣我每天就可以多睡 2-3 次來解決問題，從而成為 10 倍的開發者？哈哈。

無論如何，你已經擁有了它，一個相當強大的true字母數字排序。

哦，實際上，您可能應該使用 GENERATE 或預存程序將 sort_key 轉換為資料庫上的儲存列。遺憾的是，我使用的遊樂場似乎不支持這一點，而且今天是周日，所以我將把它留給你，親愛的觀眾！

祝您週末休息愉快，度過愉快的一週。

以上是MySQL 中真正的字母數字/自然排序 - 為什麼答案總是遞歸？的詳細內容。更多資訊請關注PHP中文網其他相關文章！