DELETE / TRUNCATE 命令详解 - MySQL

语法

DELETE FROM table [WHERE conditions] [ORDER BY ...] [LIMIT count];
TRUNCATE TABLE table;

参数	说明	示例	级别
`WHERE`	删除条件	`DELETE FROM logs WHERE created_at < '2025-01-01';`	常用
`LIMIT`	限制删除行数	`DELETE FROM logs WHERE level = 'debug' LIMIT 10000;`	进阶
`TRUNCATE`	快速清空整张表（不可回滚）	`TRUNCATE TABLE tmp_data;`	危险
`ORDER BY`	按顺序删除	`DELETE FROM logs ORDER BY created_at ASC LIMIT 1000;`	进阶
`JOIN`	多表关联删除	`DELETE o FROM orders o JOIN users u ON o.user_id = u.id WHERE u.status = -1;`	进阶

DELETE FROM sessions
WHERE expire_at < NOW();

删除过期的会话记录

-- 避免一次删除太多导致锁表
DELETE FROM operation_logs
WHERE created_at < '2025-01-01'
ORDER BY id ASC
LIMIT 10000;
-- 重复执行直到 affected rows = 0

大表删除务必分批，每批 1 万条左右

-- 比 DELETE 快得多，但不可回滚
TRUNCATE TABLE temp_import_data;

TRUNCATE 重置自增 ID，不触发 DELETE 触发器

-- 不真正删除，标记为已删除
UPDATE users
SET deleted_at = NOW(), status = -1
WHERE id = 1001;

-- 查询时排除已删除
SELECT * FROM users WHERE deleted_at IS NULL;

生产环境推荐软删除，数据可恢复

ERROR 1175: You are using safe update mode 安全模式要求 DELETE 必须带 WHERE 条件。这是保护机制，不建议关闭

ERROR 1451: Cannot delete or update a parent row: a foreign key constraint fails 有其他表通过外键引用该行。先删除子表数据，或设置 ON DELETE CASCADE