您现在的位置：程式碼之家 >> 數據庫知識 > >> MySQL 資料庫 >> MySQL 綜合教學

MySQL DML 核心指南：INSERT、REPLACE、UPDATE、DELETE 與 TRUNCATE 深度解析

編輯：MySQL 綜合教學

MySQL DML 核心指南：INSERT、REPLACE、UPDATE、DELETE 與 TRUNCATE 深度解析

數據操作語言（DML）是業務邏輯落地的核心。MySQL 在標準 SQL 基礎上擴展了 REPLACE、INSERT ... SET、多行 VALUES、DELETE ... ORDER BY/LIMIT 等實用語法。本文系統梳理五大 DML 語句的語法變體、行為差異、性能特徵與最佳實踐。

一、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. 省略欄位列表（高風險，慎用）

INSERT INTO users VALUES (123, '姚明', 25);  -- 必須給所有欄位按建表順序賦值

4. 默認值插入

INSERT INTO users () VALUES ();  -- 全部用默認值
INSERT INTO users (name) VALUES ('姚明');  -- 僅指定部分欄位

5. 批量插入（性能利器）

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

單條 SQL 插入多行，大幅減少網絡往返與解析優化開銷
每行值必須用 (...) 包裹，行間用逗號分隔
錯誤寫法：VALUES (1,2,3,4,5) → 視為單行 5 個欄位

二、REPLACE：插入或替換的原子操作

REPLACE = DELETE（若唯一鍵衝突）+ INSERT。語法與 INSERT 幾乎相同。

核心機制

表必須有 PRIMARY KEY 或 UNIQUE NOT NULL 索引，否則退化為普通 INSERT
遇到唯一鍵衝突：刪除舊行 → 插入新行（所有欄位重新賦值）
返回影響行數：1=純插入，2=一條衝突（1刪+1插），>2=多個唯一索引衝突

典型場景

-- 用戶資料同步：以 phone 為唯一鍵，新數據覆蓋舊數據
REPLACE INTO users (phone, name, avatar, updated_at) 
VALUES ('13800001111', '新名稱', 'new.jpg', NOW());

⚠️ 與 INSERT ... ON DUPLICATE KEY UPDATE 差異

特性	REPLACE	INSERT ... ON DUPLICATE KEY UPDATE
衝突處理	刪舊插新（所有列重寫）	就地更新指定列
自增 ID	變化（新行新 ID）	保持不變
外鍵關聯	觸發 ON DELETE CASCADE	不觸發
性能	較慢（兩次操作）	較快（單次操作）
推薦度	低（特定場景）	高（通用首選）

建議：優先使用 INSERT ... ON DUPLICATE KEY UPDATE，僅在需要「完全替換行」且無外鍵依賴時用 REPLACE。

三、UPDATE：精準修改數據

基本語法

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

關鍵行為

無 WHERE = 全表更新（生產環境嚴禁）
類型不匹配：隱式轉換（字符串截斷、數值截斷、NULL→默認值）
不修改的兩種情況：WHERE 無匹配行、新值等於舊值
返回實際修改行數（不含匹配但未修改的行）
TIMESTAMP 欄位若未顯式賦值，自動更新為當前時間

多表更新

UPDATE orders o JOIN users u ON o.user_id = u.id 
SET o.status = 'shipped' WHERE u.vip_level > 2;

四、DELETE 與 TRUNCATE：刪除的藝術

對比速查

特性	DELETE	TRUNCATE TABLE
WHERE 條件	支持	不支持（全表）
返回影響行數	實際刪除行數	0（不返回）
自增計數器	保持（除非 WHERE 1）	重置為 1
觸發器	觸發 FOR EACH ROW	不觸發
事務安全	支持回滾	隱式提交，不可回滾
鎖機制	行鎖（InnoDB）	表鎖（元數據鎖）
速度	慢（逐行標記）	極快（重建表）
ORDER BY / LIMIT	支持（MySQL 擴展）	不支持

DELETE 進階用法

-- 保留自增值：永真條件掃描全表（慢但保留 ID）
DELETE FROM t1 WHERE 1;

-- 分頁刪除大表歷史數據（避免長事務鎖表）
DELETE FROM logs WHERE created_at < '2023-01-01' ORDER BY id LIMIT 10000;
-- 循環執行直到影響行數為 0