软删除与逻辑删除

软删除（逻辑删除）将数据标记为已删除而非物理移除，支持数据恢复、审计追溯和关联数据完整性。本文梳理 MyBatis 中软删除的实现方案。

物理删除 vs 逻辑删除

逻辑删除表设计

CREATE TABLE user (
  id BIGINT PRIMARY KEY AUTO_INCREMENT,
  username VARCHAR(50) NOT NULL,
  email VARCHAR(100),
  deleted TINYINT DEFAULT 0 COMMENT '0:正常 1:已删除',
  deleted_at DATETIME COMMENT '删除时间',
  deleted_by VARCHAR(50) COMMENT '删除操作人',
  created_at DATETIME DEFAULT CURRENT_TIMESTAMP,
  updated_at DATETIME DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP
);

建议记录 deleted_at 和 deleted_by 字段，满足审计追溯需求。

拦截器实现全局逻辑删除

自定义 Interceptor

@Intercepts({
    @Signature(type = Executor.class, method = "query", 
               args = {MappedStatement.class, Object.class, RowBounds.class, ResultHandler.class}),
    @Signature(type = Executor.class, method = "update", 
               args = {MappedStatement.class, Object.class})
})
public class LogicalDeleteInterceptor implements Interceptor {
  
  private static final String DELETED_COLUMN = "deleted";
  
  @Override
  public Object intercept(Invocation invocation) throws Throwable {
    MappedStatement ms = (MappedStatement) invocation.getArgs()[0];
    
    // 跳过已标注逻辑删除的语句
    if (isLogicalDeleteStatement(ms)) {
      return invocation.proceed();
    }
    
    // SELECT: 追加 deleted = 0 条件
    if (isQueryMethod(ms)) {
      rewriteQuery(invocation, ms);
    }
    
    // DELETE: 转换为 UPDATE
    if (isDeleteMethod(ms)) {
      rewriteDelete(invocation, ms);
    }
    
    return invocation.proceed();
  }
  
  private boolean isQueryMethod(MappedStatement ms) {
    return ms.getSqlCommandType() == SqlCommandType.SELECT;
  }
  
  private boolean isDeleteMethod(MappedStatement ms) {
    return ms.getSqlCommandType() == SqlCommandType.DELETE;
  }
  
  private void rewriteQuery(Invocation invocation, MappedStatement ms) {
    // 通过插件修改 BoundSql，追加 WHERE deleted = 0
    BoundSql boundSql = ms.getBoundSql(invocation.getArgs()[1]);
    String originalSql = boundSql.getSql();
    
    // 简单场景：追加条件（生产建议使用 SQL 解析库）
    String newSql = appendDeletedCondition(originalSql);
    
    // 使用反射更新 BoundSql
    MetaObject metaObject = SystemMetaObject.forObject(boundSql);
    metaObject.setValue("sql", newSql);
  }
  
  private String appendDeletedCondition(String sql) {
    String lowerSql = sql.toLowerCase();
    
    // 已有 WHERE 条件
    if (lowerSql.contains("where")) {
      return sql.replaceAll("(?i)\\s+WHERE\\s+", " WHERE deleted = 0 AND ");
    }
    // 无 WHERE 条件
    return sql.replaceAll("(?i)\\s+FROM\\s+(\\w+)", " FROM $1 WHERE deleted = 0 ");
  }
  
  private void rewriteDelete(Invocation invocation, MappedStatement ms) {
    // 将 DELETE 转换为 UPDATE SET deleted = 1
    BoundSql boundSql = ms.getBoundSql(invocation.getArgs()[1]);
    String originalSql = boundSql.getSql();
    
    String updateSql = originalSql
        .replaceAll("(?i)^\\s*DELETE\\s+FROM\\s+(\\w+)", "UPDATE $1 SET deleted = 1")
        .replaceAll("(?i)\\s+WHERE", " WHERE deleted = 0 AND");
    
    MetaObject metaObject = SystemMetaObject.forObject(boundSql);
    metaObject.setValue("sql", updateSql);
    
    // 更新 SqlCommandType
    MetaObject msMetaObject = SystemMetaObject.forObject(ms);
    msMetaObject.setValue("sqlCommandType", SqlCommandType.UPDATE);
  }
  
