MongoDB唯一索引异常求助：不同账号类型返回错误重复键值

我在Mongoose Schema里设置了条件约束：当accountType为email时，email字段必填且唯一；当accountType为username时，username字段必填且唯一。但创建accountType为username的账号时，MongoDB返回email: null的重复键错误（错误码E11000），不是预期的username相关错误；创建email类型账号时，有时能返回正确的email重复错误。希望两种场景都返回对应字段的正确错误提示。

错误日志 #

创建username类型账号时的错误 #

"err": "E11000 duplicate key error collection: painPoint.users index: email_1 dup key: { email: null }",
{
    "error": {
        "index": 0,
        "code": 11000,
        "keyPattern": {
            "email": 1
        },
        "keyValue": {
            "email": null
        }
    }
}

创建重复email类型账号时的错误 #

"err": "E11000 duplicate key error collection: painPoint.users index: email_1 dup key: { email: \"damianemerah1@gmail.com\" }",
    "error": {
        "index": 0,
        "code": 11000,
        "keyPattern": {
            "email": 1
        },
        "keyValue": {
            "email": "damianemerah1@gmail.com"
        }
    }
}

当前使用的Schema代码 #

import mongoose from "mongoose";

const UserSchema = new mongoose.Schema({
  email: {
    type: String,
    unique: [true, "Email already exists"],
    required: [
      function () {
        return this.accountType === "email";
      },
      "Email is required",
    ],
  },
  username: {
    unique: [true, "Username already exists"],
    type: String,
    required: [
      function () {
        return this.accountType === "username";
      },
      "Username is required",
    ],
  },
  name: {
    type: String,
    required: [
      function () {
        return this.accountType === "email";
      },
      "Name is required",
    ],
  },
  accountType: {
    type: String,
    required: true,
    enum: ["username", "email", "google"],
  },

  password: { type: String, required: true },
  since: { type: Date, default: Date.now },
  injuries: [{ type: mongoose.Schema.Types.ObjectId, ref: "Injury" }],
});

export default mongoose.models.User || mongoose.model("User", UserSchema);

解决方案 #

问题出在MongoDB的默认唯一索引逻辑：它会把null值当成重复项。当创建username类型账号时，email字段没赋值，MongoDB自动存为null，如果之前已经有其他username类型账号的email是null，就会触发重复键错误。

要解决这个问题，得给email和username字段加部分唯一索引，让唯一约束只在特定条件下生效：

修改后的Schema代码 #

import mongoose from "mongoose";

const UserSchema = new mongoose.Schema({
  email: {
    type: String,
    required: [
      function () {
        return this.accountType === "email";
      },
      "Email is required",
    ],
  },
  username: {
    type: String,
    required: [
      function () {
        return this.accountType === "username";
      },
      "Username is required",
    ],
  },
  name: {
    type: String,
    required: [
      function () {
        return this.accountType === "email";
      },
      "Name is required",
    ],
  },
  accountType: {
    type: String,
    required: true,
    enum: ["username", "email", "google"],
  },

  password: { type: String, required: true },
  since: { type: Date, default: Date.now },
  injuries: [{ type: mongoose.Schema.Types.ObjectId, ref: "Injury" }],
});

// 仅当accountType为email时，检查email字段的唯一性
UserSchema.index({ email: 1 }, { 
  unique: true, 
  partialFilterExpression: { accountType: "email" },
  message: "Email already exists"
});

// 仅当accountType为username时，检查username字段的唯一性
UserSchema.index({ username: 1 }, { 
  unique: true, 
  partialFilterExpression: { accountType: "username" },
  message: "Username already exists"
});

export default mongoose.models.User || mongoose.model("User", UserSchema);

核心改动说明 #

1. 删掉了字段定义里的unique选项，改用Schema级别的部分索引实现条件唯一约束
2. partialFilterExpression指定了索引生效的条件：
   • email的唯一索引只在accountType是email时生效
   • username的唯一索引只在accountType是username时生效
3. 这样创建username类型账号时，email字段的null值不会触发唯一检查；同理，email类型账号的username为空也不会被检查

必须做的后续操作 #

修改索引后，得先删掉原来的email_1和username_1索引，否则新的部分索引不会生效。可以用MongoDB命令行执行：

# 删除email字段的旧索引
db.users.dropIndex("email_1")
# 删除username字段的旧索引
db.users.dropIndex("username_1")

重启应用后，Mongoose会自动创建新的部分索引。

内容的提问来源于stack exchange，提问作者Damian