深入解析 MySQL 更新語句：INSERT、REPLACE、UPDATE、DELETE、TRUNCATE

前言

SQL 資料操作語言（DML）核心包含 INSERT、UPDATE、DELETE 三大標準指令，MySQL 額外擴充了 REPLACE 與 TRUNCATE TABLE。本文系統整理五大更新語句的語法差異、行為細節與最佳實務。

一、INSERT 與 REPLACE：新增資料的兩種策略

1. INSERT 兩種語法風格

標準風格（值與欄位分離）：

INSERT INTO users (id, name, age) VALUES (123, '姚明', 25);

MySQL 專屬 SET 風格（欄位值成對出現）：

INSERT INTO users SET id = 123, name = '姚明', age = 25;

省略自增/預設欄位：

-- 僅指定需賦值欄位
INSERT INTO users (name, age) VALUES ('姚明', 25);
INSERT INTO users SET name = '姚明', age = 25;

2. 批次新增多筆記錄（MySQL 專屬）

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

關鍵規則： 每筆資料必須用獨立括號包裹，以逗號分隔。單欄位表錯誤寫法：VALUES (1,2,3) → 正確：VALUES (1),(2),(3)。

3. REPLACE：Upsert 語意的原子操作

當表擁有 唯一索引（Primary Key / Unique Key）且不允許 NULL 時：

• 無衝突 → 等同 INSERT。
• 鍵值衝突 → 先 DELETE 舊列，再 INSERT 新列，回傳影響列數 2。

REPLACE INTO users (id, name, age) VALUES (123, '趙本山', 50);
-- 多筆
REPLACE INTO users (id, name, age) VALUES (123, '趙本山', 50), (134, 'Mary', 15);

多重唯一索引情境： 若表有多個唯一索引，單一 REPLACE 可能刪除多筆舊資料（影響列數 > 2）。

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

UPDATE users SET age = 24, name = 'Mike' WHERE id = 123;

行為細節

• 型別自動轉換：數值超範圍截取為邊界值、字串過長截斷、非空欄位設 NULL 會轉為預設值（數值 0、字串空字串）。
• 不影響資料情況：WHERE 無匹配列、或新值等於舊值。
• 回傳值：實際「更新」的列數（不含符合條件但未變更的列）。
• TIMESTAMP 欄位在其他欄位更新時會自動刷新。

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

DELETE 進階用法

-- 刪除前 6 筆符合條件的資料（配合排序）
DELETE FROM users WHERE name = 'Mike' ORDER BY id DESC LIMIT 6;

保留自增值技巧

DELETE FROM table1 WHERE 1;  -- 掃描全表，保留 AUTO_INCREMENT 最大值

總結建議

• 大量清空表 → TRUNCATE TABLE。
• 條件刪除、需觸發器、需回傳列數 → DELETE。
• 主鍵衝突需覆蓋 → REPLACE（注意多重唯一索引副作用）。
• 批次新增 → 單一 INSERT ... VALUES (),(),() 減少網路往返。