  @Override
  public Object plugin(Object target) {
    return Plugin.wrap(target, this);
  }
  
  @Override
  public void setProperties(Properties properties) {}
}

拦截器方案对业务代码无侵入，所有 DELETE 自动转为 UPDATE SET deleted = 1，所有 SELECT 自动追加 deleted = 0 条件。

MyBatis-Plus @TableLogic 集成

实体类标注

@TableName("user")
public class User {
  @TableId(type = IdType.AUTO)
  private Long id;
  
  private String username;
  private String email;
  
  @TableLogic(value = "0", delval = "1")
  private Integer deleted;
  
  @TableField(fill = FieldFill.INSERT)
  private LocalDateTime createdAt;
}

配置方式

# application.yml
mybatis-plus:
  global-config:
    db-config:
      logic-delete-field: deleted        # 全局逻辑删除字段名
      logic-delete-value: 1              # 已删除值
      logic-not-delete-value: 0          # 未删除值

使用效果

// 普通 delete 自动转为 update
userMapper.deleteById(1L);
// 实际执行: UPDATE user SET deleted = 1 WHERE id = 1 AND deleted = 0

// select 自动追加 deleted = 0
userMapper.selectList(null);
// 实际执行: SELECT * FROM user WHERE deleted = 0

MyBatis-Plus 的 @TableLogic 方案比自定义拦截器更简洁，只需标注字段即可自动拦截，无需手动编写 SQL 改写逻辑。

查询已删除数据

忽略逻辑删除条件

public interface UserMapper {
  // 查询所有数据（含已删除）
  @Options(useCache = false)
  @Select("SELECT * FROM user")
  List<User> selectAllIncludingDeleted();
  
  // 仅查询已删除的
  @Select("SELECT * FROM user WHERE deleted = 1")
  List<User> selectDeleted();
}

MyBatis-Plus 忽略逻辑删除

public interface UserMapper extends BaseMapper<User> {
  @DataPermission(ignore = true)  // 忽略逻辑删除
  List<User> selectAllIncludingDeleted();
}

或使用 @TableLogic 配置：

@Select("SELECT * FROM user ${ew.customSqlSegment}")
List<User> selectWithDeleted(@Param(Constants.WRAPPER) Wrapper<User> wrapper);

数据恢复与审计

恢复已删除数据

public void recoverUser(Long userId) {
  // 将 deleted 标记回 0
  int rows = mapper.recoverById(userId);
  if (rows == 0) {
    throw new RuntimeException("用户不存在或未被删除");
  }
}

<update id="recoverById">
  UPDATE user SET deleted = 0, deleted_at = NULL, deleted_by = NULL
  WHERE id = #{id} AND deleted = 1
</update>

删除审计记录

<update id="logicalDeleteById">
  UPDATE user SET 
    deleted = 1, 
    deleted_at = NOW(), 
    deleted_by = #{operator}
  WHERE id = #{id} AND deleted = 0
</update>

唯一索引与逻辑删除

问题

逻辑删除场景下，唯一索引无法区分已删除记录：

-- 问题：删除后无法再插入同名用户
CREATE UNIQUE INDEX uk_username ON user(username);

解决方案

-- 方案一：联合唯一索引（包含 deleted 字段）
CREATE UNIQUE INDEX uk_username_deleted ON user(username, deleted);

-- 方案二：删除时间戳区分（MySQL 8.0+）
CREATE UNIQUE INDEX uk_username ON user(username, deleted_at);

生产推荐使用联合唯一索引 (username, deleted)，兼顾简单和兼容性。

要点总结

• 逻辑删除将 DELETE 转为 UPDATE SET deleted = 1，查询自动追加 WHERE deleted = 0。
• 自定义拦截器方案全局生效但 SQL 改写复杂；@TableLogic 方案更简洁，推荐集成使用。
• 记录 deleted_at 和 deleted_by 字段满足审计追溯需求。
• 数据恢复通过将 deleted 标记回 0 实现，无需备份数据。
• 逻辑删除场景下的唯一索引应使用联合索引 (字段, deleted) 避免冲突。
• 物理删除适合临时数据，逻辑删除适合核心业务数据，选择依据：数据是否需要恢复和审计。
• MyBatis-Plus 的 @TableLogic 标注字段后，CRUD 操作自动适配逻辑删除规则。