# MongoServerError: E11000 重复键错误，集合：test.users，索引：email_1，重复键：{ email: null }

- **ID:** `mongodb/index-duplicate-key-on-sparse`
- **领域:** mongodb
- **类别:** data_error
- **错误码:** `11000`
- **验证级别:** ai_generated
- **修复率:** 85%

## 根因

多个文档在具有唯一稀疏索引的字段上具有空值或缺失值，导致重复键错误，因为稀疏索引将空值和缺失值视为相同的键。

## 版本兼容性

| 版本 | 状态 | 引入 | 弃用 |
|------|------|------|------|
| mongodb-3.6 | active | — | — |
| mongodb-4.0 | active | — | — |
| mongodb-4.2 | active | — | — |
| mongodb-4.4 | active | — | — |
| mongodb-5.0 | active | — | — |
| mongodb-6.0 | active | — | — |
| mongodb-7.0 | active | — | — |

## 解决方案

1. ```
   Use a partial unique index instead of sparse to allow multiple nulls. Example: `db.users.createIndex({email:1},{unique:true,partialFilterExpression:{email:{$exists:true,$ne:null}}})`
   ```
2. ```
   Update all documents with null email to a unique placeholder value, such as an empty string or a generated UUID. Example: `db.users.updateMany({email:null},{$set:{email:UUID().toString()}})`
   ```
3. ```
   Drop the sparse index and create a regular unique index, ensuring that null values are replaced with a sentinel value first. Example: `db.users.dropIndex('email_1'); db.users.createIndex({email:1},{unique:true})`
   ```

## 无效尝试

- **** — This removes data integrity guarantees for non-null values, potentially allowing unintended duplicates. (15% 失败率)
- **** — This is a temporary fix; future inserts with null will cause the same error. (10% 失败率)
- **** — Existing null documents remain, so the error persists until they are also updated. (20% 失败率)