首页 慕课教程 SQL 入门教程 SQL 入门教程 实战2:如何使用软删除(逻辑删除)

实战2:如何使用软删除(逻辑删除)

1. 前言

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

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

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

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

2. 软删除与硬删除

从逻辑的角度上来说,我们可以把删除分为两大类:软删除硬删除

说明 描述
软删除 在逻辑上删除,数据依然存在 会通过数据表上的某一字段来表示,如deleted
硬删除 在物理上删除,数据被丢弃 无额外支持,直接被删掉了

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

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

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. 小结

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