JSON kepada SQL: Pernyataan INSERT, Reka Bentuk Jadual, dan Perangkap Pemetaan Jenis

Kesimpulan utama: tentukan entiti perniagaan yang diwakili oleh satu baris sebelum mereka bentuk jadual, kemudian tulis VALUES berbilang baris yang eksplisit di dalam satu transaksi; selesaikan penamaan medan dan pemetaan jenis sebelum import dan bukannya menampal-nampal dalam SQL.

JSON menerangkan bentuk; SQL juga menerangkan kekangan. Apabila anda memindahkan yang pertama ke dalam yang kedua, setiap kekangan yang tidak anda tuliskan akan kembali kemudian sebagai data kotor.

1. Tentukan dahulu: satu jadual atau satu rekod bagi setiap baris JSON

Lihat lapisan paling luar data sebelum mereka bentuk apa-apa jadual. Salah keputusan ini dan setiap INSERT selepasnya perlu ditulis semula.

Hanya ada satu kriteria: entiti perniagaan apa yang diwakili oleh satu baris? Jika satu baris ialah pesanan, item baris mesti berada dalam jadualnya sendiri. Jika satu baris ialah pengguna, tag tergolong dalam jadual anak atau lajur JSON, bukan dalam rentetan yang digabungkan dengan koma.

2. Daripada tatasusunan kepada INSERT: templat terkecil yang boleh dijalankan

Bentuk paling selamat sentiasa menyenaraikan nama lajur secara eksplisit dan tidak sekali-kali bergantung pada susunan lajur:

复制CREATE TABLE users (
  id INTEGER PRIMARY KEY,
  name TEXT NOT NULL,
  email TEXT UNIQUE
);

INSERT INTO users (id, name, email) VALUES
  (1, 'Ada', 'ada@example.com'),
  (2, 'Linus', NULL);

Tiga perincian penting:

• Tulis setiap nama lajur. Apabila skema kemudian mendapat lajur baharu, tiada apa yang beralih ke medan yang salah.
• VALUES berbilang baris berfungsi dalam SQLite, PostgreSQL, dan MySQL, dan menjimatkan sejumlah besar perjalanan pergi balik berbanding gelung sisipan satu baris.
• NULL ialah kata kunci; jangan petiknya. Menulis 'NULL' menyimpan rentetan empat aksara.

Jika kunci JSON tidak sepadan dengan nama lajur (userId berbanding user_id), tambah lapisan pemetaan. Jangan meneka dengan COALESCE di dalam SQL. Membetulkan nama medan dalam lapisan data jauh lebih murah daripada menampal-nampal dalam pertanyaan.

3. Objek bersarang: jadual induk ditambah jadual anak

Pesanan ialah kes satu-ke-banyak klasik:

复制INSERT INTO orders (id, customer_name, total_cents) VALUES
  (1001, 'Ada', 12900);

INSERT INTO order_items (order_id, sku, quantity) VALUES
  (1001, 'A-1', 2),
  (1001, 'B-7', 1);

Ini sepadan dengan JSON {"id":1001,"customer":"Ada","total":129.00,"items":[{"sku":"A-1","quantity":2},{"sku":"B-7","quantity":1}]}.

Perkara penting:

• Panjang tatasusunan items menentukan bilangan baris anak; jadual induk mendapat tepat satu baris.
• Simpan wang sebagai sen integer (total_cents), jangan sekali-kali sebagai FLOAT.
• Berikan jadual anak kunci asing order_id dan commit kedua-dua sisipan dalam satu transaksi, jika tidak induk boleh wujud manakala itemnya hilang.
• Jika data itu baca-sahaja dan tiada siapa menapis mengikut item baris, lajur JSON boleh diterima (PostgreSQL jsonb, MySQL JSON, atau SQLite TEXT dengan fungsi json1), tetapi terima kosnya: tiada pengindeksan dan pertanyaan yang janggal.

Ratakan objek bersarang tetap seperti user.address menjadi lajur (address_city, address_zip). Pecahkan kepada jadual hanya apabila sarang boleh terus berkembang; memecahkan bentuk tetap hanya menambah JOIN.

4. NULL berbanding medan tiada: SQL tiada "kunci tidak hadir"

