批量删除unexpectedadmission时如何避免误删正常记录？

Question

在批量删除 unexpectedadmission 记录时，如何确保不误删仍在正常流程中的 admission 数据？常见问题是：缺乏明确的状态标识或时间窗口过滤，导致脚本将尚未确认异常的待审记录一并清除。此外，外键关联校验缺失可能误伤依赖该记录的业务数据。如何设计安全删除机制，结合状态校验、软删除标记与事务回滚策略，成为保障数据完整性的关键挑战。

Answer 1

一、问题背景与核心挑战

在医疗或教育等业务系统中，admission（入院/入学）记录是核心数据资产。当系统产生大量unexpectedadmission（异常入院）记录时，运维人员常需批量清理以释放资源。然而，若删除机制设计不当，极易误删仍在正常流程中的admission数据。

常见问题包括：

• 缺乏明确的状态标识，无法区分“待确认异常”与“已确认异常”记录；
• 未设置合理的时间窗口过滤，导致近期生成的待审记录被误清除；
• 外键关联校验缺失，删除操作可能破坏依赖该记录的账单、日志或审批链数据；
• 直接硬删除（Hard Delete），缺乏回滚能力，一旦出错难以恢复。

二、分层防御式删除机制设计

为确保数据安全性，应采用“状态校验 → 软删除标记 → 事务控制 → 外键检测 → 异步清理”的多层策略。以下是具体实施路径：

1. 明确状态机模型

首先定义unexpectedadmission表的状态字段（如status），建议取值如下：

状态码	含义	是否可删除
PENDING	待审核	否
CONFIRMED	已确认异常	是
REVIEWING	正在复核	否
SOFT_DELETED	已软删	不可再删

2. 时间窗口与业务规则过滤

即使状态为“CONFIRMED”，也应结合时间维度进行二次过滤。例如，仅允许删除创建时间超过7天的记录，避免误删刚标记的异常数据。

-- 示例SQL：安全查询待删除记录
SELECT id, created_time, status 
FROM unexpectedadmission 
WHERE status = 'CONFIRMED'
  AND created_time < NOW() - INTERVAL 7 DAY
  AND NOT EXISTS (
    SELECT 1 FROM billing WHERE billing.admission_id = unexpectedadmission.id
  );
    

3. 外键依赖检测机制

在执行删除前，必须检查是否存在子表引用。可通过元数据查询或预定义依赖关系表实现。

子表名	外键字段	检测SQL模板
billing	admission_id	EXISTS(SELECT 1 FROM billing WHERE admission_id=? )
audit_log	ref_id	EXISTS(SELECT 1 FROM audit_log WHERE ref_id=? AND type='admission')
enrollment	source_admission_id	EXISTS(...)

4. 软删除标记替代物理删除

引入is_deleted布尔字段和deleted_at时间戳，将删除操作转化为更新：

UPDATE unexpectedadmission 
SET is_deleted = TRUE, deleted_at = NOW(), deleted_by = 'cleanup_job_v2'
WHERE id IN (
    SELECT id FROM (
        SELECT u.id FROM unexpectedadmission u
        LEFT JOIN billing b ON b.admission_id = u.id
        WHERE u.status = 'CONFIRMED'
          AND u.created_time < NOW() - INTERVAL 7 DAY
          AND b.admission_id IS NULL
          AND u.is_deleted = FALSE
    ) AS safe_to_delete
);
    

5. 事务封装与回滚策略

所有删除操作应在数据库事务中执行，并设置合理的超时与回滚条件。

BEGIN TRANSACTION;

-- 步骤1：记录操作上下文
INSERT INTO cleanup_audit (job_id, start_time, records_affected) 
VALUES ('job_20241015', NOW(), 0);

-- 步骤2：执行软删除
UPDATE unexpectedadmission SET ... WHERE ...;

-- 步骤3：验证影响行数是否符合预期
IF ROW_COUNT() > 10000 THEN
    ROLLBACK;
ELSE
    COMMIT;
END IF;
    

6. 基于Mermaid的流程图展示整体逻辑

graph TD
    A[启动清理任务] --> B{读取配置: 状态=CONFIRMED, 时间>7天}
    B --> C[查询候选记录]
    C --> D{是否存在外键依赖?}
    D -- 是 --> E[跳过并记录警告]
    D -- 否 --> F[开启事务]
    F --> G[执行软删除更新]
    G --> H{影响行数异常?}
    H -- 是 --> I[触发告警并回滚]
    H -- 否 --> J[提交事务]
    J --> K[写入审计日志]
    K --> L[任务结束]

7. 审计与监控机制

每次清理操作都应写入专用审计表cleanup_audit_log，包含以下字段：

• job_id: 作业唯一标识
• start_time / end_time: 执行时间段
• filter_condition: 使用的过滤条件
• record_count_before / after: 清理前后数量
• operator: 操作来源（脚本/人工）
• rollback_reason: 若回滚，记录原因
• notification_sent: 是否发送通知

8. 自动化脚本的最佳实践

编写清理脚本时应遵循以下原则：

1. 使用参数化配置而非硬编码条件；
2. 支持干运行模式（dry-run），仅输出将删除的ID列表；
3. 集成到CI/CD流水线，通过代码评审控制变更；
4. 配合Prometheus+Alertmanager实现异常操作告警；
5. 定期归档软删除数据至冷存储，实现最终物理清理。