IT技术社区_专业开发者平台_最新编程教程与解决方案 -沐辰社区

PHP 唯一软删除如何避免

wen PHP项目 3

PHP 软删除唯一索引冲突终极解决方案：从入门到生产级架构

目录导读（Table of Contents）

  1. 问题本质：为什么唯一约束与软删除是“天敌”？
  2. 常见误区：deleted_at 加随机数为何是“饮鸩止渴”？
  3. 复合唯一索引（最优雅）
  4. Trash 归档表（数据量大时的王道）
  5. 全局唯一 UUID + 业务标识（分布式必选）
  6. 触发器 + 状态生成列（数据库隐式处理）
  7. 实战演示：Laravel / ThinkPHP 代码级落地
  8. 性能与索引优化：覆盖索引 vs 过滤索引
  9. 终极问答（FAQ）：高频面试与线上故障排查

问题本质：为什么唯一约束与软删除是“天敌”？

在传统电商或 CMS 系统中，我们常对 user_email 或 product_sku 建立唯一索引，用于防止重复数据，但一旦启用软删除（deleted_at 字段标记删除），逻辑上被删除的行依然物理存在于表中，此时若新增一条相同 email 的记录，数据库会直接抛出 Duplicate entry 错误。

PHP 唯一软删除如何避免

核心矛盾：唯一索引的粒度是“整行”，而你需要的唯一性是“仅针对未删除的记录”。

常见误区：deleted_at 加随机数为何是“饮鸩止渴”？

许多新手会这样写：

$data['email'] = $email . '_' . uniqid();

代码看似解决了冲突，实则破坏了软删除的意义：

  • 你无法通过原始 email 恢复数据。
  • 关联外键（如订单表引用用户ID）后，邮件无法对应原始用户。
  • 垃圾数据无限膨胀,索引失效。

更糟糕的是,有些方案用 deleted_at 存时间戳，但查询时忘记加 WHERE deleted_at IS NULL，导致统计错误。

方案一：复合唯一索引（最优雅）

核心逻辑：将唯一约束从“单一字段”升级为“字段 + 删除状态”的组合。

MySQL 8.0+ / PostgreSQL 15+ 生成列方案

ALTER TABLE users
ADD COLUMN deleted_marker VARCHAR(20) GENERATED ALWAYS AS (
    IF(deleted_at IS NULL, 'active', CONCAT('del_', id))
) STORED,
ADD UNIQUE INDEX idx_unique_email (email, deleted_marker);
  • 原理：当 deleted_at 为 NULL 时，标记为固定值 active；一旦删除，标记为 del_ + 主键ID（保证唯一）。
  • 效果：同一邮箱只能有一条活跃记录；多个已删除记录可以共存（因为标记各不相同）。

针对 MySQL 5.7（无生成列）：使用普通复合索引

ALTER TABLE users ADD UNIQUE INDEX idx_email_active (email, deleted_at);
  • 变通：将 deleted_at 默认设为 '1970-01-01 00:00:01'（代表未删除），删除时更新为当前时间。
  • 缺点：你必须在代码层强制 deleted_at 要么为默认值，要么为时间戳，但此方法简单有效，兼容老版本。

方案二：Trash 归档表（数据量大时的王道）

当表数据过亿且频繁删除时,复合索引会让 B+ 树索引体积剧增，此时推荐双表设计：

  • 主表：只存活跃数据（status=1），物理删除时直接 DELETE。
  • 归档表：users_trash，结构与主表一致，但额外增加 deleted_reason、deleted_by、deleted_at。

伪代码（PHP + PDO）：

try {
    $pdo->beginTransaction();
    // 1. 将记录从主表复制到归档表
    $stmt = $pdo->prepare("INSERT INTO users_trash SELECT *, NOW() FROM users WHERE id = ?");
    $stmt->execute([$id]);
    // 2. 从主表物理删除
    $pdo->prepare("DELETE FROM users WHERE id = ?")->execute([$id]);
    $pdo->commit();
} catch (Exception $e) {
    $pdo->rollBack();
    // 记录日志
}

优势：

  • 主表体积小,唯一索引性能极高。
  • 查询活跃用户无需过滤 deleted_at，天然区分。
  • 恢复数据只需从归档表插回主表。

适用场景：订单表、发票表、日志型业务。

方案三：全局唯一 UUID + 业务标识（分布式必选）

如果系统采用分库分表或微服务,数据库自增 ID 不再全局唯一，此时应抛弃数据库唯一索引，改为应用层 UUID + 业务唯一键哈希：

