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;

そして、それを試してみたい (そしてそれを壊してみたい) 場合は、この DB フィドルで遊ぶことができます

では、これはどのように機能するのでしょうか?

これは私が当初やりたかったことを実行し、すべての数値グループを取得して合計 10 桁になるように埋め込みます。

したがって、これに 11 桁の連続する数値を含むいくつかの文字列を入力すると、調整なしでは動作しませんが、それ以外は正常に動作します!

ご存知のとおり、MySQL は辞書編集モードであっても数値を正しく並べ替えることができますが、欠点が 1 つあります。

一度に 1 文字ずつ (効率的に) ソートするため、「11」は「2」より小さいものとしてカウントされます。したがって、「2」は「1」より大きいため、最初に来ます。次に、次の文字をチェックします。この時点で (少なくとも数値に関しては) 並べ替えが正しくありません。

これをよりよく理解するには、1 が実際に文字「b」で、2 が文字「c」だったらと想像してください。

これが MySQL が数字を「認識」する方法であり、数字は単なる文字の 1 つです。

つまり、「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

これが仕組みです!

再帰について説明するつもりですか？

なんだか。私はこれについて「家を一周」しており、私の知識は表面レベルですが、試してみるつもりです。

問題は、MySQL で RegEx がどのように動作するかにありました。 REGEX_SUBSTR は、一致を 1 つだけ見つけて、他の一致が見つかるたびにその一致を返し続けます。昨日のソリューションが正しく機能しなかったのはそのためです。

しかし、REGEX_REPLACE には、一致する文字列の長さを正しく公開していないように見えるという独自の問題があります (そのため、正しく LPAD することができません)

それが、私が答えとして再帰について考えた理由です。

REGEX_SUBSTR を使用すると、正しいパディング動作を取得できます。また、RegEx を介した各ループは基本的に新しい関数呼び出しであるため、前の一致を「記憶」しないため、この問題は解決されます。

ロジックを簡単に理解したい場合は、実際は見た目ほど怖くないです!

• 指定された文字列をループして、数値 (単一の文字だけでなく数値全体) を探します。
• その後、それを Remaining_data から削除して、再び一致しないようにします。
• 先ほど一致させた数値を取得し、合計 10 桁になるように埋め込みます。
• 次に、文字列内の次の数値部分を検索し、このプロセスを繰り返し、最終的な文字列としてprocessed_dataを構築します。
• 最後に、処理する数値がなくなったら、残りの文字をprocessed_dataの末尾に追加して変換を完了し、これをsort_keyとして返します。

その後、クエリでこの sort_key を使用して、列を正しく並べ替えることができます。

反復部分は純粋に保護ツールであり、十分に複雑な文字列が処理された場合 (または、ロジックにエラーがあった場合) に MySQL サーバーがメモリ不足で完全に実行されたり、クエリがクラッシュしたりしないようにするためのものです。それは永遠に繰り返されるでしょう)。

それはラップです！

物事について寝ていると新しい視点が生まれるのは面白いと思いませんか?

毎日 2 ～ 3 倍の頻度で問題に取り組み、10 倍の開発者になれるように、多相睡眠を試してみるべきでしょうか?はは。

とにかく、かなり堅牢な真の英数字ソートができました。

ああ、実際には、GENERATE またはストアド プロシージャを使用して、sort_key をデータベース上のストアド列に変換する必要があるでしょう。悲しいことに、私が使っている遊び場はそれをサポートしていないようです。そして今日は日曜日なので、それはあなたに任せます、親愛なる視聴者!

週末の休息と素晴らしい一週間をお過ごしください。

以上がMySQL における真の英数字 / 自然ソート – 答えが常に再帰になるのはなぜですか?の詳細内容です。詳細については、PHP 中国語 Web サイトの他の関連記事を参照してください。