SQL 中 TRUNCATE 和 DELETE 的工作原理及差异
在操作数据库表格时，有时你需要删除部分或全部行。为了实现这个目标，SQL 提供了 TRUNCATE 和 DELETE 两种语句，它们在不同的场景下发挥作用。本文将深入探讨这两种语句的运作方式，并分析在何种情况下应该优先选择使用哪一种。
在详细讨论之前，我们先简要回顾 SQL 的几个子集：
- 数据定义语言 (DDL)：用于创建和管理数据库对象，如表格。
CREATE、DROP和TRUNCATE等语句都属于 DDL。 - 数据操作语言 (DML)：用于操作数据库对象中的数据。DML 语句用于执行数据的创建、读取、更新和删除等操作。
- 数据查询语言 (DQL)：用于从数据库表格中检索数据。所有的
SELECT语句都属于 DQL 子集。
如何使用 SQL TRUNCATE 语句
SQL TRUNCATE 语句的语法
TRUNCATE 语句的基本语法如下：
TRUNCATE TABLE table_name;
执行上述命令会删除 table_name 指定表格中的所有行，但不会删除表格本身。 TRUNCATE 操作不会扫描表格中的每一条记录，因此在处理大型数据库表格时速度很快。
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: 任务的状态，可以是“待处理”、“进行中”或“已完成”。 默认值设置为“待处理”。assignee: 特定任务的负责人。
现在，我们向 tasks 表格插入一些记录：
-- 向 tasks 表格插入多条记录
INSERT INTO tasks (title, due_date, status, assignee)
VALUES
('任务 1', '2023-08-10', 'Pending', '张三'),
('任务 2', '2023-08-11', 'In Progress', '李四'),
('任务 3', '2023-08-12', 'Completed', '王五'),
('任务 4', '2023-08-13', 'Pending', '赵六'),
('任务 5', '2023-08-14', 'In Progress', '钱七'),
('任务 6', '2023-08-15', 'Completed', '孙八'),
('任务 7', '2023-08-16', 'Pending', '周九'),
('任务 8', '2023-08-17', 'In Progress', '吴十'),
('任务 9', '2023-08-18', 'Pending', '郑十一'),
('任务 10', '2023-08-19', 'Completed', '冯十二'),
('任务 11', '2023-08-20', 'Pending', '陈十三'),
('任务 12', '2023-08-21', 'In Progress', '楚十四'),
('任务 13', '2023-08-22', 'Completed', '卫十五'),
('任务 14', '2023-08-23', 'Pending', '蒋十六'),
('任务 15', '2023-08-24', 'In Progress', '沈十七'),
('任务 16', '2023-08-25', 'Completed', '韩十八'),
('任务 17', '2023-08-26', 'Pending', '杨十九'),
('任务 18', '2023-08-27', 'In Progress', '朱二十'),
('任务 19', '2023-08-28', 'Pending', '秦二十一'),
('任务 20', '2023-08-29', 'Completed', '尤二十二');
执行插入语句后，您会看到类似下面的输出：
Query OK, 20 rows affected (0.18 sec) Records: 20 Duplicates: 0 Warnings: 0
现在执行 TRUNCATE TABLE 命令以删除 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 语句的语法
DELETE 语句的一般语法如下：
DELETE FROM table_name WHERE condition;
WHERE 子句中的条件是一个谓词，用于确定哪些行应该被删除。DELETE 语句会删除所有谓词为 True 的行。
因此，DELETE 语句允许你更精细地控制要删除的记录。
如果使用不带 WHERE 子句的 DELETE 语句会发生什么呢？🤔
DELETE FROM table_name;
执行如上所示的 DELETE 语句将删除数据库表格中的所有行。
如果 DELETE 语句或一组 DELETE 语句是未提交事务的一部分，那么可以回滚这些更改。 然而，建议您始终将数据备份到其他位置。
SQL DELETE 用法示例
现在，我们来实际看一下 DELETE 语句的操作。
我们已经删除了 tasks 表格中的所有记录。您可以重新运行之前用过的 INSERT 语句来插入记录：
-- 向 tasks 表格插入多条记录
INSERT INTO tasks (title, due_date, status, assignee)
VALUES
('任务 1', '2023-08-10', 'Pending', '张三'),
('任务 2', '2023-08-11', 'In Progress', '李四'),
('任务 3', '2023-08-12', 'Completed', '王五'),
...
('任务 18', '2023-08-27', 'In Progress', '朱二十'),
('任务 19', '2023-08-28', 'Pending', '秦二十一'),
('任务 20', '2023-08-29', 'Completed', '尤二十二');
首先，我们使用带有 WHERE 子句的 DELETE 语句。以下查询删除所有状态为“已完成”的行：
DELETE FROM tasks WHERE status="Completed"; Query OK, 6 rows affected (0.14 sec)
现在执行以下 SELECT 查询：
SELECT * FROM tasks;
你会看到当前有 14 行：
+---------+---------+------------+-------------+----------+ | task_id | title | due_date | status | assignee | +---------+---------+------------+-------------+----------+ | 1 | 任务 1 | 2023-08-10 | Pending | 张三 | | 2 | 任务 2 | 2023-08-11 | In Progress | 李四 | | 4 | 任务 4 | 2023-08-13 | Pending | 赵六 | | 5 | 任务 5 | 2023-08-14 | In Progress | 钱七 | | 7 | 任务 7 | 2023-08-16 | Pending | 周九 | | 8 | 任务 8 | 2023-08-17 | In Progress | 吴十 | | 9 | 任务 9 | 2023-08-18 | Pending | 郑十一 | | 11 | 任务 11 | 2023-08-20 | Pending | 陈十三 | | 12 | 任务 12 | 2023-08-21 | In Progress | 楚十四 | | 14 | 任务 14 | 2023-08-23 | Pending | 蒋十六 | | 15 | 任务 15 | 2023-08-24 | In Progress | 沈十七 | | 17 | 任务 17 | 2023-08-26 | Pending | 杨十九 | | 18 | 任务 18 | 2023-08-27 | In Progress | 朱二十 | | 19 | 任务 19 | 2023-08-28 | Pending | 秦二十一 | +---------+---------+------------+-------------+----------+ 14 rows in set (0.00 sec)
执行以下 DELETE 语句会删除表格中剩余的所有 14 条记录：
DELETE FROM tasks; Query OK, 14 rows affected (0.20 sec)
现在 tasks 表格是空的：
SELECT * FROM tasks; Empty set (0.00 sec)
SQL DROP 语句
到目前为止，我们已经了解到：
TRUNCATE语句会删除表格中的所有行。DELETE语句（不带WHERE子句）会删除表格中的所有记录。
然而，TRUNCATE 和 DELETE 语句不会删除表格。如果要从数据库中删除表格，可以使用 DROP TABLE 命令，如下所示：
DROP TABLE table_name;
现在我们从数据库中删除 tasks 表格：
mysql> DROP TABLE tasks; Query OK, 0 rows affected (0.43 sec)
你会发现 SHOW TABLES; 返回一个空集（因为我们已经删除了数据库中唯一的表格）：
mysql> SHOW TABLES; Empty set (0.00 sec)
何时在 SQL 中使用 TRUNCATE 与 DELETE
| 特性 | TRUNCATE |
DELETE |
| 语法 | TRUNCATE TABLE table_name; |
带有 WHERE 子句: DELETE FROM table_name WHERE condition;不带 WHERE 子句: DELETE TABLE table_name; |
| SQL 子集 | 数据定义语言 (DDL) | 数据操作语言 (DML) |
| 作用 | 删除数据库表格中的所有行。 | 当不带 WHERE 子句运行时，DELETE 语句会删除数据库表格中的所有记录。 |
| 性能 | 处理大型表格时比 DELETE 语句更高效。 |
比 TRUNCATE 语句效率低。 |
总结：
- 当需要从大型数据库表格中删除所有行时，使用
TRUNCATE语句。 - 要根据特定条件删除行的子集，请使用
DELETE语句。
总结
让我们以总结来结束我们的讨论：
- 当使用数据库表格时，可能需要删除特定表格中的部分或所有行。为此，可以使用
TRUNCATE或DELETE语句。 TRUNCATE语句的语法是：TRUNCATE TABLE table_name;它删除table_name指定的表格中的所有行，但不删除表格本身。DELETE语句的语法是：DELETE FROM table_name WHERE condition;这会删除谓词条件为真的行。- 执行不带
WHERE子句的 SQLDELETE语句会删除表格中的所有行。因此，从功能上来说，它实现了与 SQLTRUNCATE语句相同的结果。 - 在处理较大的表格时，执行
TRUNCATE特别快，因为它不会扫描整个表格。因此，当需要删除大型数据库表格中的所有行时，执行TRUNCATE会更有效。 - 当需要根据特定条件删除行的子集时，可以使用 SQL
DELETE语句。
要快速查看常用的 SQL 命令，请查看这份 SQL 速查表。