// 生成一个基于业务内容的确定性 UUID
$uuid = Ramsey\Uuid\Uuid::uuid5(Uuid::NAMESPACE_DNS, $user_email);
$data = [
  'email' => $user_email,
  'uuid_sk' => $uuid->toString(),
  'deleted_at' => null,
];
// 查询时判断：
$exists = User::where('uuid_sk', $uuid)
             ->whereNull('deleted_at')
             ->exists();
  • 为什么解决冲突：即使同一邮箱被多次删除，每次插入的 uuid_sk 是相同的，但唯一索引建在 uuid_sk 上，却要结合 deleted_at，不过这里我们不依赖索引，而是通过“软删除 + UUID”在业务层只查活跃记录。
  • 关键：将唯一索引改为 (uuid_sk, deleted_at) 复合索引，并保证 deleted_at 为 NULL 时，uuid_sk 唯一。

方案四：触发器 + 状态生成列（数据库隐式处理）

这是方案一的进阶版,完全由数据库拦截冲突：

DELIMITER $$
CREATE TRIGGER prevent_duplicate_active_email
BEFORE INSERT ON users
FOR EACH ROW
BEGIN
    IF NEW.deleted_at IS NULL THEN
        IF EXISTS (SELECT 1 FROM users WHERE email = NEW.email AND deleted_at IS NULL) THEN
            SIGNAL SQLSTATE '45000' SET MESSAGE_TEXT = 'Duplicate active email';
        END IF;
    END IF;
END$$

注意：此方案在高并发下存在竞态条件（Race Condition），必须配合 SELECT ... FOR UPDATE 或唯一索引兜底。不推荐生产环境单独使用。

实战演示：Laravel / ThinkPHP 代码级落地

Laravel 6+ 使用复合唯一索引（方案一）

迁移文件：

Schema::table('users', function (Blueprint $table) {
    $table->timestamp('deleted_at')->nullable()->default(null);
    $table->string('active_hash')->nullable()->virtualAs('IF(deleted_at IS NULL, "ACTIVE", CONCAT("DEL_", id))');
    $table->unique(['email', 'active_hash'], 'idx_email_active');
});

模型操作：

// 创建用户（无需特殊处理）
User::create(['email' => 'test@example.com']);
// 软删除
$user->delete(); // 假设使用 SoftDeletes trait
// 再次插入同邮箱（不会冲突）
User::create(['email' => 'test@example.com']);

ThinkPHP 6 基于 Trash 表（方案二）

// 归档 Service
class UserArchiveService {
    public static function remove(int $id): bool {
        Db::startTrans();
        try {
            $user = Db::table('users')->find($id);
            Db::table('users_trash')->insert($user + ['deleted_at' => time()]);
            Db::table('users')->delete($id);
            Db::commit();
            return true;
        } catch (\Throwable $e) {
            Db::rollBack();
            return false;
        }
    }
}

性能与索引优化：覆盖索引 vs 过滤索引

  • 过滤索引（PostgreSQL 部分索引）：

    CREATE UNIQUE INDEX idx_unique_email_active ON users(email) WHERE deleted_at IS NULL;

    仅在未删除的记录上建立唯一索引,最优解！MySQL 8.0.13 以下不支持，但云数据库（如 Aurora）已支持。

  • 索引利用率：查询活跃用户必须强制带上 WHERE deleted_at IS NULL，否则索引失效。

  • EXPLAIN 优化：复合索引字段顺序应为 (email, deleted_at)，这样等值查询 email 时，可直接跳过已删除记录。

终极问答（FAQ）

Q1：MySQL 版本不支持生成列或部分索引，如何快速上线？ A：采用方案一（复合索引）的变种：deleted_at 默认设为 0（非 NULL），删除时设为当前时间戳，查询时用 WHERE deleted_at = 0，这种情况下，唯一索引为 (email, deleted_at)，但注意删除后如果同一秒内删除两条相同邮箱，deleted_at 时间戳可能不同（毫秒精度），不会冲突。

Q2：软删除记录越来越多，怎么清理？ A：定期任务——将 30 天前的软删除记录物理删除，或迁移至冷存储（如 ClickHouse），注意清理前必须手动校验无外键关联。

Q3：使用 UUID 方案，那用户 ID 用什么？ A：保留自增主键作为内部关联，但业务传参使用 UUID，唯一性由应用层保证，但必须在 users 表上为 (uuid_sk, deleted_at) 建立索引，并捕获重复插入异常，重试或报错。

Q4：多个字段（email + phone）都要唯一怎么办？ A：要么为每个字段建立独立复合索引，要么创建一个唯一“标签”字段，如 md5(email . '|' . phone)，再结合 deleted_at 建唯一索引。

Q5：我能只用 deleted_at 加 deleted_by 吗？ A：不行，因为 deleted_by 是操作者 ID，并非唯一标识，你必须有一个区分不同删除记录的序列。

结束语：选择方案时，请先评估数据库版本、数据量级、并发峰值，对于 1000 万以下数据，复合索引最省心；对于高吞吐系统，Trash 归档或部分索引才是王道。—软删除不是“不删”，而是“延迟删”，优雅的唯一约束是架构师的分水岭。

上一篇PHP 模型全局作用域

下一篇PHP 软删除实现方式

相关文章

抱歉，评论功能暂时关闭!