UPDATE 修改記錄
UPDATE tbl_name
SET col1 = val1, col2 = val2, ...
[WHERE condition]
[ORDER BY ...]
[LIMIT row_count];
核心範例
-- 1. 單條精確更新 (主鍵)
UPDATE `pet` SET `sex` = 'f' WHERE `name` = 'Whistler';
-- 2. 批量條件更新
UPDATE `orders` SET `status` = 'shipped', `shipped_at` = NOW()
WHERE `status` = 'paid' AND `paid_at` < DATE_SUB(NOW(), INTERVAL 1 DAY);
-- 3. 關聯更新 (多表)
UPDATE `orders` o
JOIN `customers` c ON o.customer_id = c.id
SET o.`discount` = 0.95
WHERE c.`vip_level` = 'gold';
-- 4. 限制影響行數 (防誤操作)
UPDATE `logs` SET `archived` = 1 WHERE `created_at` < '2023-01-01' LIMIT 1000;
DELETE 刪除記錄
DELETE FROM tbl_name
[WHERE condition]
[ORDER BY ...]
[LIMIT row_count];
核心範例
-- 1. 精確刪除
DELETE FROM `pet` WHERE `name` = 'Whistler';
-- 2. 範圍刪除 + 限制數量 (分批刪大表)
DELETE FROM `big_table` WHERE `created_at` < '2022-01-01' ORDER BY `id` LIMIT 1000;
-- 3. 多表刪除 (內連接語法)
DELETE o, oi FROM `orders` o
JOIN `order_items` oi ON o.id = oi.order_id
WHERE o.`status` = 'cancelled' AND o.`created_at` < '2023-01-01';
-- 4. 清空表 (高效、重置自增)
TRUNCATE TABLE `temp_table`; -- 不觸發觸發器、不記錄 Binlog (行級)
安全守則：防止災難性誤操作
| 守則 | 做法 | 工具/技巧 |
|---|
| 永遠先 SELECT 驗證 | 同條件先跑 SELECT 確認影響行數 | SELECT * FROM t WHERE ... → 再改 DELETE/UPDATE |
| 生產環境加 LIMIT | 每次影響行數可控 | LIMIT 1000 分批循環 |
| 使用事務包裹 | 可回滾 | START TRANSACTION; ... ROLLBACK/COMMIT; |
| 備份前置 | 關鍵表操作前備份 | CREATE TABLE t_backup AS SELECT * FROM t WHERE ... |
| 權限最小化 | 應用帳號無 DROP/TRUNCATE 權限 | GRANT SELECT, INSERT, UPDATE, DELETE ON db.* TO app@'%'; |
| 審計日誌 | 記錄誰在何時執行了什麼 | 通用日誌 / 審計插件 / 中間件攔截 |
TRUNCATE vs DELETE 對比
| 特性 | TRUNCATE TABLE | DELETE FROM (無 WHERE) |
|---|
| 速度 | 極快 (重建表) | 慢 (逐行刪除、記錄 Binlog) |
| 自增計數器 | 重置為 1 | 保留 |
| 觸發器 | 不觸發 | 觸發 BEFORE/AFTER DELETE |
| 外鍵約束 | 被引用時失敗 | 受 ON DELETE CASCADE 影響 |
| 回滾 | 不可 (隱式提交) | 可 (在事務中) |
| 權限需求 | DROP + CREATE | DELETE |
保留自增值的「假刪除」技巧
-- 需清空表但保留 AUTO_INCREMENT 當前值
DELETE FROM `tbl_name` WHERE 1; -- 恆真條件，強制逐行刪除
總結
UPDATE 與 DELETE 是數據修改的核心，**WHERE 子句是靈魂**。生產環境鐵律：**先 SELECT 驗證 → 加 LIMIT 分批 → 事務包裹 → 備份可回滾**。大量刪除優先 TRUNCATE 或分區表 DROP PARTITION。
