Pengisihan Alphanumeric / semulajadi dalam MySQL - mengapa jawapannya sentiasa rekursi?

Semalam saya cuba menyelesaikan pengisihan alfanumerik dalam MySQL dan gagal. (baca artikel itu di sini)

Saya memang rapat dan mempunyai konsep yang betul, cuma pelaksanaan yang salah.

Hari ini, saya bangun dan mengalami epiphany...rekursi.

Masalah dengan rekursi ialah anda perlu memahami rekursi untuk dapat melakukan rekursi...dan saya tidak faham rekursi cukup untuk melakukan rekursi dalam MySQL.

Walau bagaimanapun, dengan sedikit Chat Gippity berulang-alik (yang saya maksudkan supaya ia menulis apa yang saya minta, mendapatkan kembali kira-kira 25% daripada apa yang saya minta, membetulkannya dan memasukkannya ke dalam sembang baharu supaya ia tidak 'jangan terus berulang selama kira-kira 2 jam) Saya mendapat jawapan yang berkesan!

Sampai ke tahap

Bolehkah saya mempersembahkan kepada anda lagu swan saya, karya agung saya, jawapan kepada kehidupan itu sendiri (baiklah, satu-satunya penyelesaian yang berkesan untuk pengisihan alfanumerik sebenar dalam MySQL yang saya lihat).

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;

Dan jika anda ingin mencubanya (dan cuba memecahkannya) anda boleh bermain dengan biola DB ini

Jadi bagaimana ini berfungsi?

Ia melakukan apa yang saya mahu lakukan pada asalnya, mengambil setiap kumpulan nombor dan menambahnya kepada jumlah 10 digit.

Jadi jelas sekali jika anda menyuap ini beberapa rentetan dengan 11 digit angka berturut-turut ia tidak akan berfungsi tanpa pelarasan, tetapi selain itu ia berfungsi dengan baik!

Anda lihat, MySQL boleh mengisih nombor dengan betul, walaupun dalam mod susunan leksikografi, tetapi ia mempunyai satu kelemahan.

Ia mengira "11" sebagai lebih kecil daripada "2" kerana hakikatnya ia mengisih satu aksara pada satu masa (dengan berkesan). Jadi "2" lebih besar daripada "1" jadi ia didahulukan. Kemudian ia menyemak aksara seterusnya, yang mana pengisihan adalah tidak betul (untuk nombor sekurang-kurangnya).

Untuk memahami perkara ini dengan lebih baik, bayangkan jika 1 sebenarnya huruf "b" dan 2 ialah huruf "c".

Begitulah MySQL "melihat" nombor, ia hanyalah satu aksara.

Jadi jika saya mempunyai "bb" dan "c" anda akan menjangkakan "bb" datang sebelum "c". Sekarang tukar nombor semula dan anda boleh lihat sebab "11" didahulukan sebelum "2".

Jadi ini adalah hack?

Ya, kami mengalih keluar isu dengan mengalihkan nombor "kembali" melalui pelapik.

Berbalik kepada contoh kita, jika kita mengalas "11" dan "2" kepada 3 panjang dan menggunakan "a" sebagai 0, ini yang berlaku:

011 = abb
002 = aac 

perhatikan cara pengisihan sekarang:

• watak 1: ialah "a" lebih besar daripada "a" - tidak, mereka adalah sama.
• watak 2: ialah "b" lebih besar daripada "a" - ya, letakkan "a" sebelum "b"
• watak 3: kini tidak relevan dan kami telah menemui kejadian lebih awal yang berbeza dan lebih besar.

Jadi dengan logik itu kita kini mempunyai:

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

Dan begitulah caranya!

Adakah anda akan menerangkan perkara rekursi?

Semacam. Saya telah "keliling rumah" dengan yang ini dan pengetahuan saya adalah tahap permukaan, tetapi saya akan mencubanya.

Masalah datang dengan cara RegEx berfungsi dalam MySQL. REGEX_SUBSTR hanya akan mencari satu perlawanan dan kemudian terus mengembalikannya untuk setiap perlawanan lain yang ditemuinya. Jadi itulah sebabnya penyelesaian saya dari semalam tidak berfungsi dengan betul.

Tetapi REGEX_REPLACE mempunyai isu tersendiri di mana ia nampaknya tidak mendedahkan panjang rentetan padanan dengan betul (jadi kami tidak boleh LPAD dengannya dengan betul)

Itulah sebabnya saya memikirkan tentang rekursi sebagai jawapannya.

Saya boleh menggunakan REGEX_SUBSTR untuk mendapatkan gelagat pelapik yang betul, dan kerana setiap gelung melalui RegEx pada asasnya adalah panggilan fungsi baharu, ia tidak "mengingat" padanan sebelumnya, jadi ia menyelesaikan masalah itu.

Dan jika anda mahukan langkah ringkas melalui logik, ia sebenarnya tidak semenakutkan seperti yang kelihatan!

• Kami melingkari rentetan yang diberikan, mencari sebarang nombor (keseluruhan nombor, bukan hanya satu aksara).
• Kami kemudian mengeluarkannya daripada baki_data supaya kami tidak memadankannya lagi.
• Kami mengambil nombor yang baru kami padankan dan memasukkannya menjadi 10 digit jumlah panjang.
• Kami kemudian mencari bahagian angka seterusnya dalam rentetan dan mengulangi proses itu, membina processed_data sebagai rentetan terakhir kami.
• akhirnya apabila kami tiada lagi nombor untuk diproses, kami menambah sebarang huruf yang tinggal pada penghujung data_proses untuk melengkapkan transformasi dan kami mengembalikannya sebagai kunci_isih.

Kemudian kami boleh menggunakan kunci_isih ini dalam pertanyaan kami untuk memesan lajur dengan betul.

Dan bahagian lelaran adalah semata-mata alat perlindungan, untuk memastikan ia tidak sepenuhnya menjalankan pelayan MySQL daripada memori atau ranap pertanyaan jika rentetan yang cukup kompleks diproses (atau terdapat ralat dalam logik yang bermaksud ia akan berulang selama-lamanya).

Itu bungkus!

Bukankah kelakar bagaimana tidur pada sesuatu membawa perspektif baharu?

Mungkin saya patut mencuba tidur polyphasic supaya saya boleh tidur dengan masalah 2-3 kali lebih kerap setiap hari dan menjadi pembangun 10x? haha.

Bagaimanapun, anda mempunyainya, jenis benar yang agak kukuh.

Oh dan sebenarnya anda mungkin harus menukar kunci_isih kepada lajur yang disimpan pada pangkalan data anda menggunakan GENERATE atau prosedur tersimpan. Malangnya taman permainan yang saya gunakan nampaknya tidak menyokongnya dan ia adalah hari Ahad jadi saya akan menyerahkannya kepada anda, penonton yang dikasihi!

Selamat berehat di hujung minggu anda dan minggu yang hebat.

Atas ialah kandungan terperinci Pengisihan Alphanumeric / semulajadi dalam MySQL - mengapa jawapannya sentiasa rekursi?. Untuk maklumat lanjut, sila ikut artikel berkaitan lain di laman web China PHP!