在SQL中的TRUNCATE和DELETE：了解差异
学习sql中truncate和delete语句的工作原理，它们之间的区别，以及何时应该优先使用其中之一。
在使用数据库表时，您可能需要删除一部分行或全部行。要从数据库表中删除行，可以根据用例使用sql truncate或delete语句。
在本教程中，我们将更详细地了解每个语句的工作原理，并决定何时应该优先使用truncate而不是delete，反之亦然。
在我们继续之前，有必要回顾以下sql子集：
- 数据定义语言（ddl）语句用于创建和管理数据库对象，如表。sql的
create，drop和truncate语句是ddl语句的示例。 - 数据操作语言（dml）语句用于操作数据库对象中的数据。dml语句用于执行创建、读取、更新和删除记录的操作。
- 数据查询语言（dql）语句用于从数据库表中检索数据。所有的
select语句都属于dql子集。
如何使用sql truncate语句
sql truncate语句的语法
使用sql truncate语句的语法如下：
truncate table table_name;运行以上truncate命令会删除由table_name指定的表中的所有行，并且不会删除表本身。
截断操作不会扫描表中的所有记录。因此，当处理大型数据库表时，速度相对较快。
sql truncate使用示例
📑 注意：如果您的机器上安装有mysql，您可以使用mysql命令行客户端进行编码。您也可以在您选择的其他dbms中进行跟随，比如postgresql。
让我们首先创建一个要使用的数据库：
mysql> create database db1;
query ok, 1 row affected (1.50 sec)然后选择我们刚创建的数据库：
mysql> use db1;
database changed下一步是创建一个数据库表。运行以下create table语句来创建一个简单的tasks表：
-- 创建tasks表
create table tasks (
task_id int auto_increment primary key,
title varchar(255) not null,
due_date date,
status enum('pending', 'in progress', 'completed') default 'pending',
assignee varchar(100)
);在这个例子中，tasks表有以下列：
task_id：每个任务的自动递增唯一标识符。title：任务的标题或名称，限制为255个字符。due_date：任务的截止日期，表示为日期。status：任务的状态，可以是'pending'，'in progress'或'completed'。默认值设置为'pending'。assignee：特定任务的被分配人员。
现在我们已经创建了tasks表，让我们向其中插入记录：
-- 向tasks表中插入多条记录
insert into tasks (title, due_date, status, assignee)
values
('任务1', '2023-08-10', '待处理', '约翰'),
('任务2', '2023-08-11', '进行中', '简'),
('任务3', '2023-08-12', '已完成', '迈克'),
('任务4', '2023-08-13', '待处理', '艾丽斯'),
('任务5', '2023-08-14', '进行中', '鲍勃'),
('任务6', '2023-08-15', '已完成', '艾米莉'),
('任务7', '2023-08-16', '待处理', '大卫'),
('任务8', '2023-08-17', '进行中', '奥利维亚'),
('任务9', '2023-08-18', '待处理', '丹尼尔'),
('任务10', '2023-08-19', '已完成', '索菲亚'),
('任务11', '2023-08-20', '待处理', '马修'),
('任务12', '2023-08-21', '进行中', '艾娃'),
('任务13', '2023-08-22', '已完成', '威廉'),
('任务14', '2023-08-23', '待处理', '艾拉'),
('任务15', '2023-08-24', '进行中', '詹姆斯'),
('任务16', '2023-08-25', '已完成', '莉莉'),
('任务17', '2023-08-26', '待处理', '本杰明'),
('任务18', '2023-08-27', '进行中', '米娅'),
('任务19', '2023-08-28', '待处理', '亨利'),
('任务20', '2023-08-29', '已完成', '伊莎贝拉');运行插入语句后，您应该看到类似的输出：
query ok, 20 rows affected (0.18 sec)
records: 20 duplicates: 0 warnings: 0现在运行truncate表命令从tasks表中删除所有记录：
truncate table tasks;
query ok, 0 rows affected (0.72 sec)这样做会删除所有记录，而不是表。您可以通过运行show tables;来验证：
show tables;+---------------+
| tables_in_db1 |
+---------------+
| tasks |
+---------------+
1 row in set (0.00 sec)从tasks表中检索数据的select查询将返回一个空集：
select * from tasks;
empty set (0.00 sec)如何使用sql delete语句
sql delete语句的语法
使用sql delete语句的一般语法如下：
delete from table_name
where condition;where子句中的condition是确定应该删除哪些行的谓词。delete语句删除谓词为true的所有行。
因此，delete语句允许您更好地控制删除哪些记录。
但是，如果在delete语句中没有where子句会发生什么呢？🤔
delete from table_name;如所示运行delete语句会删除数据库表中的所有行。
如果delete语句或一组delete语句是一个未提交的事务的一部分，则可以回滚更改。但是，建议在其他地方备份数据。
sql delete用法示例
现在让我们看看sql delete语句的实际操作。
我们已经从tasks表中删除了所有记录。因此，您可以重新运行insert语句（我们之前运行的）来插入记录：
-- 向tasks表中插入多条记录
insert into tasks (title, due_date, status, assignee)
values
('任务1', '2023-08-10', '待处理', '约翰'),
('任务2', '2023-08-11', '进行中', '简'),
('任务3', '2023-08-12', '已完成', '迈克'),
...
('任务18', '2023-08-27', '进行中', '米娅'),
('任务19', '2023-08-28', '待处理', '亨利'),
('任务20', '2023-08-29', '已完成', '伊莎贝拉');首先，让我们使用带有where子句的delete语句。以下查询删除所有状态为“已完成”的行：
delete from tasks where status = '已完成';
查询成功，受影响的行数为6（0.14秒）现在运行以下select查询：
select * from tasks;您将看到目前有14行：
+---------+---------+------------+-------------+----------+
| task_id | title | due_date | status | assignee |
+---------+---------+------------+-------------+----------+
| 1 | 任务1 | 2023-08-10 | 待处理 | 约翰 |
| 2 | 任务2 | 2023-08-11 | 进行中 | 简 |
| 4 | 任务4 | 2023-08-13 | 待处理 | 爱丽丝 |
| 5 | 任务5 | 2023-08-14 | 进行中 | 鲍勃 |
| 7 | 任务7 | 2023-08-16 | 待处理 | 大卫 |
| 8 | 任务8 | 2023-08-17 | 进行中 | 奥利维亚 |
| 9 | 任务9 | 2023-08-18 | 待处理 | 丹尼尔 |
| 11 | 任务11 | 2023-08-20 | 待处理 | 马修 |
| 12 | 任务12 | 2023-08-21 | 进行中 | 阿娃 |
| 14 | 任务14 | 2023-08-23 | 待处理 | 艾拉 |
| 15 | 任务15 | 2023-08-24 | 进行中 | 詹姆斯 |
| 17 | 任务17 | 2023-08-26 | 待处理 | 本杰明 |
| 18 | 任务18 | 2023-08-27 | 进行中 | 米娅 |
| 19 | 任务19 | 2023-08-28 | 待处理 | 亨利 |
+---------+---------+------------+-------------+----------+
共14行（0.00秒）运行以下delete语句将删除表中剩余的14条记录：
delete from tasks;
查询成功，受影响的行数为14（0.20秒）现在，tasks表为空：
select * from tasks;
空集（0.00秒）sql的drop语句
到目前为止，我们已经学习到：
- truncate语句会删除表中的所有行。
- delete语句（不带where子句）会删除表中的所有记录。
但是，truncate和delete语句不会删除表。如果您要从数据库中删除表，请使用drop table命令，如下所示：
drop table 表名;现在让我们从数据库中删除tasks表：
mysql> drop table tasks;
查询成功，受影响的行数为0（0.43秒）您将看到show tables;返回一个空集（因为我们已经删除了数据库中唯一的表）：
mysql> show tables;
空集（0.00秒）何时在sql中使用truncate和delete
| 特性 | 截断 | 删除 |
|---|---|---|
| 语法 | 截断表 table_name; |
带有条件的： 从表 table_name 中删除 where condition;没有条件的： 删除表 table_name; |
| sql子集 | 数据定义语言 (ddl) | 数据操作语言 (dml) |
| 效果 | 删除数据库表中的所有行。 | 当没有where条件时，删除语句会删除数据库表中的所有记录。 |
| 性能 | 在处理大型表时比删除语句更高效。 | 比截断语句效率低。 |
总结：
- 当您需要从大型数据库表中删除所有行时，请使用截断语句。
- 要根据特定条件删除一部分行，请使用删除语句。
总结
让我们用一个总结来结束我们的讨论：
- 在使用数据库表时，您可能希望删除一部分行或特定表中的所有行。为此，您可以使用截断或删除语句。
- 截断语句的语法为：
截断表 table_name;。它会删除由table_name指定的表中的所有行，但不会删除表本身。 - 删除语句的语法为：
从表 table_name 中删除 where condition;。它会删除谓词condition为真的行。 - 在不带where子句的情况下运行sql删除语句会删除表中的所有行。因此，在功能上，这与sql截断语句实现相同的结果。
- 在处理较大的表时，运行截断语句特别快，因为它不会扫描整个表。因此，当您需要删除大型数据库表中的所有行时，运行截断语句可能更高效。
- 当您需要删除一部分行（基于特定条件）时，可以使用sql删除语句。
要快速查看常用的sql命令，请查看此 sql备忘单。
