JSON 轉 SQL：INSERT、建表與型別對映的坑

結論：先按「一行代表什麼業務實體」決定單表還是主表加明細表，再顯式列出欄位名寫多行 VALUES 並放進同一交易提交；欄位命名與型別對映在匯入前定好，別留到 SQL 裡補救。

JSON 只描述形狀，SQL 還要描述約束。把前者搬進後者時，所有沒被寫下來的約束，最後都會以髒資料的形式回來找你。

一、先判斷：一行 JSON 對應一張表還是一行記錄

拿到資料先看最外層，再決定表結構。這個判斷做錯，後面所有 INSERT 都要重寫。

判斷標準只有一句：一行代表什麼業務實體。如果一行是訂單，訂單明細就必須單獨成表；如果一行是使用者，標籤適合放明細表或 JSON 欄位，不適合拼成逗號字串。

二、陣列轉 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);

三個細節：

• 欄位名寫全，表結構後續加欄位時不會因為位置錯位而寫錯欄位；
• 多行 VALUES 在 SQLite、PostgreSQL、MySQL 都支援，比迴圈單條插入少很多往返；
• NULL 是關鍵字，不要加引號。寫成 'NULL' 會存進一個四個字元的字串。

如果 JSON 裡的鍵名和欄位名不一致（userId 對 user_id），先做一層對映，別在 SQL 裡用 COALESCE 猜。欄位命名問題在資料層解決，比在 SQL 裡補救便宜。

三、巢狀物件拆表：主表 + 明細表

訂單是最典型的一對多：

复制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}]}。

要點：

• items 陣列長度決定明細條數，主表只寫一行；
• 金額用整數分儲存（total_cents），別用 FLOAT；
• 明細表加外鍵 order_id，並在交易裡一起提交，避免主表有、明細缺失；
• 如果業務只讀展示、不按明細查詢，可以保留 JSON 欄位（PostgreSQL jsonb、MySQL JSON、SQLite TEXT + json1 函式），但要接受「查不了、索引難」的代價。

巢狀物件本身（如 user.address）優先展開成扁平欄位（address_city、address_zip），只有層級會繼續增長時才拆表。固定層級拆表只會增加 JOIN。

四、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 設計與欄位命名。

五、字串引號轉義：別拼 SQL 字串

JSON 字串裡可能出現單引號、雙引號、反斜線、換行，甚至 \u0000。手工拼接時，任何一處漏轉義都會讓語句錯位，嚴重時構成 SQL 注入。

SQL 標準裡單引號轉義成兩個單引號：

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

但生產程式碼應該用參數化綁定，讓驅動處理轉義與型別：

复制// PostgreSQL 用 $1/$2，MySQL 用 ?；這裡以 node-postgres 為例
await client.query('INSERT INTO users (id, name) VALUES ($1, $2)', [row.id, row.name]);

只有必須匯出 .sql 檔案時才手工轉義，並且至少做到：

• 把 ' 替換成 ''；
• 保留換行時確認目標資料庫接受多行字串字面量；
• 去掉 \u0000，多數資料庫不接受字串裡的 NUL；
• MySQL 預設把反斜線當轉義符，\ 要寫成 \\，或者改用標準模式。

另外，JSON 裡的雙引號在 SQL 單引號字串裡不需要轉義，不要多做一層導致內容裡冒出反斜線。

六、型別對映：JSON 的六種值落到 SQL 的哪些型別

JSON 只有 string、number、boolean、null、object、array 六種值，SQL 的型別卻細得多。對映錯的地方通常不在語法，而在精度和語義：

布林值的坑最隱蔽：JSON false 匯入 MySQL TINYINT(1) 是 0，看起來正常；匯出時又被讀成 false，來回一趟沒問題。但一旦中間有人用 if (value) 判斷字串 "false"，就會得到真值。轉換腳本裡要顯式做 Boolean(value)。

PostgreSQL 的 JSONB 會解析並去重鍵、保留最後出現的值，適合查詢；JSON 保留原始文字與鍵順序。SQLite 的 JSON 只是 TEXT 加一組函式，沒有真正的型別約束。選之前先問：這張表需不需要按 JSON 內部欄位過濾。

七、批量插入與交易：分批、顯式提交、可重跑

資料量上來後，三件事決定匯入是否可控：

1. 分批。單條 INSERT 迴圈 N 次會產生 N 次網路往返；把 500 到 1000 行合成一條多行 VALUES，或使用資料庫自己的批量匯入（PostgreSQL COPY、MySQL LOAD DATA、SQLite 交易內多行插入）。
2. 交易。要麼全成，要麼全敗，別讓半張表的資料留在庫裡：

复制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. 冪等。匯入腳本重跑是常態，用 upsert 替代「先刪後插」：

ON CONFLICT 需要唯一約束或主鍵，否則不會觸發。沒有自然主鍵時，用業務唯一鍵（例如 email）建唯一索引；兩者都沒有時，先在 JSON 側補一個穩定 id，別用自增 id 做冪等判斷——重跑會插入重複行。

八、方言差異速查

同一條 SQL 想在三家通用，至少要避開 ON CONFLICT、反引號和 SERIAL。跨庫場景下把建表與 upsert 分別生成，不要指望一份腳本通吃。

九、匯入後的三處抽檢

轉完先別刪原始檔案，做三次核對：

1. 行數：SELECT COUNT(*) FROM users; 應等於 JSON 陣列長度，少一條通常是末尾記錄被截斷；
2. NULL 率最高的欄位：說明鍵名不一致或巢狀沒展開，回去看原始資料是不是同時存在 userId 與 user_id；
3. 金額與長編號：挑最大值與最小值各一條，確認沒有被浮點或整型截斷。

需要先看清表格形態時，可以先用 JSON 與 CSV、表格資料的互轉 的思路扁平化；如果只是給業務同事看，JSON 轉 Excel 的四種辦法 比直接建庫更省事。

為什麼 JSON 裡的數字匯入 SQL 後精度變了？

超過 2^53 的整數在 JavaScript 的 JSON.parse 階段就已經丟精度，後面寫進 BIGINT 也無法恢復。這類 id 在 JSON 裡就應該是字串，匯入時再轉成 BIGINT 或 TEXT。

JSON 裡沒有 id，主鍵怎麼填？

讓資料庫自增，或在匯入前生成穩定 UUID。不要用陣列下標做 id，重跑一次順序變了就會錯位。用業務唯一鍵（郵箱、訂單號）建唯一索引，才能配合 upsert。

批量 INSERT 報唯一鍵衝突怎麼辦？

把普通 INSERT 換成對應方言的 upsert，並確認衝突欄位上有唯一約束。另一個辦法是先 DELETE 再插入，但必須放在同一交易裡，且要接受短暫的空窗。

巢狀陣列一定要建子表嗎？

不一定。只讀展示、不按子項查詢時，存成 JSON 欄位更省事；一旦要按標籤、SKU 過濾或統計，子表加索引才是可持續的做法。判斷標準是查詢方式，不是資料形狀。