JSON membenarkan kunci tidak hadir sepenuhnya. Lajur SQL adalah tetap. Selepas import kedua-dua kes kelihatan serupa — NULL — walaupun ia boleh bermakna perkara berbeza:

Dua kesilapan lazim:

1. Menggunakan rentetan kosong dan bukan NULL. Kemudian, WHERE email IS NULL terlepas baris tersebut, dan metrik "pengguna tanpa e-mel" anda mengira kurang secara senyap.
2. Menguji dengan col = NULL. Dalam logik tiga nilai SQL, NULL = NULL dinilai sebagai NULL, bukan TRUE. Anda mesti menulis IS NULL:

复制SELECT COUNT(*) FROM users WHERE email IS NULL;

Jika anda perlu mengekalkan sama ada kunci itu wujud sejak awal, tambah lajur penanda boolean dan jangan simpulkan daripada NULL. Kontrak API juga harus menyatakan perkara ini; lihat Reka bentuk API JSON dan penamaan medan.

5. Petikan dan escaping: jangan sekali-kali menggabungkan rentetan SQL

Rentetan JSON boleh mengandungi petikan tunggal, petikan berganda, garis miring terbalik, baris baharu, dan juga \u0000. Dengan rentetan yang dibina dengan tangan, satu escape yang terlepas mengalihkan seluruh pernyataan, dan dalam kes terburuk menjadi suntikan SQL.

Dalam SQL standard, petikan tunggal di-escape dengan menggandakannya:

复制INSERT INTO notes (id, content) VALUES (1, 'It''s a ''quoted'' value');

Kod pengeluaran harus menggunakan pengikatan parameter dan biarkan pemacu mengendalikan escaping serta jenis:

复制// PostgreSQL uses $1/$2, MySQL uses ?; this example uses node-postgres
await client.query('INSERT INTO users (id, name) VALUES ($1, $2)', [row.id, row.name]);

Escape dengan tangan hanya apabila anda mesti mengeksport fail .sql, dan pada masa itu sekurang-kurangnya:

• ganti ' dengan '';
• jika baris baharu dikekalkan, sahkan pangkalan data sasaran menerima literal rentetan berbilang baris;
• buang \u0000, kerana kebanyakan pangkalan data menolak NUL di dalam rentetan;
• MySQL melayan garis miring terbalik sebagai aksara escape secara lalai, jadi \ mesti menjadi \\, atau beralih kepada mod standard.

Tanda petikan berganda daripada JSON tidak memerlukan escaping di dalam rentetan SQL berpetikan tunggal. Menambah lapisan escaping tambahan ialah cara garis miring terbalik yang terpesong berakhir dalam data anda.

6. Pemetaan jenis: di mana enam nilai JSON mendarat dalam SQL

JSON hanya mempunyai rentetan, nombor, boolean, null, objek, dan tatasusunan. SQL mempunyai sistem jenis yang jauh lebih halus. Kesilapan biasanya berlaku dalam ketepatan dan semantik dan bukan sintaks:

Boolean menyembunyikan bug paling halus: false JSON menjadi 0 dalam MySQL TINYINT(1), perjalanan pergi balik kelihatan baik, dan tiada siapa perasan. Tetapi sebaik sahaja sesuatu kod melakukan if (value) pada rentetan "false", ia dinilai sebagai benar. Skrip penukaran harus memanggil Boolean(value) secara eksplisit.

JSONB PostgreSQL menghurai dan menyahduplikasi kunci, mengekalkan kejadian terakhir, yang sesuai untuk pertanyaan; JSON mengekalkan teks asal dan susunan kunci. SQLite JSON hanyalah TEXT tambah satu set fungsi, tanpa kekangan jenis sebenar. Sebelum memilih, tanya sama ada jadual ini memerlukan penapisan pada medan di dalam JSON.

7. Sisipan pukal dan transaksi: kelompokkan, commit, jadikan boleh dijalankan semula

Apabila volum bertambah, tiga perkara menentukan sama ada import kekal terkawal:

1. Kelompokkan. Gelung N pernyataan INSERT satu baris berharga N perjalanan pergi balik rangkaian. Gabungkan 500 hingga 1000 baris menjadi satu VALUES berbilang baris, atau gunakan pemuat pukal pangkalan data itu sendiri (COPY PostgreSQL, LOAD DATA MySQL, sisipan berbilang baris di dalam transaksi SQLite).
2. Gunakan transaksi. Semua atau tiada, supaya jadual yang separuh dimuatkan tidak pernah bertahan selepas kesilapan:

复制BEGIN;
INSERT INTO users (id, name) VALUES (1, 'Ada'), (2, 'Linus');
INSERT INTO order_items (order_id, sku, quantity) VALUES (1, 'A-1', 2);
COMMIT;

3. Jadikan ia idempoten. Skrip import sentiasa dijalankan semula. Gunakan upsert dan bukan padam-kemudian-sisip:

ON CONFLICT memerlukan kekangan unik atau kunci utama, jika tidak ia tidak pernah tercetus. Apabila tiada kunci semula jadi, bina indeks unik pada kunci perniagaan seperti email. Apabila tiada kunci sama sekali, tambah id yang stabil di sebelah JSON — jangan sekali-kali bergantung pada id auto-increment untuk idempotensi, kerana jalankan semula akan menyisipkan pendua.

8. Perbezaan dialek secara ringkas

Untuk memastikan satu fail SQL berfungsi merentas ketiga-tiganya, sekurang-kurangnya elakkan ON CONFLICT, tanda petikan belakang, dan SERIAL. Bagi projek merentas pangkalan data, jana skema dan pernyataan upsert secara berasingan; jangan jangkakan satu skrip sesuai untuk semua.

9. Tiga semakan selepas import

Sebelum memadam fail sumber, sahkan tiga perkara:

1. Bilangan baris. SELECT COUNT(*) FROM users; harus sama dengan panjang tatasusunan JSON. Satu baris yang hilang biasanya bermaksud rekod terakhir dipotong.
2. Lajur dengan kadar NULL tertinggi. Itu menunjuk kepada nama kunci yang tidak konsisten atau sarang yang tidak dikembangkan. Kembali dan semak sama ada sumber menggunakan kedua-dua userId dan user_id.
3. Wang dan pengecam panjang. Ambil nilai maksimum dan minimum dan sahkan tiada titik terapung atau penukaran integer memotongnya.

Jika anda perlu memahami bentuk jadual dahulu, gunakan pendekatan perataan dalam Penukaran JSON, CSV, dan data jadual. Jika audiens ialah rakan sekerja yang hanya mahu membaca data, empat cara menukar JSON kepada Excel lebih sedikit kerja daripada menyediakan pangkalan data.

Mengapa nombor kehilangan ketepatan selepas mengimport JSON ke dalam SQL?

Integer melebihi 2^53 sudah kehilangan ketepatan semasa JSON.parse JavaScript, dan menulisnya ke dalam BIGINT tidak dapat memulihkannya. ID seperti itu harus rentetan dalam JSON dan ditukar kepada BIGINT atau TEXT semasa import.

Apa yang harus saya gunakan sebagai kunci utama apabila JSON tiada id?

Biarkan pangkalan data menjana satu, atau cipta UUID yang stabil sebelum mengimport. Jangan sekali-kali menggunakan indeks tatasusunan sebagai id: jalankan semula menyusun semula data dan menunjuk baris yang salah kepada rekod yang salah. Indeks unik pada kunci perniagaan (e-mel, nombor pesanan) juga membolehkan upsert.

Apa yang perlu saya lakukan apabila INSERT pukal menemui konflik kunci unik?

Ganti INSERT biasa dengan upsert yang sepadan untuk dialek anda, dan sahkan lajur yang bercanggah mempunyai kekangan unik. Memadam dahulu dan menyisip kemudian juga berkesan, tetapi ia mesti berlaku di dalam satu transaksi dan anda menerima tetingkap singkat tanpa baris.

Adakah tatasusunan bersarang sentiasa memerlukan jadual anak?

Tidak. Untuk paparan baca-sahaja tanpa pertanyaan mengikut sub-item, lajur JSON lebih mudah. Sebaik sahaja anda perlu menapis atau mengagregat mengikut tag atau SKU, jadual anak dengan indeks ialah pilihan yang mampan. Faktor penentu ialah cara anda membuat pertanyaan, bukan cara data itu dibentuk.