測試表結構
CREATE TABLE Student (
ID TEXT,
Name TEXT NOT NULL,
Sex TEXT,
Email TEXT UNIQUE,
Score TEXT CHECK(Score > 0),
TecID TEXT REFERENCES Teacher(ID),
Class TEXT,
PRIMARY KEY (ID, Name) -- 複合主鍵
);
REPLACE 核心行為:DELETE + INSERT
REPLACE INTO Student (...) VALUES (...) 實際執行:
- 根據主鍵/唯一約束查找衝突行。
- 刪除所有衝突行(可能多行,若多個唯一約束同時衝突)。
- 插入新行(未指定列默認
NULL)。
實驗驗證關鍵點
| 實驗 | 操作 | 觀察結果 | 結論 |
| 1 | REPLACE 指定全列,主鍵 (2,'LiSi') 存在 | 該行移至表尾(物理位置改變) | 舊行刪除、新行插入,非原地更新 |
| 2 | REPLACE 未指定 Class 列 | Class 變為 NULL | 未指定列不保留舊值,直接置 NULL |
| 3 | 主鍵 (ID,Name) 確定目標行 | 僅 (2,'LiSi') 被替換 | 按主鍵/唯一約束定位,非 WHERE |
| 4 | REPLACE ... WHERE ID='2' | 語法錯誤 | REPLACE 不支持 WHERE 子句 |
| 5 | REPLACE 不存在的主鍵 (100,'A') | 插入新行 | 無衝突則轉為純 INSERT |
| 6 | 新行 Email 與現有行衝突 (UNIQUE) | 衝突行被刪除,新行插入 | 任何唯一約束衝突均觸發刪除舊行 |
REPLACE vs UPDATE 核心差異
| 維度 | REPLACE | UPDATE |
| 定位方式 | 主鍵/唯一約束值 | WHERE 子句 |
| 影響行數 | 0 或 1(或多行若多約束衝突) | 0~N 由 WHERE 決定 |
| 底層操作 | DELETE + INSERT | 原地修改 |
| 未指定列 | 置 NULL | 保留原值 |
| 觸發器 | 觸發 DELETE + INSERT 觸發器 | 觸發 UPDATE 觸發器 |
| 自增列 | 可能改變 ROWID | ROWID 不變 |
最佳實踐建議
- 明確語義時用
UPDATE(修改);需「不存在則插入,存在則全量替換」時用 REPLACE(Upsert 語義)。
- 注意
REPLACE 會改變 ROWID、觸發雙觸發器、重置未指定列,慎用於有外鍵引用、觸發器、自增列的表。
- SQLite 3.24.0+ 支持標準
UPSERT (ON CONFLICT DO UPDATE),語義更清晰、性能更優,優先遷移。