SQL 中的 TRUNCATE 和 DELETE：了解差异

了解TRUNCATE和DELETE语句在 SQL 中的工作原理、它们有何不同以及何时使用其中一种更好。

使用数据库表时，您可能需要删除行的子集或所有行。要从数据库表中删除行，您可以根据您的用例使用 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表。

 -- Create the tasks table
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表，让我们向其中插入记录。

 -- Inserting multiple records into the tasks table
INSERT INTO tasks (title, due_date, status, assignee)
VALUES
    ('Task 1', '2023-08-10', 'Pending', 'John'),
    ('Task 2', '2023-08-11', 'In Progress', 'Jane'),
    ('Task 3', '2023-08-12', 'Completed', 'Mike'),
    ('Task 4', '2023-08-13', 'Pending', 'Alice'),
    ('Task 5', '2023-08-14', 'In Progress', 'Bob'),
    ('Task 6', '2023-08-15', 'Completed', 'Emily'),
    ('Task 7', '2023-08-16', 'Pending', 'David'),
    ('Task 8', '2023-08-17', 'In Progress', 'Olivia'),
    ('Task 9', '2023-08-18', 'Pending', 'Daniel'),
    ('Task 10', '2023-08-19', 'Completed', 'Sophia'),
    ('Task 11', '2023-08-20', 'Pending', 'Matthew'),
    ('Task 12', '2023-08-21', 'In Progress', 'Ava'),
    ('Task 13', '2023-08-22', 'Completed', 'William'),
    ('Task 14', '2023-08-23', 'Pending', 'Ella'),
    ('Task 15', '2023-08-24', 'In Progress', 'James'),
    ('Task 16', '2023-08-25', 'Completed', 'Lily'),
    ('Task 17', '2023-08-26', 'Pending', 'Benjamin'),
    ('Task 18', '2023-08-27', 'In Progress', 'Mia'),
    ('Task 19', '2023-08-28', 'Pending', 'Henry'),
    ('Task 20', '2023-08-29', 'Completed', 'Isabella');

运行插入语句时，您应该看到类似于以下内容的输出：

 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)

此外，从任务表检索数据的 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 语句可以更好地控制要删除的记录。

但是当您使用不带 WHERE 子句的 DELETE 语句时会发生什么？🤔

 DELETE FROM table_name;

执行如图所示的 DELETE 语句将删除数据库表中的所有行。

如果 DELETE 语句或一组 DELETE 语句是未提交事务的一部分，您可以回滚更改。但是，我们建议您将数据备份到其他位置。

使用 SQL DELETE 的示例

现在让我们看看 SQL 删除语句是如何工作的。

我删除了tasks表中的所有记录。因此，您可以重新执行（之前执行过的）INSERT 语句来插入记录。

 -- Inserting multiple records into the tasks table
INSERT INTO tasks (title, due_date, status, assignee)
VALUES
    ('Task 1', '2023-08-10', 'Pending', 'John'),
    ('Task 2', '2023-08-11', 'In Progress', 'Jane'),
    ('Task 3', '2023-08-12', 'Completed', 'Mike'),
    ...
    ('Task 18', '2023-08-27', 'In Progress', 'Mia'),
    ('Task 19', '2023-08-28', 'Pending', 'Henry'),
    ('Task 20', '2023-08-29', 'Completed', 'Isabella');

首先，我们使用带有 WHERE 子句的 DELETE 语句。以下查询删除状态为“Complete”的所有行。

 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 | Task 1  | 2023-08-10 | Pending     | John     |
|       2 | Task 2  | 2023-08-11 | In Progress | Jane     |
|       4 | Task 4  | 2023-08-13 | Pending     | Alice    |
|       5 | Task 5  | 2023-08-14 | In Progress | Bob      |
|       7 | Task 7  | 2023-08-16 | Pending     | David    |
|       8 | Task 8  | 2023-08-17 | In Progress | Olivia   |
|       9 | Task 9  | 2023-08-18 | Pending     | Daniel   |
|      11 | Task 11 | 2023-08-20 | Pending     | Matthew  |
|      12 | Task 12 | 2023-08-21 | In Progress | Ava      |
|      14 | Task 14 | 2023-08-23 | Pending     | Ella     |
|      15 | Task 15 | 2023-08-24 | In Progress | James    |
|      17 | Task 17 | 2023-08-26 | Pending     | Benjamin |
|      18 | Task 18 | 2023-08-27 | In Progress | Mia      |
|      19 | Task 19 | 2023-08-28 | Pending     | Henry    |
+---------+---------+------------+-------------+----------+
14 rows in set (0.00 sec)

以下 DELETE 语句删除表中所有剩余的 14 条记录。

 DELETE FROM tasks;
Query OK, 14 rows affected (0.20 sec)

现在任务表是空的。

 SELECT * FROM tasks;
Empty set (0.00 sec) 

SQL DROP 语句

到目前为止，我们已经了解了以下内容：

• TRUNCATE 语句删除表中的所有行。
• 不带 WHERE 子句的 DELETE 语句会删除表中的所有记录。

但是，TRUNCATE 和 DELETE 语句不会删除表。如果要从数据库中删除表，可以使用DROP TABLE命令，如下所示：

 DROP TABLE table_name;

接下来，让我们从数据库中删除任务表。

 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 或 DELETE 语句来执行此操作。
• TRUNCATE 语句的语法为： TRUNCATE TABLE table_name; table_name指定的表中的所有行都将被删除，但表本身不会被删除。
• DELETE 语句的语法为： DELETE FROM table_name WHERE condition;这将删除谓词condition为 true 的行。
• 执行不带WHERE 子句的 SQL DELETE 语句将删除表中的所有行。因此，从功能上讲，这实现了与 SQL TRUNCATE 语句相同的结果。
• TRUNCATE 执行在处理大型表时特别快，因为它不会扫描整个表。因此，如果需要删除大型数据库表中的所有行，则 truncate 效率更高。
• 如果需要根据某些条件删除行的子集，可以使用 SQL DELETE 语句。

查看此 SQL 备忘单，快速了解常用的 SQL 命令。