DML (Data Manipulation Language) 是資料庫日常操作核心。本文系統整理 MySQL 插入、更新、刪除、替換、批次操作、事務控制、效能優化技巧，附實戰範例與避坑指南。
-- 完整欄位插入
INSERT INTO users (username, email, status) VALUES ('john', 'john@example.com', 1);
-- 省略欄位 (需有預設值或自增)
INSERT INTO users (username, email) VALUES ('jane', 'jane@example.com');
-- 多行批次插入 (高效！)
INSERT INTO users (username, email) VALUES
('user1', 'u1@example.com'),
('user2', 'u2@example.com'),
('user3', 'u3@example.com');
-- 插入查詢結果
INSERT INTO archive_users SELECT * FROM users WHERE created_at < '2023-01-01';
-- INSERT IGNORE: 忽略重複鍵錯誤
INSERT IGNORE INTO users (username, email) VALUES ('john', 'john@example.com');
-- ON DUPLICATE KEY UPDATE: UPSERT (MySQL 特有)
INSERT INTO users (username, email, login_count) VALUES ('john', 'john@example.com', 1)
ON DUPLICATE KEY UPDATE login_count = login_count + 1, last_login = NOW();
-- REPLACE INTO: 刪除舊行再插新行 (主鍵/唯一鍵衝突時)
REPLACE INTO users (id, username, email) VALUES (1, 'john', 'new@example.com');
-- 單表更新 UPDATE users SET status = 2, updated_at = NOW() WHERE id = 1; -- 多表更新 (JOIN) UPDATE orders o JOIN customers c ON o.customer_id = c.id SET o.shipping_address = c.address WHERE o.status = 'pending'; -- 子查詢更新 UPDATE products SET price = price * 1.1 WHERE category_id IN (SELECT id FROM categories WHERE name = 'Electronics');
UPDATE ... LIMIT 1000 (需配合 ORDER BY)-- 條件刪除 DELETE FROM users WHERE status = 0 AND last_login < '2023-01-01'; -- 多表刪除 (JOIN) DELETE u FROM users u LEFT JOIN orders o ON u.id = o.user_id WHERE o.id IS NULL; -- 刪除無訂單用戶 -- TRUNCATE: 快速清空表 (DDL、不觸發 Trigger、重置自增) TRUNCATE TABLE logs;
-- 分批刪除避免長鎖定、Binlog 膨脹
REPEAT
DELETE FROM big_table WHERE created_at < '2023-01-01' LIMIT 10000;
UNTIL ROW_COUNT() = 0 END REPEAT;
-- 或用存儲過程/腳本循環
-- 顯式事務 START TRANSACTION; INSERT INTO orders ...; INSERT INTO order_items ...; UPDATE products SET stock = stock - 1 WHERE id = 1; COMMIT; -- 或 ROLLBACK; -- 保存點 (部分回滾) START TRANSACTION; INSERT ...; SAVEPOINT sp1; UPDATE ...; ROLLBACK TO SAVEPOINT sp1; -- 僅回滾到 sp1 COMMIT;
| 場景 | 優化技巧 | 效果 |
|---|---|---|
| 大量 INSERT | 多行 VALUES、LOAD DATA INFILE、關閉索引/外鍵檢查 | 10-100x 加速 |
| 大量 UPDATE | 分批 LIMIT、命中索引、避免觸發器 | 減少鎖競爭 |
| 大量 DELETE | 分批 LIMIT、TRUNCATE 替代 (全清)、分區表 DROP PARTITION | 避免長事務 |
LOAD DATA INFILE '/path/data.csv' INTO TABLE users FIELDS TERMINATED BY ',' ENCLOSED BY '"' LINES TERMINATED BY '\n' IGNORE 1 LINES (username, email, @created_at) SET created_at = STR_TO_DATE(@created_at, '%Y-%m-%d %H:%i:%s');
| 坑點 | 後果 | 解決 |
|---|---|---|
| UPDATE 無 WHERE | 全表更新、鎖死資料庫 | 養成習慣先 SELECT 驗證 |
| DELETE 無 WHERE | 全表刪除 | 同上，或用 TRUNCATE 替代 |
| 隱式類型轉換 | 索引失效、錯誤結果 | 參數化查詢、顯式轉型 |
| 大事務長鎖定 | 阻塞其他查詢、複製延遲 | 分批提交、優化索引 |
| REPLACE INTO 觸發器 | 先 DELETE 再 INSERT，觸發器雙重執行 | 改用 INSERT ... ON DUPLICATE KEY UPDATE |
MySQL DML 核心：INSERT 批次多行 VALUES、UPDATE 命中索引分批、DELETE 分批 LIMIT、事務显式控制、LOAD DATA 極速導入。記住：ON DUPLICATE KEY UPDATE 是 UPSERT 首選，REPLACE INTO 會觸發雙重 Trigger。大量操作必須分批、監控、可回滾。
