SQL JSON INSERT 插入資料

在學會如何查詢與修改 JSON 之前，最基礎的步驟就是將 JSON 資料正確地存入資料庫。本章將深入探討如何透過多種方式執行 JSON 資料的插入，並說明其中的細節與常見陷阱。

直接插入 JSON 字串

最簡單的方式是將完全符合 JSON 規範的字串直接存入 JSON 型態的欄位。

基本範例

假設我們有一張 users 資料表，其中的 profile 欄位型態定義為 JSON：

INSERT INTO users (name, profile) 
VALUES ('Mike', '{"city": "Taipei", "age": 25, "tags": ["SQL", "Web"]}');

當你執行這條語句時，資料庫會執行以下操作：

  1. 語法解析：檢查字串是否符合標準 JSON 格式。
  2. 型態轉換：將字串轉化為內部二進位格式儲存。
  3. 自動排序：在某些資料庫如 MySQL 中，存入的物件鍵值會依字母順序重新排序以便優化查詢。
格式錯誤會導致失敗： 如果你傳入了錯誤的 JSON 格式（例如：{'city': 'Taipei'}，注意 JSON 標準規定必須使用雙引號），資料庫會拋出錯誤並拒絕插入。這是 JSON 型態與 TEXT 型態最大的區別：它具備強大的輸入驗證。

使用建構子函數插入 (Constructor Functions)

手寫長字串容易出錯，尤其是當 JSON 中包含動態變數或需要處理大量轉義字元時。使用 SQL 內建函數能讓程式碼更具可讀性且更安全。

使用 JSON_OBJECT()

這是一個最推薦的動態組裝方式。它會自動處理字串轉義，確保產出的 JSON 一定合法。

-- 混合使用常數與資料變數
INSERT INTO users (name, profile) 
VALUES (
    'Jane', 
    JSON_OBJECT(
        'city', 'Kaohsiung', 
        'age', 30, 
        'is_premium', TRUE,
        'created_at', NOW()
    )
);

使用 JSON_ARRAY()

如果你的欄位是用來存一組清單（例如文章標籤、產品顏色），使用 JSON_ARRAY 非常方便。

INSERT INTO products (title, colors) 
VALUES ('經典連帽衫', JSON_ARRAY('Red', 'Blue', 'Green'));
-- 存入結果: ["Red", "Blue", "Green"]

處理轉義字元 (Handling Escaping)

當你要插入的字串內容包含單引號或特殊符號時，直接拼湊字串會非常痛苦：

  • 傳統方式：需要手動處理 I''m a developer。
  • 函數方式：JSON_OBJECT('bio', 'I''m a developer')。建構子函數會自動幫你封裝好，你只需要注意 SQL 層級的單引號轉義即可。

從現有資料表轉存 JSON

有時候我們需要將舊有的關係型資料轉換成 JSON 整合到新系統中。你可以結合 SELECT 語句：

-- 將舊表的欄位直接打包成 JSON 存入新表
INSERT INTO archive_table (id, old_data_json)
SELECT 
    id, 
    JSON_OBJECT('old_v1_email', email, 'old_v1_phone', phone)
FROM legacy_users;

常見問題與最佳實踐

JSON 欄位 vs TEXT 欄位

雖然你也可以把 JSON 存進 TEXT 欄位，但我們強烈建議使用原生 JSON 型態：

  • 空間優化：二進位儲存通常更省空間。
  • 查詢速度：原生型態支援快速定位 Key，不需要掃描整個字串。
  • 資料完整性：內建格式驗證，防止「髒資料」進入資料庫。

效能小預知

執行 INSERT 時，校驗 JSON 格式會稍微增加一點 CPU 負擔，但在後續的 SELECT 與 UPDATE 階段，這點代碼轉換的代價將會換來倍數增長的讀取效能。

總結

  1. 手寫字串：適用於靜態且結構簡單的資料，但請務必檢查雙引號格式。
  2. 動態組裝：優先選用 JSON_OBJECT() 與 JSON_ARRAY()。
  3. 批次處理：善用 JSON_ARRAYAGG 或結合 SELECT 進行大量資料轉換。