实战2：如何使用软删除（逻辑删除）

1. 前言

在 SQL Delete 一节中，我们介绍了 SQL 的基本删除功能，今天我们将以分类和实战的角度来进一步学习 SQL 的删除。

删除是一个很危险的操作，试想一下如果开发人员不慎操作失误，误删了一些数据，在数据未备份的情况下，该数据无法恢复，造成了损失绝对是致命的。

道路千万条，安全第一条。因此为了保证数据的安全性，在真正的企业级应用中都会默认的采用逻辑删除（软删除）的方式来处理数据删除。

那么既然软删除使用如此普遍，它究竟如何使用了？

2. 软删除与硬删除

从逻辑的角度上来说，我们可以把删除分为两大类：软删除和硬删除。

其实，在很多数据库中，硬删除也并非直接将数据从磁盘上丢弃，而是通过标志位将该数据标志为已删除。由于数据库是按页来组织存储数据的，如果硬删除直接将数据从页中丢弃的话，可能会引起数据页的分裂和重组，这样会大幅降低数据库的性能。

但是硬删除的数据可能会被后面添加的数据覆盖，所以磁盘上也会真正的丢失掉该数据。

2.1 软删除实施方案

软删除是在程序层面上的删除，在数据库中数据仍然存在，软删除常见的有两种实施方案：

1. 整型字段标志位：在数据表增加一个deleted字段来表示记录是否被删除，0表示未删除，1表示已被删除。

2. 日期字段标志位：在数据表增加一个delete_time字段来表示记录是否被删除，null表示未删除，有日期表示已被删除。

3. 实践

接下来，我们以实践的角度来看看软删除是如何实现的。

3.1 整型字段标志位

我们仍然以imooc_user表为例，并添加deleted作为逻辑标志位。如下：

DROP TABLE IF EXISTS imooc_user;
CREATE TABLE imooc_user
(
  id int PRIMARY KEY,
  username varchar(20),
  age int,
  -- 以 deleted 字段作为软删除标记位
  deleted int NOT NULL DEFAULT 0
);

3.1.1 整型软删除操作

有了标志位后，我们添加一条记录：

INSERT INTO imooc_user(id,username,age) VALUES (1,'pedro',23);

添加成功后，记录如下：

+----+----------+-----+---------+
| id | username | age | deleted |
+----+----------+-----+---------+
| 1  | pedro    | 23  | 0       |
+----+----------+-----+---------+

其中deleted字段为0表示记录正常，当有了标志位后，我们每次查询数据时都需要添加上标志位，即：

SELECT * FROM imooc_user WHERE id = 1 AND deleted = 0;

当要删除该记录时，我们不能直接通过 Delete 来删除了，而是通过 Update：

UPDATE imooc_user SET deleted = 1 WHERE id = 1;

软删除后，我们再次通过查询语句SELECT * FROM imooc_user WHERE id = 1 AND deleted = 0;，发现已经查不到该记录了。于是，记录在逻辑上已经删除了，可它仍在数据库中。

这样，就能在“删除”数据的情况下保证数据的安全性。

3.2 日期字段标志位

修改一下imooc_user表：

DROP TABLE IF EXISTS imooc_user;
CREATE TABLE imooc_user
(
  id int PRIMARY KEY,
  username varchar(20),
  age int,
   -- 以 delete_time 字段作为软删除标记位
  delete_time timestamp DEFAULT NULL
);

3.2.1 日期软删除操作

整体上与整形字段标志位相同，如下：

添加记录：

INSERT INTO imooc_user(id,username,age) VALUES (1,'pedro',23);

查询记录：

SELECT * FROM imooc_user WHERE id = 1 AND delete_time IS NULL;

删除记录：

UPDATE imooc_user SET delete_time = NOW() WHERE id = 1;

你可能会觉得这有些麻烦，没关系不少好用的 ORM 库，如 Hibernate，Sequelize 均已经提供了软删除功能，你只需配置一下，就能直接调用API来达到软删除的效果。

4. 小结

• 若无特殊需求，比如需要记录删除的具体时间，我们推荐使用整形字段软删除，性能较好。
• 软删除不会改变数据页的分布和排列，而硬删除可能会破坏数据页，从而降低查询性能，因此在磁盘容量允许的情况下，首选软删除。