INSERT 兩種語法風格
標準 SQL 風格
INSERT INTO users (id, name, age) VALUES (123, '姚明', 25);
MySQL 專屬 SET 風格
INSERT INTO users SET id = 123, name = '姚明', age = 25;
- 欄位與值成對出現，可讀性高。
- 自增/預設欄位可省略。
批次插入：單條 INSERT 多值
INSERT INTO users (name, age) VALUES
('姚明', 25),
('比爾·蓋茨', 50),
('火星人', 600);
- 單次網路往返、單次解析優化，效能遠優於多次 INSERT。
- 注意
max_allowed_packet 限制，超大批次需分批。
REPLACE vs INSERT ... ON DUPLICATE KEY UPDATE
| 特性 | REPLACE | INSERT ... ON DUPLICATE KEY UPDATE |
|---|
| 機制 | DELETE 舊行 + INSERT 新行 | 原地 UPDATE |
| 自增 ID | 會改變 (重新分配) | 保持不變 |
| 外鍵關聯 | 觸發 CASCADE DELETE | 不觸發 |
| 觸發器 | BEFORE DELETE + AFTER DELETE + BEFORE INSERT + AFTER INSERT | BEFORE UPDATE + AFTER UPDATE |
| 效能 | 較差 | 較好 |
| 適用場景 | 需完全替換、無外鍵依賴 | 大多數 upsert 場景 |
INSERT ... ON DUPLICATE KEY UPDATE 範例
INSERT INTO stats (date, page, views) VALUES ('2024-01-01', '/home', 1)
ON DUPLICATE KEY UPDATE views = views + 1;
UPDATE 進階用法
多表 UPDATE
UPDATE orders o
JOIN customers c ON o.customer_id = c.id
SET o.status = 'shipped', c.last_order = NOW()
WHERE o.id = 1001;
子查詢 UPDATE
UPDATE products
SET price = price * 1.1
WHERE category_id IN (SELECT id FROM categories WHERE name LIKE '電子%');
LIMIT 更新 (MySQL 專屬)
UPDATE logs SET processed = 1 WHERE processed = 0 ORDER BY id LIMIT 1000;
效能優化清單
- 批次 INSERT 替代迴圈單條。
- Upsert 用
INSERT ... ON DUPLICATE KEY UPDATE。
- UPDATE 加索引條件，避免全表掃描。
- 大量更新分批
LIMIT，避免長鎖表、大事務。
- 關閉非必要觸發器/外鍵檢查：
SET FOREIGN_KEY_CHECKS=0; (謹慎)。
常見錯誤
| 錯誤 | 原因 | 修正 |
|---|
| INSERT INTO t VALUES ('a','b') | 欄位數不匹配/順序錯 | 明確列出欄位名 |
| REPLACE 導致自增 ID 跳變 | DELETE+INSERT 機制 | 改用 ON DUPLICATE KEY UPDATE |
| UPDATE 無 WHERE | 全表更新 | 務必加 WHERE，測試環境先 SELECT 驗證 |
熟練掌握 INSERT/REPLACE/UPDATE 變體與優化技巧，是寫出高效 MySQL DML 的關鍵。
