delete 与 truncate 命令的区别

直接去看原文

原文链接:【SQL】delete 与 truncate 命令的区别_truncate和delete的区别-CSDN博客

-------------------------------------------------------------------------------------------------------------------------------- 

1. 相同点

• 二者都能删除表中的数据
• 一般情况下， 主流的关系型数据库都支持这两个命令

2. 区别

2.1 SQL 性质的不同

• delete：属于 DML(数据操作语言) 范畴
• truncate：属于 DDL(数据定义语言) 范畴

2.2 操作对象不同

delete：能删除表中或基于真实表创建的视图中的数据
truncate：只能删除表中的数据，无法应用在视图上

创建测试视图

create or replace view v_emp
as
select * from emp
where sal > 2000;

delete 测试：删除成功

SQL> delete from v_emp;
6 rows deleted
 
SQL> commit;
Commit complete

truncate 测试：

SQL> truncate table v_emp;
truncate table v_emp
 
ORA-01702: 视图不适用于此处

2.3 条件限制区别

delete：删除表中数据时，可以通过 where 关键字，进行选择性s删除
truncate：不能使用 where 关键字进行选择性删除

delete 测试：

SQL> delete from emp where sal > 2000;
6 rows deleted
 
SQL> commit;
Commit complete

truncate 测试：

SQL> truncate table emp where sal > 2000;
truncate table emp where sal > 2000
 
ORA-03291: 无效的截断选项 - 缺失 STORAGE 关键字

2.4 事务

delete：删除表中数据时，涉及到事务处理：回退（缓存）、提交、撤退
truncate：删除缓存时，不涉及缓存事务处理

delete 测试：

SQL> delete from emp;
14 rows deleted
 
SQL> select count(1) from emp;
  COUNT(1)
----------
         0

此时，在本图形化工具中，显示 14 条信息已被删除，同时查看员工表，得到 0 条数据
之后，若不执行 commit 命令，通过另一个进程再连接上数据库

此时检索员工表，依旧是 14 条数据，验证了事务不提交后的数据的不完整性（oracle数据库是手动 commit）
再在原窗口执行 commit 命令

事务得到提交，保证了数据的完整性

truncate 测试：

SQL> truncate table emp;
Table truncated
 
SQL> select count(1) from emp;
  COUNT(1)
----------
         0

若使用 rollback 命令回退：

SQL> rollback;
Rollback complete
 
SQL> select count(1) from emp;
  COUNT(1)
----------
         0

发现：虽然提示 Rollback complete ，但再次检索数据依旧是 0 。发现 truncate 命令不涉及到事务处理

2.5 删除效率

delete：由于 delete 删除数据时要考虑事务管理（缓存、回退机制、日志记录），所以当删除大批量数据时，速度慢，效率低，甚至达不到删除的目的
truncate：删除大批量数据时，速度快、效率高、但无法撤销

测试：

最后生成 1400 万条数据 
SQL> 
 
14680064 rows inserted
 
 
Commit complete

使用 delete命令，已不能在短时间内删除，甚至达不到删除的目的
使用 truncate 命令，可快速删除大批量数据

SQL> truncate table jiker;
 
Table truncated

2.6 激活触发器

delete：在进行删除操作时，可能激活触发器的处理
truncate：不存在激活触发器处理

2.7 外键约束

delete：可以运用 SQL 语句、按照业务逻辑、按照一定的先后顺序可以删除相关表中的数据
truncate：不能删除对于由 foreign key 约束引用的表，不能删除该表中的数据

delete 测试：

直接删除 dept 部门表中的数据

SQL> delete from dept;
delete from dept
 
ORA-02292: 违反完整约束条件 (SCOTT.FK_DEPTNO) - 已找到子记录

先删除员工表，再删除部门表，成功！

SQL> delete from emp;
14 rows deleted
 
SQL> commit;
Commit complete
 
SQL> delete from dept;
4 rows deleted

truncate 测试：

直接删除部门表

SQL> truncate table dept;
truncate table dept
 
ORA-02266: 表中的唯一/主键被启用的外键引用

先删除员工表，再删除部门表，

SQL> truncate table emp;
 
Table truncated
 
SQL> truncate table dept;
truncate table dept
 
ORA-02266: 表中的唯一/主键被启用的外键引用

依旧不能被删除

3. 应用场景

• 需要根据一定的业务条件删除数据时、且数据量、性能可控的情况下，可以考虑使用 delete from xxx where 业务条件
• 需要考虑事务提交、撤销的管理机制时，可以使用 delete
• 当需要删除大批量数据时，同时要求速度快，效率高并且w无需撤销时，可以使用 truncate
• 另外，在企业级开发中，实际上都是进行逻辑删除（将数据进行删除标识处理）、而并不进行物理上的删除
• 在实际生产环境中，一般情况下删除业务处理（过渡表）中的数据
• 在实际企业开发、维护过程中，不管使用 delete 还是 truncate 命令前，都要考虑数据的备份