在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备忘单。