Lajur JSON MySQL daripada CFML: Simpan, Tanya dan Index

Lajur JSON dalam MySQL menyimpan satu dokumen dalam satu nilai, dan daripada CFML anda menulisnya dengan JSON_OBJECT, membacanya dengan -> dan ->>, serta mengindeksnya melalui lajur terjana. Pada rig ujian artikel ini, Lucee 7.1.1.17-RC dengan MySQL 8.4.11, satu kiraan pada 20,000 baris turun daripada 18.3 ms dengan imbasan penuh kepada 1.85 ms selepas lajur terjana diindeks. Semua blok cfml di bawah dijalankan dalam rig.

Lajur JSON berguna apabila bentuk data berbeza antara baris: butiran pesanan, jawapan borang, muatan webhook. Ia bukan pengganti jadual biasa.

Jadual ujian dan cara menyisip dokumen

Jadual ujian mempunyai tiga lajur: id, pelanggan dan butiran. pelanggan kekal sebagai lajur biasa kerana ia ditapis selalu, manakala semua yang berubah bentuk masuk ke dalam butiran.

<cfscript>
dsn = "kedai";
queryExecute("DROP TABLE IF EXISTS pesanan_json", {}, {datasource: dsn});
queryExecute("CREATE TABLE pesanan_json (
    id INT AUTO_INCREMENT PRIMARY KEY,
    pelanggan VARCHAR(80) NOT NULL,
    butiran JSON NOT NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4", {}, {datasource: dsn});

data = [
    {nama: "Siti", bandar: "Shah Alam", jumlah: 149.90, item: ["buku", "pen"], ahli: true},
    {nama: "Ahmad", bandar: "Johor Bahru", jumlah: 89.50, item: ["papan kekunci", "tetikus"], ahli: false}
];
for (d in data) {
    queryExecute(
        "INSERT INTO pesanan_json (pelanggan, butiran)
         VALUES (:nama, JSON_OBJECT('bandar', :bandar, 'jumlah', :jumlah,
                                    'item', JSON_ARRAY(:i1, :i2), 'ahli', CAST(:ahli AS JSON)))",
        {nama: {value: d.nama, cfsqltype: "cf_sql_varchar"},
         bandar: {value: d.bandar, cfsqltype: "cf_sql_varchar"},
         jumlah: {value: d.jumlah, cfsqltype: "cf_sql_double"},
         i1: {value: d.item[1], cfsqltype: "cf_sql_varchar"},
         i2: {value: d.item[2], cfsqltype: "cf_sql_varchar"},
         ahli: {value: d.ahli ? "true" : "false", cfsqltype: "cf_sql_varchar"}},
        {datasource: dsn}
    );
}
q = queryExecute("SELECT id, pelanggan, butiran FROM pesanan_json ORDER BY id", {}, {datasource: dsn});
for (r in q) {
    writeOutput("id=" & r.id & " | " & r.pelanggan & " | " & r.butiran & chr(10));
}
</cfscript>

id=1 | Siti | {"ahli": true, "item": ["buku", "pen"], "bandar": "Shah Alam", "jumlah": 149.9}
id=2 | Ahmad | {"ahli": false, "item": ["papan kekunci", "tetikus"], "bandar": "Johor Bahru", "jumlah": 89.5}

MySQL menyusun semula kunci JSON mengikut panjangnya, jadi jangan bergantung pada susunan itu. Nilai boolean perlu dihantar sebagai JSON melalui CAST(:ahli AS JSON); tanpa itu MySQL menyimpannya sebagai nombor.

Operator -> dan ->>: satu memetik, satu tidak

-> memulangkan nilai JSON, jadi teks datang dengan tanda petikan. ->> memulangkan teks biasa. Perbezaan ini punca paling kerap bagi carian yang pulang kosong dan perbandingan yang terlepas baris.

<cfscript>
dsn = "kedai";
q = queryExecute(
    "SELECT id, butiran->'$.bandar' AS bjson, butiran->>'$.bandar' AS bteks
     FROM pesanan_json ORDER BY id", {}, {datasource: dsn});
for (r in q) {
    writeOutput("id=" & r.id & " | ->[" & r.bjson & "] | ->>[" & r.bteks & "]" & chr(10));
}
writeOutput("angka: " & queryExecute("SELECT COUNT(*) AS n FROM pesanan_json
    WHERE butiran->>'$.jumlah' > 100", {}, {datasource: dsn}).n & chr(10));
writeOutput("teks:  " & queryExecute("SELECT COUNT(*) AS n FROM pesanan_json
    WHERE butiran->>'$.jumlah' > '100'", {}, {datasource: dsn}).n & chr(10));
</cfscript>

id=1 | ->["Shah Alam"] | ->>[Shah Alam]
id=2 | ->["Johor Bahru"] | ->>[Johor Bahru]
angka: 1
teks:  2

Kedua-duanya masalah yang senyap. Membanding dengan angka 100 memberi satu baris yang betul, manakala '100' memberi dua baris kerana MySQL membanding teks dengan teks dan "89.5" lebih besar daripada "100" mengikut abjad. Jika nilai JSON anda kadang-kadang disimpan sebagai rentetan, CAST(... AS DECIMAL(10,2)) menjadikan perbandingan itu jelas. Bentuk parameter queryExecute yang sama dibincangkan dalam queryExecute dalam CFML.

Bila kunci JSON huruf besar dan carian pulang kosong

Laluan JSON dalam MySQL sensitif huruf. $.bandar dan $.BANDAR ialah dua kunci berbeza, dan yang salah memulangkan NULL tanpa ralat. Masalahnya, CFML sendiri boleh menukar kunci anda sebelum data sampai ke MySQL.

<cfscript>
a = {};
a["bandar"] = "Ipoh";
writeOutput("bracket: " & serializeJSON(a) & chr(10));

b = {"bandar": "Ipoh"};
writeOutput("petikan: " & serializeJSON(b) & chr(10));

c = {bandar: "Ipoh", anak: {kecil: 1}};
writeOutput("tanpa:   " & serializeJSON(c) & chr(10));
</cfscript>

bracket: {"bandar":"Ipoh"}
petikan: {"bandar":"Ipoh"}
tanpa:   {"ANAK":{"KECIL":1},"BANDAR":"Ipoh"}

Literal struct tanpa tanda petikan pada nama kunci menghasilkan kunci huruf besar, dan penukaran itu berlaku bersarang. Menulis "bandar" dengan petikan atau membina struct melalui a["bandar"] = ... mengekalkan huruf asal, manakala JSON_OBJECT('bandar', ...) menetapkan kunci melalui teks SQL. Itulah sebabnya contoh pertama memilih JSON_OBJECT untuk lajur yang akan ditapis kemudian. Perangkap serializeJSON yang lebih lengkap ada dalam serializeJSON dalam CFML.

Mengemas kini sebahagian dokumen

Anda boleh mengubah satu cabang tanpa menghantar semula seluruh dokumen. Setiap fungsi di bawah mengembalikan dokumen baharu, jadi ia mesti ditulis kembali dengan UPDATE.

<cfscript>
dsn = "kedai";
queryExecute("UPDATE pesanan_json SET butiran = JSON_SET(butiran, '$.bandar', 'Klang', '$.poskod', '41000') WHERE id = 1", {}, {datasource: dsn});
queryExecute("UPDATE pesanan_json SET butiran = JSON_ARRAY_APPEND(butiran, '$.item', 'pembaris') WHERE id = 1", {}, {datasource: dsn});
queryExecute("UPDATE pesanan_json SET butiran = JSON_REMOVE(butiran, '$.ahli') WHERE id = 1", {}, {datasource: dsn});
r = queryExecute("SELECT butiran FROM pesanan_json WHERE id = 1", {}, {datasource: dsn});
writeOutput(r.butiran & chr(10));
</cfscript>

{"item": ["buku", "pen", "pembaris"], "bandar": "Klang", "jumlah": 149.9, "poskod": "41000"}

Array: JSON_TABLE dan null yang menjadi baris kosong

JSON_TABLE memecahkan array menjadi baris di dalam MySQL, supaya penapis dan kiraan berlaku pada pangkalan data.

<cfscript>
dsn = "kedai";
queryExecute("INSERT INTO pesanan_json (pelanggan, butiran)
    VALUES ('Ujian Null', '{""item"": [""buku"", null], ""bandar"": ""Ipoh"", ""jumlah"": 10}')", {}, {datasource: dsn});
q = queryExecute("SELECT p.pelanggan, j.item FROM pesanan_json p,
    JSON_TABLE(p.butiran, '$.item[*]' COLUMNS (item VARCHAR(40) PATH '$')) AS j
    WHERE p.id = 3 ORDER BY j.item", {}, {datasource: dsn});
for (row in q) {
    writeOutput("[" & row.item & "]" & chr(10));
}
</cfscript>

[buku]
[]

Baris dengan kurungan kosong itu ialah null JSON di dalam array. Ia nilai yang sah dan ia menggelembungkan kiraan dalam laporan. Tapis dengan WHERE j.item IS NOT NULL AND j.item <> '', atau jangan simpan null di dalam array sebagai ganti nilai sebenar.

Index: lajur terjana, index berfungsi, index berbilang nilai

Carian pada lajur JSON tanpa index membaca setiap baris. Pada 20,000 baris, EXPLAIN ANALYZE menunjukkan imbasan penuh mengambil 18.3 ms untuk 4,000 baris sepadan. Lajur terjana yang diindeks menurunkan bacaan itu kepada 1.85 ms.

<cfscript>
dsn = "kedai";
queryExecute("DROP TABLE IF EXISTS pesanan_index", {}, {datasource: dsn});
queryExecute("CREATE TABLE pesanan_index (
    id INT AUTO_INCREMENT PRIMARY KEY,
    butiran JSON NOT NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4", {}, {datasource: dsn});
queryExecute("SET SESSION cte_max_recursion_depth = 30000", {}, {datasource: dsn});
queryExecute("INSERT INTO pesanan_index (butiran)
    WITH RECURSIVE s(n) AS (SELECT 1 UNION ALL SELECT n + 1 FROM s WHERE n < 20000)
    SELECT JSON_OBJECT('bandar', ELT(1 + (n MOD 5),
        'Shah Alam','Ipoh','Johor Bahru','Kuching','Kota Kinabalu')) FROM s", {}, {datasource: dsn});
queryExecute("ALTER TABLE pesanan_index
    ADD COLUMN bandar VARCHAR(40) GENERATED ALWAYS AS (butiran->>'$.bandar') STORED,
    ADD INDEX idx_bandar (bandar)", {}, {datasource: dsn});
ex = queryExecute("EXPLAIN SELECT COUNT(*) FROM pesanan_index WHERE butiran->>'$.bandar' = 'Ipoh'", {}, {datasource: dsn});
writeOutput("jenis=" & ex.type & " | kunci=" & ex.key & " | baris=" & ex.rows & chr(10));
</cfscript>

jenis=ref | kunci=idx_bandar | baris=4000

jenis berubah daripada ALL sebelum index kepada ref selepasnya, dan baris kunci ialah bukti yang perlu anda cari, bukan masa respons semata-mata. MySQL 8.4 juga memadankan ungkapan asal butiran->>'$.bandar' dengan index lajur terjana itu, jadi query lama tidak perlu ditulis semula.

Baris terakhir bukan kemenangan, dan itu penting disebut. Index berbilang nilai memang dipakai, tetapi 6,800 daripada 20,000 baris sepadan, lebih satu pertiga, dan pada nisbah itu membaca index tidak lebih murah daripada membaca jadual. Lajur terjana lebih pantas daripada index berfungsi kerana nilainya disimpan dan index itu menjadi covering index untuk kiraan. Harganya ruang: data 2.52 MB dan jumlah index 1.22 MB selepas ANALYZE TABLE.

Bila JSON sesuai dan bila jadual biasa lebih baik

Gunakan lajur JSON apabila bentuk data memang berbeza antara baris, apabila dokumen dibaca dan ditulis sebagai satu unit, atau apabila muatan datang daripada API luar yang berubah. Elakkannya apabila lajur itu ditapis, disusun atau dijumlahkan selalu, kerana setiap tapisan memerlukan lajur terjana atau index berfungsi. Jika anda menulis laluan $.nama lebih daripada sekali dalam kod aplikasi, medan itu patut keluar daripada JSON dan menjadi lajur biasa.

Untuk nilai wang, simpan dalam lajur DECIMAL biasa dan bukan di dalam JSON. Kerja pangkalan data yang lain ada dalam sambungan MySQL dalam Lucee dan transaksi dan tahap pengasingan, manakala carian teks di dalam dokumen lebih sesuai dengan carian full-text MySQL.

Ralat yang berulang

Baris kedua diuji terus: <cfquery> yang mengandungi > membalas ralat sintaks daripada MySQL, kerana entiti HTML itu sampai ke pangkalan data sebagai teks.

Soalan Lazim (FAQ)

Bolehkah saya simpan struct CFML terus ke lajur JSON? Tidak. queryExecute membalas key [value] doesn't exist apabila nilai parameter ialah struct, kerana setiap parameter perlu kunci value dan cfsqltype. Tukar struct kepada teks JSON dahulu, atau bina dokumen dengan JSON_OBJECT di dalam SQL.

Apa beza operator -> dan ->>? -> memulangkan nilai JSON, jadi teks datang dengan tanda petikan; ->> memulangkan teks tanpa petikan. Untuk perbandingan guna ->>, untuk menghantar semula sebahagian dokumen sebagai JSON guna ->.

Kenapa lajur JSON perlukan index tambahan? Lajur JSON tidak boleh diindeks secara umum. MySQL mengindeks ungkapan tertentu melalui lajur terjana atau index berfungsi, dan tanpa salah satu daripadanya setiap carian membaca seluruh jadual, iaitu 18.3 ms berbanding 1.85 ms pada 20,000 baris dalam rig ini.

Adakah null di dalam JSON sama dengan NULL SQL? Tidak. null JSON ialah nilai sah dan JSON_TYPE memulangkannya sebagai NULL, manakala NULL SQL bermakna tiada nilai. d->'$.x' IS NULL memulangkan 0 untuk null JSON dan 1 untuk lajur NULL.

Bolehkah saya cari nilai di dalam array? Boleh. 'buku' MEMBER OF (butiran->'$.item') berfungsi dan boleh memakai index berbilang nilai. Tetapi semak berapa banyak baris sepadan dahulu: 6,800 daripada 20,000 baris dalam ujian ini, dan bacaan melalui index mengambil 23.6 ms, lebih perlahan daripada imbasan penuh.

Sumber

• Fungsi carian JSON dan operator ->: dev.mysql.com/doc/refman/8.4/en/json-search-functions.html
• JSON_TABLE: dev.mysql.com/doc/refman/8.4/en/json-table-functions.html
• Index berbilang nilai dan lajur terjana: dev.mysql.com/doc/refman/8.4/en/create-index.html
• serializeJSON dalam Lucee: docs.lucee.org/reference/functions/serializejson.html