昨天我嘗試解決 mysql 中的字母數(shù)字排序問題，但失敗了。 （在這里閱讀那篇文章）
我確實接近了，并且有正確的概念，只是錯誤的執(zhí)行。
今天，我醒來并頓悟…遞歸。
遞歸的問題在于你必須了解遞歸才能進行遞歸…而我對遞歸的理解不足以在 mysql 中進行遞歸。
但是，通過 chat gippity 來回進行一些操作（我的意思是讓它寫出我要求的內(nèi)容，返回我要求的大約 25%，修復它并將其輸入到新的聊天中，這樣就不會出現(xiàn)問題）不要一直重復大約 2 小時）我得到了有效的答案！
說到重點
愿我向您呈現(xiàn)我的絕唱、我的杰作、生活本身的答案（好吧，這是我見過的 mysql 中真正字母數(shù)字排序的唯一有效解決方案）。
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;
如果你想嘗試一下（并嘗試打破它），你可以使用這個數(shù)據(jù)庫小提琴
那么這是如何運作的呢？
它完成了我最初想做的事情，取出每組數(shù)字并將它們填充到總共 10 位數(shù)字。
很明顯，如果你給它提供幾個包含 11 個連續(xù)數(shù)字的字符串，如果不進行調(diào)整，它就無法工作，但除此之外它工作得很好！
你看，mysql 可以正確地對數(shù)字進行排序，即使在字典排序模式下也是如此，但它有一個缺陷。
它將“11”視為小于“2”，因為它一次對一個字符進行排序（有效）。所以“2”比“1”大，所以它排在第一位。然后它檢查下一個字符，此時排序不正確（至少對于數(shù)字而言）。
為了更好地理解這一點，想象一下 1 實際上是字母“b”，2 是字母“c”。
這就是mysql“看到”數(shù)字的方式，它們只是另一個字符。
因此，如果我有“bb”和“c”，您會期望“bb”出現(xiàn)在“c”之前。現(xiàn)在將數(shù)字交換回去，您就會明白為什么“11”位于“2”之前。
那么這是一個黑客行為嗎？
是的，我們通過填充將數(shù)字“向后”移動來解決這個問題。
回到我們的示例，如果我們將“11”和“2”的長度填充為 3 并將“a”用作 0，則會發(fā)生以下情況：
011 = abb 002 = aac
注意現(xiàn)在排序的方式：
- 字符 1：“a”比“a”大 – 不，它們是相同的。
- 字符 2：“b”比“a”大 – 是的，將“a”放在“b”之前
- 字符 3：現(xiàn)在無關(guān)緊要，我們已經(jīng)發(fā)現(xiàn)了更早發(fā)生的不同且更大的事件。
按照這個邏輯我們現(xiàn)在有：
002 = aac (the second "a" comes before the second "b" in the next row) 011 = abb
這就是它的工作原理！
你要解釋一下遞歸的事情嗎？
有點。我已經(jīng)用這個“繞了房子一圈”，我的知識只是表面水平，但我會嘗試一下。
問題在于 regex 在 mysql 中的工作方式。 regex_substr 只會找到一個匹配項，然后為找到的所有其他匹配項繼續(xù)返回該匹配項。這就是為什么我昨天的解決方案無法正常工作的原因。
但是 regex_replace 有它自己的問題，它似乎沒有正確公開匹配的字符串長度（因此我們無法正確地對其進行 lpad）
這就是為什么我認為遞歸作為答案。
我可以使用 regex_substr 來獲得正確的填充行為，并且由于 regex 的每個循環(huán)本質(zhì)上都是一個新函數(shù)調(diào)用，因此它不會“記住”上一個匹配項，因此它解決了這個問題。
如果你想簡單了解一下邏輯，它實際上并不像看起來那么可怕！
- 我們循環(huán)給定的字符串，查找任何數(shù)字（整個數(shù)字，而不僅僅是單個字符）。
- 然后我們將其從剩余數(shù)據(jù)中刪除，這樣我們就不會再次匹配它。
- 我們?nèi)〕鰟倓偲ヅ涞臄?shù)字并將其填充為總共 10 位數(shù)字。
- 然后我們搜索字符串中的下一個數(shù)字部分并重復該過程，將processed_data構(gòu)建為最終字符串。
- 最后，一旦我們沒有更多的數(shù)字需要處理，我們將剩余的字母添加到processed_data的末尾以完成轉(zhuǎn)換，并將其作為sort_key返回。
然后我們可以在查詢中使用這個 sort_key 來正確排序列。
迭代部分純粹是一個保護工具，以確保它不會完全運行 mysql 服務器內(nèi)存不足或在處理足夠復雜的字符串時使查詢崩潰（或者邏輯中存在錯誤，這意味著它會永遠遞歸）。
這就是一個包裹！
睡在東西上會帶來新的視角，這不是很有趣嗎？
也許我應該嘗試多相睡眠，這樣我每天就可以多睡覺 2-3 次來解決問題，從而成為 10 倍的開發(fā)者？哈哈。
無論如何，你已經(jīng)擁有了它，一個相當強大的true字母數(shù)字排序。
哦，實際上，您可能應該使用 generate 或存儲過程將 sort_key 轉(zhuǎn)換為數(shù)據(jù)庫上的存儲列。遺憾的是，我使用的游樂場似乎不支持這一點，而且今天是周日，所以我將把它留給你，親愛的觀眾！
祝您周末休息愉快，度過愉快的一周。
