實戰(zhàn)2：如何使用軟刪除（邏輯刪除）

1. 前言

在 SQL Delete 一節(jié)中，我們介紹了 SQL 的基本刪除功能，今天我們將以分類和實戰(zhàn)的角度來進一步學習 SQL 的刪除。

刪除是一個很危險的操作，試想一下如果開發(fā)人員不慎操作失誤，誤刪了一些數(shù)據(jù)，在數(shù)據(jù)未備份的情況下，該數(shù)據(jù)無法恢復，造成了損失絕對是致命的。

道路千萬條，安全第一條。因此為了保證數(shù)據(jù)的安全性，在真正的企業(yè)級應用中都會默認的采用邏輯刪除（軟刪除）的方式來處理數(shù)據(jù)刪除。

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

2. 軟刪除與硬刪除

從邏輯的角度上來說，我們可以把刪除分為兩大類：軟刪除和硬刪除。

其實，在很多數(shù)據(jù)庫中，硬刪除也并非直接將數(shù)據(jù)從磁盤上丟棄，而是通過標志位將該數(shù)據(jù)標志為已刪除。由于數(shù)據(jù)庫是按頁來組織存儲數(shù)據(jù)的，如果硬刪除直接將數(shù)據(jù)從頁中丟棄的話，可能會引起數(shù)據(jù)頁的分裂和重組，這樣會大幅降低數(shù)據(jù)庫的性能。

但是硬刪除的數(shù)據(jù)可能會被后面添加的數(shù)據(jù)覆蓋，所以磁盤上也會真正的丟失掉該數(shù)據(jù)。

2.1 軟刪除實施方案

軟刪除是在程序層面上的刪除，在數(shù)據(jù)庫中數(shù)據(jù)仍然存在，軟刪除常見的有兩種實施方案：

1. 整型字段標志位：在數(shù)據(jù)表增加一個deleted字段來表示記錄是否被刪除，0表示未刪除，1表示已被刪除。

2. 日期字段標志位：在數(shù)據(jù)表增加一個delete_time字段來表示記錄是否被刪除，null表示未刪除，有日期表示已被刪除。

3. 實踐

接下來，我們以實踐的角度來看看軟刪除是如何實現(xiàn)的。

3.1 整型字段標志位

我們?nèi)匀灰?code>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表示記錄正常，當有了標志位后，我們每次查詢數(shù)據(jù)時都需要添加上標志位，即：

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;，發(fā)現(xiàn)已經(jīng)查不到該記錄了。于是，記錄在邏輯上已經(jīng)刪除了，可它仍在數(shù)據(jù)庫中。

這樣，就能在“刪除”數(shù)據(jù)的情況下保證數(shù)據(jù)的安全性。

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 均已經(jīng)提供了軟刪除功能，你只需配置一下，就能直接調(diào)用API來達到軟刪除的效果。

4. 小結

• 若無特殊需求，比如需要記錄刪除的具體時間，我們推薦使用整形字段軟刪除，性能較好。
• 軟刪除不會改變數(shù)據(jù)頁的分布和排列，而硬刪除可能會破壞數(shù)據(jù)頁，從而降低查詢性能，因此在磁盤容量允許的情況下，首選軟刪除。