MySQL 數據操作語句全解析：INSERT、UPDATE、DELETE、REPLACE 深度對比

掌握 MySQL 四大數據操作語句（DML）的語法細節、行為差異與最佳實踐，是開發高質量數據訪問層的前提。本文系統對比 INSERT/REPLACE/UPDATE/DELETE/TRUNCATE 核心特性。

INSERT 與 REPLACE：插入數據的兩種哲學

INSERT 兩種語法風格

-- 標準 SQL 風格（列值分離）
INSERT INTO users (id, name, age) VALUES (123, '張三', 25);

-- MySQL 擴展 SET 風格（列值配對）
INSERT INTO users SET id = 123, name = '張三', age = 25;

差異：SET 風格允許部分列賦值、列值直觀對應、便於動態構建；VALUES 風格標準、支持多行批量插入。

批量插入優化

-- 單條 INSERT 循環：N 次解析、優化、網絡往返
-- 批量 INSERT：一次解析、一次網絡往返
INSERT INTO users (name, age) VALUES 
  ('張三', 25), ('李四', 30), ('王五', 28);

• 每行值必須用括號包裹，逗號分隔
• VALUES (1,2,3,4,5) 錯誤，需 VALUES (1),(2),(3),(4),(5)
• 批量大小建議 500-1000 行，避免超過 max_allowed_packet

REPLACE：主鍵衝突時的「刪後插」

REPLACE INTO users (id, name, age) VALUES (123, '趙六', 35);

• 前置條件：表有主鍵或唯一索引，且該列非 NULL
• 行為：無衝突 = INSERT；有衝突 = DELETE 舊行 + INSERT 新行
• 返回值：1=純插入，2=一條衝突（1刪+1插），>2=多唯一索引多條衝突
• 原子性：單語句完成，無需顯式事務

INSERT vs REPLACE 核心差異

UPDATE：精準修改

UPDATE users SET age = 26, name = '張三豐' WHERE id = 123;

• WHERE 缺失 = 全表更新（極度危險）
• 類型不匹配：隱式轉換（字符串截斷、數值截斷、NULL 轉默認值）
• 返回值：實際修改行數（滿足 WHERE 但值未變的行不計入）
• TIMESTAMP 列：其他列更新時自動刷新為當前時間

UPDATE vs REPLACE

DELETE 與 TRUNCATE：清空表的兩種方式

保留自增值的 DELETE 寫法

DELETE FROM users WHERE 1;  -- 恆真條件，掃描全表但保留自增計數器

MySQL 獨有擴展：ORDER BY / LIMIT 子句

-- 刪除名為 Mike 的前 6 條（按 id 降序）
DELETE FROM users WHERE name = 'Mike' ORDER BY id DESC LIMIT 6;

最佳實踐建議

1. 批量寫入優先 INSERT ... VALUES (...),(...) 或 LOAD DATA INFILE
2. 主鍵衝突業務語義明確時用 REPLACE，需保留其他列值用 INSERT ... ON DUPLICATE KEY UPDATE
3. 全表清空優先 TRUNCATE，需保留自增值或條件刪除用 DELETE
4. 生產環境 DELETE/UPDATE 必加 WHERE，建議先 SELECT 驗證影響範圍
5. 大事務拆分小批次，避免長鎖定、Binlog 膨脹、回滾耗時

精準選擇 DML 語句，兼顧業務語義、性能與數據安全。