MySQL软删除与唯一约束共存的最佳实践及Prisma相关解决方案咨询

嘿，这个问题我在项目里碰过好多次了——软删除要保留数据历史，可唯一约束又卡着不让重复值复用，确实头疼！下面分享下MySQL端的最佳实践，还有Prisma里怎么适配：

MySQL 端的核心解决方案 #

1. 复合唯一索引（首选方案） #

这是最通用也最可靠的做法，把原来单一的email唯一索引，改成email+deleted_at的复合唯一索引。

原理很简单：MySQL里NULL值不视为相等，所以**未删除的记录（deleted_at为NULL）**之间，email不能重复；而软删除的记录（deleted_at有时间戳），因为每个记录的deleted_at值不同，哪怕email相同也不会触发唯一约束；新插入的未删除记录（deleted_at为NULL）和软删除的同email记录也不会冲突，完美契合需求。

修改后的表结构如下：

CREATE TABLE users (
  id INTEGER NOT NULL AUTO_INCREMENT,
  email VARCHAR(50) NOT NULL,
  deleted_at TIMESTAMP(0) NULL,
  UNIQUE INDEX `users_id_unique` (`id`),
  -- 替换原单一email唯一索引
  UNIQUE INDEX `users_email_deleted_at_unique` (`email`, `deleted_at`),
  PRIMARY KEY (`id`)
);

2. 归档表分离（适合严格归档场景） #

如果你的业务对主表数据纯净度要求很高，也可以在软删除时把记录物理迁移到归档表，主表只保留活跃数据。这样主表的单一email唯一索引就能正常工作，不会有冲突问题。

但这个方案的缺点是失去了软删除“原表留存、快速恢复”的优势，适合不需要频繁恢复删除数据的场景。

3. 业务层/触发器校验（不推荐） #

也可以保留原单一唯一索引，在插入/更新时通过业务逻辑或者触发器，检查是否存在未删除的同email记录。但这种方案可靠性不如数据库层面的约束——业务层容易出现逻辑漏洞，触发器也可能因为事务、并发场景失效，所以只作为备选。

Prisma 中的适配方案 #

Prisma本身没有内置的软删除+唯一约束的专属支持，但可以通过以下方式完美适配上面的MySQL方案：

1. 模型定义复合唯一约束 #

在schema.prisma里直接定义email和deletedAt的复合唯一约束，执行迁移后会自动生成对应的MySQL复合索引：

model User {
  id        Int      @id @default(autoincrement())
  email     String
  deletedAt DateTime?
  -- 声明复合唯一约束
  @@unique([email, deletedAt])
}

2. 封装软删除与创建逻辑 #

可以在业务层封装通用的软删除和用户创建方法，既保证逻辑统一，又能返回更友好的错误提示：

import { PrismaClient } from '@prisma/client';
const prisma = new PrismaClient();

// 创建用户前先检查未删除的同邮箱记录
async function createUser(data: { email: string }) {
  const existingActiveUser = await prisma.user.findFirst({
    where: {
      email: data.email,
      deletedAt: null
    }
  });

  if (existingActiveUser) {
    throw new Error("该邮箱已被活跃用户使用");
  }

  return await prisma.user.create({ data });
}

// 封装软删除方法
async function softDeleteUser(userId: number) {
  return await prisma.user.update({
    where: { id: userId },
    data: { deletedAt: new Date() }
  });
}

3. 全局中间件自动过滤软删除记录 #

用Prisma Middleware可以全局处理软删除记录的过滤，避免每次查询都手动添加deletedAt: null的条件：

prisma.$use(async (params, next) => {
  // 针对User模型的查询操作自动过滤已删除记录
  if (params.model === 'User') {
    switch (params.action) {
      case 'findUnique':
      case 'findFirst':
        params.args.where = { ...params.args.where, deletedAt: null };
        break;
      case 'findMany':
        params.args.where = { ...(params.args.where || {}), deletedAt: null };
        break;
    }
  }
  return next(params);
});

这样在调用prisma.user.findFirst({ where: { email: 'xxx@xxx.com' } })时，会自动只查询未删除的用户，无需额外条件。

内容来源于stack exchange