MySQL DELETE与TRUNCATE删除数据操作详解

DELETE 语句执行逻辑与基础操作

在MySQL中，DELETE 语句用于删除表中的特定记录。理解其背后的执行顺序对于编写正确的查询至关重要。虽然语法上看起来与 UPDATE 相似，但 DELETE 的逻辑流是：

FROM → WHERE → ORDER BY → LIMIT → DELETE

• FROm: 确定源表。
• WHERE: 过滤出符合条件的行。
• ORDER BY: 对剩余数据进行排序（通常配合 LIMIT 使用）。
• LIMIT: 限制实际被删除的行数。
• DELETE: 最终执行删除动作。

语法结构如下：

DELETE [IGNORE] FROM table_name
[WHERE condition]
[ORDER BY ...
LIMIT count];

注意：如果省略 WHERE 子句，将删除表中所有记录。在生产环境中，务必谨慎操作并在删除前备份数据。

第1步：基于条件的单表删除

这是最常见的场景，通过 WHERE 子句精确指定需要删除的记录。例如，删除工龄超过20年的员工。

-- 假设表名为 t_emp，包含部门号 deptno 和入职日期 hiredate
DELETE FROM t_emp
WHERE deptno = 10 
AND DATEDIFF(NOW(), hiredate) / 365 >= 20;

第2步：结合排序限制数量的删除

当需要从某个范围内仅删除特定的几条记录时（如工资最高的一条），可以结合 ORDER BY 和 LIMIT 使用。先按条件排序，再取第一条删除。

-- 删除20号部门中薪资最高的员工
-- 注意：MySQL允许在DELETE中使用ORDER BY ... LIMIT

DELETE FROM t_emp
WHERE deptno = 20
ORDER BY sal + IFNULL(comm, 0) DESC
LIMIT 1;

DELETE 语句的多表连接删除

当需要依据另一张表的条件来删除当前表的记录时，子查询往往效率较低。利用 JOIN 语法进行多表删除不仅更高效，而且能一次性清理关联数据。

第3步：内连接（INNER JOIN）删除

语法形式为 DELETE t1, t2 FROM t1 JOIN t2 ON ...。如果要只删除某一张表的记录，则在 DELETE 后只写该表别名。

场景A：级联删除部门及其员工

删除 SALES 部门以及属于该部门的所有员工记录。这需要同时删除 t_dept 和 t_emp 中的匹配行。

DELETE e, d
FROM t_emp e
JOIN t_dept d ON e.deptno = d.deptno
WHERE d.dname = 'SALES';

场景B：基于聚合统计的删除

删除薪资低于其所在部门平均底薪的员工。这里需要将分组查询的结果作为临时表参与连接。

DELETE e
FROM t_emp e
JOIN (
    SELECT deptno, AVG(sal) AS avg_sal
    FROM t_emp
    GROUP BY deptno
) t ON e.deptno = t.deptno
WHERE e.sal < t.avg_sal;

场景C：层级关系删除（查找下属）

删除特定员工（如 KING）及其直接下属。利用自连接或子查询找出所有 mgr 字段指向该员工的记录。

DELETE e
FROM t_emp e
JOIN (SELECT empno FROM t_emp WHERE ename = 'KING') t
ON e.mgr = t.empno OR e.empno = t.empno;

第4步：外连接（LEFT/RIGHT JOIN）删除

外连接常用于处理“孤儿记录”或特定条件下的混合删除。例如，既要删除特定部门的员工，又要清理掉那些尚未分配部门的“悬浮”员工记录。

场景D：清理无部门员工及特定部门

使用 LEFT JOIN 连接部门和员工表。条件是：部门名称为 SALES，或者员工没有归属部门（deptno IS NULL）。

DELETE e
FROM t_emp e
LEFT JOIN t_dept d ON e.deptno = d.deptno
WHERE d.dname = 'SALES' OR e.deptno IS NULL;

高速清空表数据：TRUNCATE vs DELETE

如果需要删除表中的所有数据，建议使用 TRUNCATE TABLE 而不是不带 WHERE 的 DELETE。

1. DELETE 机制：逐行删除，每删除一行都会产生日志记录（Undo Log 等），受事务机制控制，速度慢且释放空间不立即返回给操作系统。
2. TRUNCATE 机制：直接销毁数据页并重新初始化，不在事务中运行（无法回滚），速度极快，适用于大批量清空场景。

操作示例：

-- 快速清空 t_emp 表，不可回滚
TRUNCATE TABLE t_emp;

常见删除相关操作辨析

在实际开发中，容易混淆以下几类“删除”操作，以下是它们的区别：

以上就是MySQL数据库数据删除操作详解的详细内容，更多关于MySQL数据删除的资料请关注本站其它相关文章！