JSON เป็น SQL: คำสั่ง INSERT การออกแบบตาราง และกับดักการแมปชนิดข้อมูล

สรุปสั้น: ตัดสินก่อนว่าหนึ่งแถวแทนเอนทิตีธุรกิจอะไร แล้วจึงออกแบบตาราง จากนั้นเขียน multi-row VALUES อย่างชัดเจนภายใน transaction เดียว; ตกลงเรื่องการตั้งชื่อฟิลด์และการแมปชนิดข้อมูลก่อนนำเข้า อย่าแก้ทีหลังด้วย SQL

JSON บอกเล่ารูปร่าง; SQL บอกเล่า constraint ด้วย เมื่อคุณย้ายอดีตไปสู่อย่างหลัง ทุก constraint ที่คุณไม่ได้เขียนไว้จะกลับมาเป็นข้อมูลสกปรกในภายหลัง

1. ตัดสินก่อน: หนึ่งตารางหรือหนึ่งเรกคอร์ดต่อหนึ่งแถว JSON

ดูชั้นนอกสุดของข้อมูลก่อนออกแบบตารางใด ๆ ตัดสินข้อนี้ผิดแล้วทุก INSERT หลังจากนั้นต้องเขียนใหม่

มีเกณฑ์เดียว: หนึ่งแถวแทนเอนทิตีธุรกิจอะไร? ถ้าหนึ่งแถวคือออเดอร์ รายการสินค้าต้องอยู่ในตารางของตัวเอง ถ้าหนึ่งแถวคือผู้ใช้ แท็กต้องอยู่ในตารางลูกหรือคอลัมน์ JSON ไม่ใช่สตริงที่เชื่อมด้วยเครื่องหมายจุลภาค

2. จาก array สู่ INSERT: เทมเพลตที่เล็กที่สุดที่รันได้

รูปแบบที่ปลอดภัยที่สุด ระบุชื่อคอลัมน์อย่างชัดเจนเสมอ และไม่พึ่งลำดับคอลัมน์:

复制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);

รายละเอียดสำคัญสามข้อ:

• เขียนชื่อคอลัมน์ทุกตัว เมื่อ schema ภายหลังเพิ่มคอลัมน์ จะไม่มีอะไรเลื่อนไปผิดฟิลด์
• VALUES หลายแถวใช้ได้ใน SQLite, PostgreSQL และ MySQL และประหยัดการไป-กลับได้มากเมื่อเทียบกับลูป insert ทีละแถว
• NULL เป็นคีย์เวิร์ด ห้ามใส่เครื่องหมายคำพูด การเขียน 'NULL' จะเก็บสตริงสี่ตัวอักษร

ถ้าคีย์ JSON ไม่ตรงกับชื่อคอลัมน์ (userId กับ user_id) ให้เพิ่มชั้นการแมป อย่าเดาด้วย COALESCE ใน SQL การแก้ชื่อฟิลด์ในชั้นข้อมูลถูกกว่าการแก้ในคิวรีมาก

3. Object ที่ซ้อน: ตารางหลักพร้อมตารางลูก

ออเดอร์คือกรณีหนึ่งต่อหลายแบบคลาสสิก:

复制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);

สิ่งนี้ตรงกับ JSON {"id":1001,"customer":"Ada","total":129.00,"items":[{"sku":"A-1","quantity":2},{"sku":"B-7","quantity":1}]}

ประเด็นสำคัญ:

• ความยาวของ array items กำหนดจำนวนแถวลูก; ตารางหลักได้หนึ่งแถวพอดี
• เก็บเงินเป็นจำนวนเต็มหน่วยย่อย (total_cents) ห้ามใช้ FLOAT
• ให้ตารางลูกมี foreign key order_id และคอมมิต insert ทั้งสองใน transaction เดียว ไม่อย่างนั้นตารางหลักอาจมีอยู่ขณะที่รายการของมันหายไป
• ถ้าข้อมูลเป็นแบบอ่านอย่างเดียวและไม่มีใครกรองตามรายการสินค้า คอลัมน์ JSON ก็ยอมรับได้ (jsonb ของ PostgreSQL, JSON ของ MySQL หรือ TEXT ของ SQLite ร่วมกับฟังก์ชัน json1) แต่ต้องยอมรับต้นทุน: ทำ index ไม่ได้และคิวรีลำบาก

ให้แบน object ที่ซ้อนแบบคงที่อย่าง user.address เป็นคอลัมน์ (address_city, address_zip) แยกเป็นตารางเฉพาะเมื่อการซ้อนยังเติบโตได้ การแยกโครงสร้างคงที่แค่เพิ่ม JOIN

4. NULL กับฟิลด์ที่หายไป: SQL ไม่มี "คีย์ที่ไม่มีอยู่"

JSON ปล่อยให้คีย์หายไปทั้งตัวได้ คอลัมน์ SQL ตายตัว หลังนำเข้าทั้งสองกรณีดูเหมือนกัน — NULL — แม้จะหมายถึงต่างกันได้:

ความผิดพลาดที่พบบ่อยสองข้อ:

1. ใช้สตริงว่างแทน NULL ภายหลัง WHERE email IS NULL จะพลาดแถวเหล่านั้น และตัวชี้วัด "ผู้ใช้ที่ไม่มีอีเมล" ของคุณจะนับขาดอย่างเงียบ ๆ
2. ทดสอบด้วย col = NULL ในตรรกะสามค่าของ SQL NULL = NULL ให้ผลเป็น NULL ไม่ใช่ TRUE คุณต้องเขียน IS NULL:

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

ถ้าต้องการรักษาว่าคีย์มีอยู่หรือไม่ ให้เพิ่มคอลัมน์เครื่องหมายบูลีนแทนการอนุมานจาก NULL สัญญา API ก็ควรระบุเรื่องนี้ด้วย; ดู การออกแบบ JSON API และการตั้งชื่อฟิลด์

5. การใส่เครื่องหมายคำพูดและการ escape: ห้ามต่อสตริง SQL

สตริง JSON มีเครื่องหมายคำพูดเดี่ยว เครื่องหมายคำพูดคู่ แบ็กสแลช การขึ้นบรรทัดใหม่ และแม้แต่ \u0000 ได้ ด้วยสตริงที่ประกอบมือ การ escape ที่พลาดครั้งเดียวจะเลื่อนส่วนที่เหลือของคำสั่ง และในกรณีเลวร้ายที่สุดกลายเป็น SQL injection

ใน SQL มาตรฐาน เครื่องหมายคำพูดเดี่ยวถูก escape ด้วยการเขียนซ้ำ:

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

โค้ดโปรดักชันควรใช้ parameter binding และให้ไดรเวอร์จัดการการ escape และชนิดข้อมูล:

复制// 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 ด้วยมือเฉพาะเมื่อต้องส่งออกไฟล์ .sql และอย่างน้อยต้อง:

• แทน ' ด้วย '';
• ถ้ารักษาการขึ้นบรรทัดใหม่ ให้ยืนยันว่าฐานข้อมูลปลายทางรับสตริงหลายบรรทัดได้;
• ตัด \u0000 ออก เพราะฐานข้อมูลส่วนใหญ่ปฏิเสธ NUL ภายในสตริง;
• MySQL ถือแบ็กสแลชเป็นอักขระ escape โดยค่าเริ่มต้น \ จึงต้องกลายเป็น \\ หรือเปลี่ยนไปใช้โหมดมาตรฐาน

เครื่องหมายคำพูดคู่จาก JSON ไม่ต้อง escape ภายในสตริงที่ครอบด้วยเครื่องหมายคำพูดเดี่ยวของ SQL การเพิ่มชั้นการ escape เกินมาคือหนทางที่แบ็กสแลชแปลกปลอมไปโผล่ในข้อมูลของคุณ

6. การแมปชนิดข้อมูล: ค่าหกประเภทของ JSON ลงเอยที่ไหนใน SQL

JSON มีเพียงสตริง ตัวเลข บูลีน null object และ array SQL มีระบบชนิดข้อมูลละเอียดกว่ามาก ความผิดพลาดมักเกิดที่ความแม่นยำและความหมายมากกว่าไวยากรณ์:

บูลีนซ่อนบั๊กที่ละเอียดอ่อนที่สุด: false ใน JSON กลายเป็น 0 ใน TINYINT(1) ของ MySQL การไป-กลับดูปกติและไม่มีใครสังเกต แต่เมื่อโค้ดใดทำ if (value) กับสตริง "false" มันจะให้ผลเป็นจริง สคริปต์แปลงจึงควรเรียก Boolean(value) อย่างชัดเจน

JSONB ของ PostgreSQL แยกวิเคราะห์และตัดคีย์ซ้ำโดยเก็บตัวสุดท้าย เหมาะกับการสอบถาม; JSON รักษาข้อความเดิมและลำดับคีย์ JSON ของ SQLite เป็นเพียง TEXT บวกฟังก์ชันชุดหนึ่ง ไม่มี constraint ชนิดจริง ก่อนเลือก ให้ถามว่าตารางนี้ต้องกรองตามฟิลด์ภายใน JSON หรือไม่

7. การ insert แบบกลุ่มและ transaction: แบตช์ คอมมิต และทำให้รันซ้ำได้

เมื่อปริมาณเพิ่มขึ้น สามสิ่งตัดสินว่าการนำเข้ายังควบคุมได้หรือไม่:

1. แบตช์ ลูป INSERT ทีละแถว N ครั้งเสียการไป-กลับผ่านเครือข่าย N ครั้ง รวม 500 ถึง 1000 แถวเป็น VALUES หลายแถวชุดเดียว หรือใช้ bulk loader ของฐานข้อมูลเอง (COPY ของ PostgreSQL, LOAD DATA ของ MySQL, multi-row insert ภายใน transaction ของ SQLite)
2. ใช้ transaction ทั้งหมดหรือไม่เลย เพื่อให้ตารางที่โหลดค้างครึ่งทางไม่รอดจาก error:

