昨天我嘗試解決 mysql 中的字母數(shù)字排序問(wèn)題，但失敗了。 （在這里閱讀那篇文章）

我確實(shí)接近了，并且有正確的概念，只是錯(cuò)誤的執(zhí)行。

今天，我醒來(lái)并頓悟…遞歸。

遞歸的問(wèn)題在于你必須了解遞歸才能進(jìn)行遞歸…而我對(duì)遞歸的理解不足以在 mysql 中進(jìn)行遞歸。

但是，通過(guò) chat gippity 來(lái)回進(jìn)行一些操作（我的意思是讓它寫出我要求的內(nèi)容，返回我要求的大約 25%，修復(fù)它并將其輸入到新的聊天中，這樣就不會(huì)出現(xiàn)問(wèn)題）不要一直重復(fù)大約 2 小時(shí)）我得到了有效的答案！

說(shuō)到重點(diǎn)

愿我向您呈現(xiàn)我的絕唱、我的杰作、生活本身的答案（好吧，這是我見(jiàn)過(guò)的 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; 

如果你想嘗試一下（并嘗試打破它），你可以使用這個(gè)數(shù)據(jù)庫(kù)小提琴

那么這是如何運(yùn)作的呢？

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

很明顯，如果你給它提供幾個(gè)包含 11 個(gè)連續(xù)數(shù)字的字符串，如果不進(jìn)行調(diào)整，它就無(wú)法工作，但除此之外它工作得很好！

你看，mysql 可以正確地對(duì)數(shù)字進(jìn)行排序，即使在字典排序模式下也是如此，但它有一個(gè)缺陷。

它將“11”視為小于“2”，因?yàn)樗淮螌?duì)一個(gè)字符進(jìn)行排序（有效）。所以“2”比“1”大，所以它排在第一位。然后它檢查下一個(gè)字符，此時(shí)排序不正確（至少對(duì)于數(shù)字而言）。

為了更好地理解這一點(diǎn)，想象一下 1 實(shí)際上是字母“b”，2 是字母“c”。

這就是mysql“看到”數(shù)字的方式，它們只是另一個(gè)字符。

因此，如果我有“bb”和“c”，您會(huì)期望“bb”出現(xiàn)在“c”之前?，F(xiàn)在將數(shù)字交換回去，您就會(huì)明白為什么“11”位于“2”之前。

那么這是一個(gè)黑客行為嗎？

是的，我們通過(guò)填充將數(shù)字“向后”移動(dòng)來(lái)解決這個(gè)問(wèn)題。

回到我們的示例，如果我們將“11”和“2”的長(zhǎng)度填充為 3 并將“a”用作 0，則會(huì)發(fā)生以下情況：

011 = abb 002 = aac  

注意現(xiàn)在排序的方式：

• 字符 1：“a”比“a”大 – 不，它們是相同的。
• 字符 2：“b”比“a”大 – 是的，將“a”放在“b”之前
• 字符 3：現(xiàn)在無(wú)關(guān)緊要，我們已經(jīng)發(fā)現(xiàn)了更早發(fā)生的不同且更大的事件。

按照這個(gè)邏輯我們現(xiàn)在有：

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

這就是它的工作原理！

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

有點(diǎn)。我已經(jīng)用這個(gè)“繞了房子一圈”，我的知識(shí)只是表面水平，但我會(huì)嘗試一下。

問(wèn)題在于 regex 在 mysql 中的工作方式。 regex_substr 只會(huì)找到一個(gè)匹配項(xiàng)，然后為找到的所有其他匹配項(xiàng)繼續(xù)返回該匹配項(xiàng)。這就是為什么我昨天的解決方案無(wú)法正常工作的原因。

但是 regex_replace 有它自己的問(wèn)題，它似乎沒(méi)有正確公開(kāi)匹配的字符串長(zhǎng)度（因此我們無(wú)法正確地對(duì)其進(jìn)行 lpad）

這就是為什么我認(rèn)為遞歸作為答案。

我可以使用 regex_substr 來(lái)獲得正確的填充行為，并且由于 regex 的每個(gè)循環(huán)本質(zhì)上都是一個(gè)新函數(shù)調(diào)用，因此它不會(huì)“記住”上一個(gè)匹配項(xiàng)，因此它解決了這個(gè)問(wèn)題。

如果你想簡(jiǎn)單了解一下邏輯，它實(shí)際上并不像看起來(lái)那么可怕！

• 我們循環(huán)給定的字符串，查找任何數(shù)字（整個(gè)數(shù)字，而不僅僅是單個(gè)字符）。
• 然后我們將其從剩余數(shù)據(jù)中刪除，這樣我們就不會(huì)再次匹配它。
• 我們?nèi)〕鰟倓偲ヅ涞臄?shù)字并將其填充為總共 10 位數(shù)字。
• 然后我們搜索字符串中的下一個(gè)數(shù)字部分并重復(fù)該過(guò)程，將processed_data構(gòu)建為最終字符串。
• 最后，一旦我們沒(méi)有更多的數(shù)字需要處理，我們將剩余的字母添加到processed_data的末尾以完成轉(zhuǎn)換，并將其作為sort_key返回。

然后我們可以在查詢中使用這個(gè) sort_key 來(lái)正確排序列。

迭代部分純粹是一個(gè)保護(hù)工具，以確保它不會(huì)完全運(yùn)行 mysql 服務(wù)器內(nèi)存不足或在處理足夠復(fù)雜的字符串時(shí)使查詢崩潰（或者邏輯中存在錯(cuò)誤，這意味著它會(huì)永遠(yuǎn)遞歸）。

這就是一個(gè)包裹！

睡在東西上會(huì)帶來(lái)新的視角，這不是很有趣嗎？

也許我應(yīng)該嘗試多相睡眠，這樣我每天就可以多睡覺(jué) 2-3 次來(lái)解決問(wèn)題，從而成為 10 倍的開(kāi)發(fā)者？哈哈。

無(wú)論如何，你已經(jīng)擁有了它，一個(gè)相當(dāng)強(qiáng)大的true字母數(shù)字排序。

哦，實(shí)際上，您可能應(yīng)該使用 generate 或存儲(chǔ)過(guò)程將 sort_key 轉(zhuǎn)換為數(shù)據(jù)庫(kù)上的存儲(chǔ)列。遺憾的是，我使用的游樂(lè)場(chǎng)似乎不支持這一點(diǎn)，而且今天是周日，所以我將把它留給你，親愛(ài)的觀眾！

祝您周末休息愉快，度過(guò)愉快的一周。