唯一索引遇到 null 和缺失字段

  test> db.a.createIndex({email: 1}, {unique: true})
  email_1
  test> db.a.insertOne({_id: 1, name: "a"})    // 没有 email
  true
  test> db.a.insertOne({_id: 2, name: "b"})    // 又一个没有 email 的文档
  E11000 duplicate key error collection: test.a index: email_1 dup key: { email: null }
  test> db.a.insertOne({_id: 3, email: null})    // 显式 null,与缺失的冲突
  E11000 duplicate key error collection: test.a index: email_1 dup key: { email: null }
  test> 两个不同的非空 email
  ok

  test> db.b.createIndex({email: 1}, {unique: true, sparse: true})
  email_1
  test> db.b.insertMany([{_id: 1}, {_id: 2}, {_id: 3}])    // 三个缺失 email
  ok
  test> db.b.insertOne({_id: 4, email: null})    // 显式 null:第一个
  ok
  test> db.b.insertOne({_id: 5, email: null})    // 显式 null:第二个
  E11000 duplicate key error collection: test.b index: email_1 dup key: { email: null }

  test> db.c.createIndex({email: 1}, {unique: true, partialFilterExpression: {email: {$type: "string"}}})
  email_1
  test> 缺失 ×2、null ×2
  ok
  test> 两个相同的字符串 email
  E11000 duplicate key error collection: test.c index: email_1 dup key: { email: "a@x.com" }
  test> db.c.countDocuments({})
  5

  test> db.d.createIndex({a: 1, b: 1}, {unique: true})
  a_1_b_1
  test> db.d.insertOne({a: 1})    // b 缺失
  ok
  test> db.d.insertOne({a: 1, b: null})    // 和上一个相同的 (1, null)
  E11000 duplicate key error collection: test.d index: a_1_b_1 dup key: { a: 1, b: null }
  test> db.d.insertOne({a: 1, b: 2})
  ok
  test> db.d.insertOne({})    // a、b 都缺失
  ok
  test> 再来一个 a、b 都缺失的
  E11000 duplicate key error collection: test.d index: a_1_b_1 dup key: { a: null, b: null }

  test> try { db.e.createIndex({k: 1}, {unique: true}) } catch (e) { ({code: e.code, message: e.message.slice(0, 200)}) }
  {
    code: 11000,
    message: 'Index build failed: 53e5d09f-245f-4067-b988-5190b0df7272: Collection test.e ( 3f36c4cf-fa04-42cb-abc0-0888acf89909 ) :: caused by :: E11000 duplicate key erro
  }
  test> 找出重复:$group 后 $match n > 1
  [
    { _id: null, ids: [ 3, 4 ], n: 2 },
    { _id: 'x', ids: [ 1, 2 ], n: 2 }
  ]

  test> try { db.a.insertOne({_id: 9}) } catch (e) { ({code: e.code, keyPattern: e.keyPattern, keyValue: e.keyValue, message: e.message}) }
  {
    code: 11000,
    keyPattern: { email: 1 },
    keyValue: { email: null },
    message: 'E11000 duplicate key error collection: test.a index: email_1 dup key: { email: null }'
  }
  test> try { db.d.insertOne({_id: 10, a: 1, b: 2}) } catch (e) { ({code: e.code, keyPattern: e.keyPattern, keyValue: e.keyValue}) }
  { code: 11000, keyPattern: { a: 1, b: 1 }, keyValue: { a: 1, b: 2 } }