复制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. ทำให้ idempotent สคริปต์นำเข้าถูกรันซ้ำตลอด ให้ใช้ upsert แทนการลบแล้ว insert:

ON CONFLICT ต้องมี unique constraint หรือ primary key ไม่อย่างนั้นจะไม่ทำงานเลย เมื่อไม่มีคีย์ธรรมชาติ ให้สร้าง unique index บนคีย์ธุรกิจอย่าง email เมื่อไม่มีคีย์เลย ให้เพิ่ม id ที่เสถียรฝั่ง JSON — ห้ามพึ่ง id แบบ auto-increment เพื่อความ idempotent เพราะการรันซ้ำจะ insert ข้อมูลซ้ำ

8. ความต่างระหว่าง dialect ในพริบตา

ถ้าต้องให้ไฟล์ SQL ไฟล์เดียวใช้ได้ทั้งสามตัว อย่างน้อยให้เลี่ยง ON CONFLICT, แบ็กทิก และ SERIAL สำหรับโปรเจกต์ข้ามฐานข้อมูล ให้สร้าง schema และคำสั่ง upsert แยกกัน อย่าหวังว่าสคริปต์เดียวจะใช้ได้กับทุกตัว

9. การสุ่มตรวจสามอย่างหลังนำเข้า

ก่อนลบไฟล์ต้นฉบับ ให้ยืนยันสามสิ่ง:

1. จำนวนแถว SELECT COUNT(*) FROM users; ควรเท่ากับความยาวของ array ใน JSON แถวที่หายไปหนึ่งแถวมักหมายถึงเรกคอร์ดสุดท้ายถูกตัดทอน
2. คอลัมน์ที่มีอัตรา NULL สูงสุด บ่งชี้ชื่อคีย์ไม่สม่ำเสมอหรือการซ้อนที่ไม่ได้ขยาย กลับไปดูว่าต้นฉบับใช้ทั้ง userId และ user_id หรือไม่
3. เงินและตัวระบุยาว เลือกค่าสูงสุดและต่ำสุดมายืนยันว่าทั้งการแปลงเป็นเลขทศนิยมลอยตัวและจำนวนเต็มไม่ได้ตัดทอนค่าเหล่านี้

ถ้าต้องเข้าใจรูปร่างแบบตารางก่อน ให้ใช้วิธีแบนใน การแปลง JSON, CSV และข้อมูลแบบตาราง ถ้าผู้ชมเป็นเพื่อนร่วมงานที่แค่อยากอ่านข้อมูล สี่วิธีแปลง JSON เป็น Excel ใช้แรงน้อยกว่าการตั้งฐานข้อมูล

ทำไมตัวเลขสูญเสียความแม่นยำหลังนำเข้า JSON ลง SQL

จำนวนเต็มที่เกิน 2^53 สูญเสียความแม่นยำไปแล้วระหว่าง JSON.parse ของ JavaScript และการเขียนลง BIGINT ก็กู้คืนไม่ได้ id แบบนี้ควรเป็นสตริงใน JSON และแปลงเป็น BIGINT หรือ TEXT ตอนนำเข้า

ควรใช้ primary key อะไรเมื่อ JSON ไม่มี id

ให้ฐานข้อมูลสร้างขึ้นมา หรือสร้าง UUID ที่เสถียรก่อนนำเข้า ห้ามใช้ดัชนีของ array เป็น id: การรันซ้ำจะจัดลำดับข้อมูลใหม่และชี้แถวผิดไปยังเรกคอร์ดผิด unique index บนคีย์ธุรกิจ (อีเมล เลขคำสั่งซื้อ) ยังช่วยให้ทำ upsert ได้ด้วย

ต้องทำอย่างไรเมื่อ INSERT แบบกลุ่มชน unique key

เปลี่ยน INSERT ธรรมดาเป็น upsert ที่ตรงกับ dialect ของคุณ และยืนยันว่าคอลัมน์ที่ชนกันมี unique constraint การลบก่อนแล้วค่อย insert ก็ใช้ได้ แต่ต้องอยู่ใน transaction เดียวและต้องยอมรับช่วงเวลาสั้น ๆ ที่ไม่มีแถว

array ที่ซ้อนต้องมีตารางลูกเสมอหรือไม่

ไม่จำเป็น สำหรับการแสดงแบบอ่านอย่างเดียวที่ไม่ต้องสอบถามตามรายการย่อย คอลัมน์ JSON ง่ายกว่า ทันทีที่ต้องกรองหรือรวมข้อมูลตามแท็กหรือ SKU ตารางลูกที่มี index คือตัวเลือกที่ยั่งยืนกว่า ปัจจัยตัดสินคือวิธีที่คุณสอบถาม ไม่ใช่รูปร่างของข้อมูล