SQL 软删除字段该如何设计

在业务开发中，为了避免误删数据后无法恢复，同时保留数据的历史痕迹，很多场景会采用软删除方案，通过特定字段标记数据是否被删除，而不是直接执行DELETE语句物理删除数据，软删除字段的设计直接影响后续查询效率和数据维护成本。

软删除字段的基础类型选择

最常见的软删除字段类型是布尔型和时间戳型，两种类型各有适用场景，需要根据业务需求选择。

布尔型软删除字段

布尔型字段一般用is_deleted命名，取值为0和1，0代表未删除，1代表已删除。这种类型结构简单，查询逻辑清晰，适合只需要判断数据是否被删除，不需要记录删除时间的场景。

创建表时示例如下：

-- 布尔型软删除字段设计示例
CREATE TABLE user_info (
    id INT PRIMARY KEY AUTO_INCREMENT,
    username VARCHAR(50) NOT NULL,
    email VARCHAR(100),
    -- 软删除字段，0未删除 1已删除，默认0
    is_deleted TINYINT NOT NULL DEFAULT 0,
    create_time DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP
);

时间戳型软删除字段

时间戳型字段一般用deleted_at命名，取值为NULL或者具体的删除时间，NULL代表未删除，非NULL值代表删除的时间点。这种类型可以同时记录删除时间，适合需要追溯删除操作的场景。

创建表时示例如下：

-- 时间戳型软删除字段设计示例
CREATE TABLE order_info (
    id INT PRIMARY KEY AUTO_INCREMENT,
    order_no VARCHAR(32) NOT NULL,
    user_id INT NOT NULL,
    -- 软删除字段，NULL未删除，非NULL为删除时间
    deleted_at DATETIME NULL,
    create_time DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP
);

软删除字段的默认值与约束设计

软删除字段需要设置合理的默认值和约束，避免无效数据写入。

• 布尔型字段默认值必须设为0，保证新插入的数据默认是未删除状态，避免遗漏设置导致数据被误判为已删除。
• 时间戳型字段默认值必须设为NULL，同样保证新数据默认未删除，同时可以设置字段为NULL约束，避免写入无效的非时间值。
• 如果业务要求软删除后不能重复插入相同唯一键的数据，需要结合唯一索引做适配，避免唯一键冲突。

软删除字段的索引设计

软删除字段的索引设计直接影响查询性能，尤其是数据量较大的表。

如果查询时经常过滤未删除的数据，比如查询用户列表时默认排除已删除用户，可以为软删除字段单独创建索引，或者将软删除字段加入组合索引的前缀。

示例如下：

-- 为布尔型软删除字段创建索引
CREATE INDEX idx_user_is_deleted ON user_info(is_deleted);

-- 为时间戳型软删除字段创建索引
CREATE INDEX idx_order_deleted_at ON order_info(deleted_at);

-- 组合索引示例，查询时经常按用户ID和未删除状态过滤
CREATE INDEX idx_user_id_is_deleted ON user_info(user_id, is_deleted);

查询逻辑适配

使用软删除后，所有查询都需要适配过滤逻辑，避免查询到已删除的数据。

普通查询适配

布尔型字段的查询需要加上is_deleted = 0的条件：

-- 查询未删除的用户列表
SELECT id, username, email FROM user_info WHERE is_deleted = 0;

时间戳型字段的查询需要加上deleted_at IS NULL的条件：

-- 查询未删除的订单列表
SELECT id, order_no, user_id FROM order_info WHERE deleted_at IS NULL;

更新和删除操作适配

更新操作需要避免修改已删除的数据，删除操作需要改为更新软删除字段的值：

-- 布尔型软删除的删除操作，实际是更新字段
UPDATE user_info SET is_deleted = 1 WHERE id = 10 AND is_deleted = 0;

-- 时间戳型软删除的删除操作，更新为当前时间
UPDATE order_info SET deleted_at = CURRENT_TIMESTAMP WHERE id = 20 AND deleted_at IS NULL;

常见设计误区与规避方法

误区1：软删除字段使用字符串类型存储删除状态，比如用"yes"和"no"标记，会增加存储成本，查询时效率也更低，建议统一用数值型或时间戳型。

误区2：软删除后不清理关联数据，比如用户删除后，用户的订单数据仍然存在且关联已删除的用户ID，需要业务层做关联数据的处理，或者设计级联软删除逻辑。

误区3：所有表都加软删除字段，部分临时表、日志表不需要保留删除痕迹，直接物理删除即可，不需要额外增加字段和维护成本。

不同场景的设计建议

可以通过下表快速选择适合的软删除字段设计方案：

SQL软删除字段设计数据库修改时间：2026-06-25 09:30:37