MySQL 更新語句深度解析：INSERT、REPLACE、UPDATE、DELETE 與 TRUNCATE 實戰

SQL 敘述大致分為查詢（SELECT）與更新兩大類。更新語句用於修改資料，標準 SQL 包含 INSERT、UPDATE、DELETE，MySQL 另擴充 REPLACE。本文以 MySQL 為背景，系統整理各類更新語句的語法差異、效能考量與實務陷阱。

一、INSERT 與 REPLACE：資料插入的兩種哲學

兩者皆為插入新資料，核心差異在於「重複鍵值」的處理策略。

1. INSERT 多樣化語法

MySQL 支援兩種寫法：

• 標準形式：INSERT INTO tbl (col1, col2) VALUES (val1, val2); —— 列名與值分離，數量須一致。
• SET 形式：INSERT INTO tbl SET col1=val1, col2=val2; —— 列值成對出現，可讀性較佳，且至少需指定一欄。

若欄位具自增或預設值，兩種寫法皆可省略該欄位。MySQL 尚允許 INSERT INTO tbl () VALUES () 全用預設值，或 INSERT INTO tbl VALUES (v1, v2, v3) 按建表順序填滿所有欄位（順序不可錯）。

2. 批次插入：單一敘述寫入多筆

MySQL 專屬擴充語法：

INSERT INTO users (name, age)
VALUES ('姚明', 25), ('比爾·蓋茲', 50), ('火星人', 600);

每組值必須以括號包裹、逗號分隔。若省略欄位清單，每組括號內值的數量與順序必須與資料表定義完全一致。此寫法大幅減少解析/優化開銷，效能遠優於多次單筆 INSERT。

3. REPLACE：刪除舊行再插入新行的原子操作

當表存在唯一索引且嘗試插入重複鍵值時，REPLACE 會先刪除舊行再插入新行，回傳影響行數：

• 1：無重複，純插入
• 2：一筆重複（刪 1 + 插 1）
• >2：多重唯一索引導致多筆刪除

語法與 INSERT 幾乎相同，支援 VALUES 與 SET 兩種形式。注意：REPLACE 會重置所有欄位（含未指定者），若僅需部分更新請改用 UPDATE。

二、UPDATE：精準修改既有資料

UPDATE tbl SET col1=val1, col2=val2 WHERE condition;

關鍵行為：

• 型別不符時自動轉換（數值溢位截斷、字串過長截斷、非空欄位設 NULL 會轉為預設值 0/空字串）。
• WHERE 條件無匹配或新值等於舊值時，不修改資料且回傳 0 筆影響。
• TIMESTAMP 欄位若未顯式更新，在其他欄位變更時會自動刷新。
• 回傳「實際修改筆數」，非「符合條件筆數」；若需後者可透過 C API 或 JDBC 參數取得。

三、DELETE 與 TRUNCATE TABLE：刪除策略抉擇

實務建議：需保留自增值用 DELETE FROM tbl WHERE 1；大量清空首選 TRUNCATE；需條件刪除或分批刪除（配合 LIMIT）用 DELETE。

效能與避坑小結

• 批次寫入優先使用多值 INSERT / REPLACE
• 避免在 WHERE 欄位上做運算（如 YEAR(date)<2007），改寫為範圍查詢 date < '2007-01-01' 以利用索引
• 慎用 NOT IN、<>，易導致全表掃描，改用 NOT EXISTS 或 id>3 OR id<3
• LIKE 查詢：aaa% 可用索引，%aaa% 無法使用

掌握更新語句的細微差異，能讓資料操作更精準、效能更穩健。