SQLite 進階：條件插入或替換——僅當新值大於舊值時更新

業務場景

在數據統計類應用中，常需維護「最高分」「最大值」「最新時間戳」等單調遞增指標。需求：插入新記錄時，若主鍵不存在則插入；若存在則僅當新值大於舊值時更新，否則保持原值不變。

表結構

CREATE TABLE IF NOT EXISTS SearchTable (
    Owner INTEGER PRIMARY KEY,
    Generations INTEGER DEFAULT 0
);

錯誤嘗試分析

開發者嘗試使用 INSERT OR REPLACE 搭配 MAX() 子查詢：

-- 錯誤：子查詢在 REPLACE 觸發前執行，此時新行尚未插入，導致邏輯錯誤
INSERT OR REPLACE INTO SearchTable(Owner, Generations) 
VALUES (?, MAX((SELECT Generations FROM SearchTable WHERE Owner = ?), ?));

此語句會導致數據異常甚至插入 NULL 值。

正確解決方案：INSERT ... SELECT 配合 COALESCE

-- 正確寫法：使用 SELECT 子句構建插入值，COALESCE 處理 NULL 情況
INSERT OR REPLACE INTO SearchTable(Owner, Generations)
SELECT 5, MAX(3, COALESCE(Generations, 0)) 
FROM SearchTable 
WHERE Owner = 5;

語句解析

• INSERT OR REPLACE：主鍵衝突時刪除舊行插入新行（觸發 DELETE + INSERT）。
• SELECT 5, MAX(3, COALESCE(Generations, 0))：構建新行數據。
  • 5：目標 Owner 值。
  • COALESCE(Generations, 0)：若查無記錄（Generations 為 NULL）則默認為 0。
  • MAX(3, ...)：取新值 3 與舊值（或 0）的最大值。
• FROM SearchTable WHERE Owner = 5：從目標行讀取舊值，無行時 SELECT 返回空，但 COALESCE 兜底。

通用模板

-- 參數化版本（適用於 PreparedStatement）
INSERT OR REPLACE INTO SearchTable(Owner, Generations)
SELECT ?, MAX(?, COALESCE(Generations, 0))
FROM SearchTable 
WHERE Owner = ?;

綁定參數順序：Owner值, 新Generations值, Owner值。

替代方案：UPSERT 語法（SQLite 3.24.0+）

現代 SQLite 支持標準 UPSERT 語法，語義更清晰：

INSERT INTO SearchTable(Owner, Generations) VALUES (5, 3)
ON CONFLICT(Owner) DO UPDATE SET 
    Generations = MAX(excluded.Generations, SearchTable.Generations)
WHERE excluded.Generations > SearchTable.Generations;

• excluded：引用插入嘗試的新行值。
• WHERE 子句：僅當新值更大時才更新，避免不必要的寫入。

性能與並發提示

• 加入 WHERE excluded.Generations > SearchTable.Generations 可減少無效更新，降低 WAL 日誌開銷。
• 高並發場景建議使用事務包裹，或考慮應用層樂觀鎖（版本號/時間戳）。
• 索引優化：Owner 為主鍵已自動建立索引，查詢極快。