MySQL中的真正字母数字排序——为什么解决方法总是递归？

昨天我在MySQL中尝试解决字母数字排序的问题，但失败了。你可以在这里阅读这篇文章。(read that article here)

尽管我差一点就成功了，只是在操作上出了点岔子。

今天早上醒来，我突然想到了一个概念，就是那种不断重复的递归。

递归的问题在于，你得先懂递归才能做递归……我目前对递归还不是很熟悉，所以用MySQL里还做不了递归。

然而通过几次与ChatGPT的对话（也就是说，它只提供了我要求的约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;

全屏；退出全屏

如果你想试一试（并试着测试它），你可以在这个DB fiddle上试试。

它做到了我原来想做的，将每组数字都补足到10位。

所以很明显，如果你给这个输入几个包含11个连续数字的字符串，它会无法正常工作，除非你做一些调整。不过除此之外，它运行正常。

你知道，MySQL 可以正确地对数字进行排序，即使在字典顺序模式下也一样，但它有一个小问题。

它认为"11"比"2"小，因为它是逐字符比较（实际上就是这样）。因此"2"比"1"大，所以"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 下一行的第二个“b”出现在002之后
011 = abb

切换到全屏，或者结束全屏

就是这样！这就是它的工作方式！

## 你打算解释一下递归吗？

有点像。我对这个话题只是大概知道一些，但我会试着做一下。

问题出在MySQL中正则表达式的工作方式上。`REGEX_SUBSTR`只会找到一个匹配项并一直返回这个匹配项，即使有其他匹配项也是如此。这就是为什么我昨天的解决方案没有正确工作。

但 `REGEX_REPLACE` 本身也有一些问题，它似乎无法正确返回匹配字符串的长度，因此我们无法正确地使用它来进行 `LPAD` 操作。

所以我想到递归就是解决方案。

我可以使用 `REGEX_SUBSTR` 来实现正确的填充效果，而每次循环实际上都是一个新的函数调用，因此它不会记住之前的匹配，这就解决了这个问题。

如果你想快速了解一下这个逻辑，其实并没有看起来那么复杂！

* 我们遍历给定的字符串，查找完整的数字（而不仅仅是单个数字字符）。
* 然后我们将该数字从 `remaining_data` 中移除，避免再次匹配。
* 我们将其补足为总长度为10位的数字。
* 然后我们在字符串中查找下一个数字部分，并重复上述过程，构建 `processed_data` 作为最终字符串。
* 最后，当我们没有更多的数字可处理时，我们将任何剩余的字母添加到 `processed_data` 的末尾以完成转换，最后将 `processed_data` 作为 `sort_key` 返回。

然后我们就可以在查询中使用这个 `sort_key` 来正确地排序这个列。

`iteration`部分是一个纯粹的防护工具，确保在处理非常复杂的字符串（或者由于逻辑错误导致无限递归）时，不会耗尽MySQL服务器的内存或使查询崩溃。

## 收工啦！

睡觉能让人看到不同的看法，不是挺有意思的吗？

也许我应该试试多相睡眠（[polyphasic sleep](https://en.wikipedia.org/wiki/Polyphasic_sleep)），这样我每天可以多睡2到3次，成为一个10倍效率的程序员？哈哈。

总之，这就是一个相当可靠的字母数字排序方法。

哦，实际上你应该把 `sort_key` 转换为数据库中的存储列，使用 `GENERATE` 或存储过程。不过我使用的环境似乎不支持这个功能，而且今天是周日，所以这个就交给你了，亲爱的朋友们！

祝你周末愉快，下周也一切顺利。