学习如何安全、高效地修改和删除数据，掌握 UPDATE/DELETE/TRUNCATE 的核心区别，避免误操作灾难。
一、UPDATE 语法详解
1.1 单表更新
UPDATE 表名
SET 列1 = 新值1 [, 列2 = 新值2, ...]
[WHERE 条件];
实战案例：修改用户邮箱
-- 更新指定用户
UPDATE users
SET email = 'newemail@example.com', updated_at = NOW()
WHERE user_id = 1001;
-- 批量更新（根据已有字段计算）
UPDATE products
SET price = price * 0.9, discount_note = '双11打9折'
WHERE category = '电子产品';
1.2 多表更新（JOIN 更新）
-- MySQL 语法
UPDATE 表1 t1
JOIN 表2 t2 ON t1.id = t2.ref_id
SET t1.字段 = t2.字段
[WHERE 条件];
-- PostgreSQL 语法
UPDATE 表1
SET 字段 = 表2.字段
FROM 表2
WHERE 表1.id = 表2.ref_id;
实战：根据订单数据更新用户等级
-- 将累计消费超1万的用户升级为VIP
UPDATE users u
JOIN (
SELECT user_id, SUM(amount) AS total
FROM orders
WHERE status = 'completed'
GROUP BY user_id
) o ON u.user_id = o.user_id
SET u.level = 'VIP', u.level_updated = NOW()
WHERE o.total > 10000;
二、DELETE 语法详解
2.1 基本删除
DELETE FROM 表名
[WHERE 条件];
实战案例：清理过期数据
-- 删除30天前的日志
DELETE FROM logs
WHERE created_at < DATE_SUB(NOW(), INTERVAL 30 DAY);
-- 删除未付款且超时30分钟的订单
DELETE FROM orders
WHERE status = 'unpaid'
AND created_at < DATE_SUB(NOW(), INTERVAL 30 MINUTE);
2.2 多表删除（JOIN DELETE）
-- 删除没有订单的废弃用户（MySQL）
DELETE u
FROM users u
LEFT JOIN orders o ON u.user_id = o.user_id
WHERE o.order_id IS NULL;
-- 删除已停用的用户及其关联数据
DELETE u, p
FROM users u
LEFT JOIN profiles p ON u.user_id = p.user_id
WHERE u.status = 'disabled';
三、DELETE vs TRUNCATE 关键对比
| 特性 | DELETE | TRUNCATE |
|---|---|---|
| 语法 | DELETE FROM 表 WHERE 条件 | TRUNCATE TABLE 表 |
| WHERE条件 | ✅ 支持 | ❌ 不支持 |
| 逐行删除 | ✅ 逐行删除，触发触发器 | ❌ 直接释放数据页 |
| 自增ID重置 | ❌ 保留当前值 | ✅ 归零重新开始 |
| 事务支持 | ✅ 可以 ROLLBACK | ❌ 隐式提交，不可回滚 |
| 速度 | 慢（逐行 + 写日志） | 快（释放数据页） |
| 空间释放 | 不释放磁盘空间 | 释放磁盘空间 |
| 触发触发器 | ✅ 触发 | ❌ 不触发 |
场景选择参考：
-- 清空测试表（速度快，释放空间）
TRUNCATE TABLE temp_test_data;
-- 条件性清理（需要保留部分数据）
DELETE FROM orders WHERE created_at < '2025-01-01';
-- 需要回滚的生产操作
BEGIN;
DELETE FROM staging_import WHERE batch_id = 101;
-- 确认无误再 COMMIT，否则 ROLLBACK
COMMIT;
四、⚠️ 注意事项（血的教训）
4.1 WHERE 子句遗漏的灾难
-- 🔴 灾难：全表更新/删除
UPDATE employees SET salary = salary * 2; -- 全员涨薪！？
DELETE FROM employees; -- 全员离职！？
-- 🟢 正确做法：加 WHERE 条件
UPDATE employees SET salary = salary * 2 WHERE department = '研发部';
-- 🛡️ 防御机制：使用 LIMIT 逐步操作
DELETE FROM logs
WHERE created_at < '2024-01-01'
LIMIT 1000; -- 每次只删1000条，分批执行
4.2 LIMIT 控制（MySQL）
-- 每次更新少量记录，避免锁表
UPDATE users
SET status = 'inactive'
WHERE last_login < '2024-01-01'
LIMIT 500;
-- 配合循环分批删除
-- 应用层逻辑（伪代码）：
-- while (affected_rows > 0) {
-- DELETE FROM big_table WHERE condition LIMIT 1000;
-- }
4.3 事务包裹确保安全
START TRANSACTION;
-- 先查询受影响的数据
SELECT * FROM employees WHERE department = 'Temp';
-- 确认无误后执行删除
DELETE FROM employees WHERE department = 'Temp';
-- 检查影响行数是否符合预期
-- 如果不对，ROLLBACK 即可恢复
COMMIT; -- 或 ROLLBACK;
4.4 使用 LOW_PRIORITY / 外键约束
-- 低优先级写入（减少对读的影响）
DELETE LOW_PRIORITY FROM logs WHERE expire < NOW();
-- 注意外键约束：删除被引用的主表数据可能失败
-- 需要先清理子表数据或使用 ON DELETE CASCADE
五、实战案例综合
场景：季度数据清理，删除旧订单并更新用户统计
-- Step 1: 归档完成后删除3年前的订单详情
START TRANSACTION;
DELETE FROM order_items
WHERE order_id IN (
SELECT order_id FROM orders
WHERE created_at < '2023-01-01'
);
-- Step 2: 删除订单主表
DELETE FROM orders
WHERE created_at < '2023-01-01';
-- Step 3: 更新这些用户的统计
UPDATE users u
JOIN (
SELECT user_id, SUM(amount) AS total
FROM orders_archive
GROUP BY user_id
) a ON u.user_id = a.user_id
SET u.lifetime_value = u.lifetime_value - a.total;
COMMIT;
今日小结
- UPDATE：一定要带 WHERE！多表 JOIN 更新很强大，先 SELECT 确认再执行
- DELETE：逐行删除、支持条件、可事务回滚，适合有选择的清理
- TRUNCATE：极速清空、重置自增 ID、不可回滚，适合全表重置
- 安全铁律：先 SELECT 确认 → 加 WHERE → 加 LIMIT → 事务包裹 → 检查影响行数
- 批量操作：大数据量删除用 LIMIT 分批，避免锁表或事务日志爆炸
