MySQL 更新語句深度解析：INSERT、REPLACE、多行寫入與最佳實踐

SQL 更新語句分類

標準 SQL 三大更新語句：INSERT、UPDATE、DELETE。MySQL 獨有增強：REPLACE。

一、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. 多行批次 INSERT（MySQL 專有，極大提升寫入效能）

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

關鍵規則：每組值必須用括號包裹，組間用逗號分隔。
錯誤寫法：VALUES (1,2,3,4,5) → 視為單行 5 欄位
正確寫法：VALUES (1),(2),(3),(4),(5) → 5 行 1 欄位

3. REPLACE：原子化「覆寫」語義

觸發條件：表有 PRIMARY KEY 或 UNIQUE 索引，且新行鍵值與現有行衝突。

行為：自動刪除衝突舊行 → 插入新行。單語句原子操作，無需顯式事務。

語法形式

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

影響行數解讀

多唯一索引觸發多行刪除範例

CREATE TABLE t1 (
  a INT NOT NULL UNIQUE,
  b INT NOT NULL UNIQUE,
  c INT NOT NULL UNIQUE
);
-- 既有 (1,1,1), (2,2,2), (3,3,3)

REPLACE INTO t1 (a,b,c) VALUES (1,2,3);
-- 影響 4 行：刪除 3 行 + 插入 1 行
-- 結果僅剩 (1,2,3)

二、UPDATE 與 DELETE 核心要點

UPDATE

UPDATE users SET age = 26 WHERE id = 123;
UPDATE users SET age = age + 1 WHERE id = 123;  -- 相對更新
UPDATE LOW_PRIORITY users SET ... WHERE ...;    -- 降低寫入優先級

DELETE

DELETE FROM users WHERE id = 123;
DELETE FROM users WHERE id > 100 LIMIT 10;  -- 限制刪除筆數
DELETE FROM users;  -- 清空表（比逐行刪快得多）

三、效能與安全最佳實踐

• 批次寫入用多行 INSERT/REPLACE：減少網路往返與解析開銷
• 大量刪除用 TRUNCATE TABLE 或無 WHERE DELETE：避免逐行鎖開銷
• 嚴禁無 WHERE 的 UPDATE/DELETE：生產環境啟用 --i-am-a-dummy 強制要求 WHERE
• 參數化查詢/預處理語句：防 SQL 注入、提升重複執行效能
• 利用 INSERT DELAYED 非同步寫入：日誌類高寫入低一致性場景