MySQL 入門核心 DML：INSERT、UPDATE、DELETE、REPLACE 完整語法與實例

資料操作語言 (DML) 是與資料庫交互的核心。本文系統整理 INSERT、REPLACE、DELETE、UPDATE 四大指令的完整語法、多種寫法、實戰範例與避坑指南，適合入門速查與進階複習。

一、INSERT 插入資料

語法變體

-- 1. 標準 VALUES 寫法 (單行/多行)
INSERT INTO tbl (col1, col2) VALUES (val1, val2);
INSERT INTO tbl (col1, col2) VALUES (v1, v2), (v3, v4), (v5, v6);  -- 批次插入，效能最佳

-- 2. SET 寫法 (單行，欄位順序不固定)
INSERT INTO tbl SET col1 = val1, col2 = val2;

-- 3. SELECT 衍生插入 (查詢結果直接寫入)
INSERT INTO tbl (col1, col2) SELECT col_a, col_b FROM other_tbl WHERE condition;

-- 4. 忽略重複鍵錯誤
INSERT IGNORE INTO tbl (col1, col2) VALUES (val1, val2);  -- 重複主鍵/唯一鍵靜默跳過

資料型別字面值規範

實戰範例

-- 批次插入 (高效)
INSERT INTO tutors (Tname, Gender, Age) VALUES 
('Sam', 'M', 28), 
('Barlow', 'M', 27);

-- SET 寫法
INSERT INTO tutors SET Tname='Winne', Gender='F', Age=25;

-- 衍生插入 (年齡 > 20 的學生轉為導師)
INSERT INTO tutors (Tname, Gender, Age) 
SELECT Name, Gender, Age FROM students WHERE Age > 20;

二、REPLACE 插入或替換

邏輯：嘗試插入 → 若違反主鍵/唯一鍵 → 刪除舊行 → 插入新行。本質是 DELETE + INSERT，會觸發 ON DELETE 觸發器、自增 ID 變更。

語法 (與 INSERT 完全一致)

REPLACE INTO tbl (col1, col2) VALUES (val1, val2);
REPLACE INTO tbl SET col1 = val1, col2 = val2;
REPLACE INTO tbl (col1, col2) SELECT col_a, col_b FROM other_tbl;

適用場景

• 「不存在則插入，存在則全欄位更新」 (Upsert 語意)
• 需保持主鍵不變時用 INSERT ... ON DUPLICATE KEY UPDATE (見下)

三、DELETE 刪除資料

基礎語法

DELETE FROM tbl WHERE condition;
DELETE FROM tbl WHERE condition LIMIT 1;  -- 僅刪 1 行
DELETE FROM tbl;  -- 清空表 (不重置自增)

TRUNCATE vs DELETE

清空表並重置自增

TRUNCATE TABLE tbl;

四、UPDATE 更新資料

基礎語法

UPDATE tbl SET col1 = val1, col2 = val2 WHERE condition;
UPDATE tbl SET col1 = val1 WHERE condition LIMIT 1;  -- 僅更新 1 行

多表 UPDATE (MySQL 特有)

-- JOIN 更新
UPDATE tbl_a a JOIN tbl_b b ON a.id = b.id SET a.age = b.age;

-- 子查詢更新
UPDATE tbl_a SET age = (SELECT age FROM tbl_b WHERE id = tbl_a.id);

實戰範例

-- 修正寵物性別
UPDATE pet SET sex = 'f' WHERE name = 'Whistler';

-- 學生年齡修正
UPDATE students SET Age = 20 WHERE Name = 'Xuzhu';

五、進階 Upsert 模式對比 (MySQL 8.0+)

ON DUPLICATE KEY UPDATE 範例

INSERT INTO stats (date, pv, uv) VALUES ('2024-01-15', 100, 50)
ON DUPLICATE KEY UPDATE 
    pv = pv + VALUES(pv), 
    uv = uv + VALUES(uv),
    updated_at = NOW();

避坑提醒

1. 自增主鍵插入：傳 NULL、0 或省略該欄位，絕不傳空字串 '' (嚴格模式報錯)。
2. 大批量操作：分批 (每批 1000-5000 行)，避免長鎖/大事務/Undo log 膨脹。
3. WHERE 條件：UPDATE/DELETE 沒 WHERE = 全表操作，**生產環境嚴禁** 直接執行無條件 DML。
4. 字元集一致：字串字面值編碼與連線/表字元集一致，避免亂碼/排序規則衝突。

熟練掌握四大 DML 的語法變體、效能特性與適用邊界，是資料庫開發者處理業務邏輯的基本功。