MySQL 更新語句深度解析：INSERT、REPLACE、多行插入與主鍵衝突處理

SQL 更新語句是數據操作的核心。MySQL 在標準 SQL（INSERT/UPDATE/DELETE）基礎上擴展了 REPLACE 語句，並提供獨特的多行插入語法。本文系統梳理 MySQL 更新語句的語法變體、行為差異與最佳實踐。

一、INSERT 與 REPLACE：主鍵衝突的兩種哲學

兩者均用於插入新數據，核心差異在於重複鍵值處理策略：

• INSERT：遇到主鍵/唯一鍵衝突報錯終止（除非加 IGNORE 忽略）
• REPLACE：遇到衝突先刪除舊行再插入新行（實質為 DELETE + INSERT）

二、INSERT 多樣語法形態

1. 標準 VALUES 形式

INSERT INTO users (id, name, age) VALUES (123, '姚明', 25);
-- 列名與值分離，數量順序必須一一對應

2. MySQL 專有 SET 形式

INSERT INTO users SET id = 123, name = '姚明', age = 25;
-- 列值成對出現，可讀性強，至少賦值一列

3. 利用默認值/自增簡化

-- id 為 AUTO_INCREMENT 時
INSERT INTO users (name, age) VALUES ('姚明', 25);
INSERT INTO users SET name = '姚明', age = 25;
-- 甚至完全使用默認值
INSERT INTO users () VALUES ();

4. 省略列名的風險

INSERT INTO users VALUES (123, '姚明', 25); -- 必須匹配所有列且順序固定
-- 易因表結構變更導致數據錯位，生產環境禁用

三、單語句多行插入：批量寫入利器

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

核心規則：每組值必須用 () 包裹，組間用逗號分隔。錯誤示例：VALUES (1,2,3,4,5) 會被視為單行 5 列而非 5 行 1 列，拋出 Column count doesn't match 錯誤。正確寫法：VALUES (1),(2),(3),(4),(5)。

性能優勢：單次解析優化、單次網絡往返、單次事務提交，批量寫入效率遠超循環調用單行 INSERT。

四、REPLACE 實戰場景

適用於業務主鍵天然唯一、需全量覆蓋更新的場景：

• 數據同步任務（如每日全量刷新維度表）
• 配置中心「鍵-值」存儲
• 爬蟲去重存儲（URL 為主鍵）

副作用警示：

1. 觸發 DELETE + INSERT 觸發器
2. 自增 ID 會重新分配（舊 ID 丟失）
3. 外鍵關聯可能導致級聯刪除（需謹慎）
4. InnoDB 聚簇索引導致物理頁分裂，寫放大明顯

五、現代替代方案：ON DUPLICATE KEY UPDATE

INSERT INTO users (id, name, age) VALUES (123, '姚明', 26)
ON DUPLICATE KEY UPDATE name=VALUES(name), age=VALUES(age);

優勢：原地更新、保持自增 ID 不變、避免觸發器雙重觸發、減少索引維護開銷。推薦作為 REPLACE 的首選替代。