MySQL 唯一索引與 NULL/空值陷阱：多空值導致創建失敗的解決方案

現象重現

mysql> SELECT phone, COUNT(1) FROM User GROUP BY phone;
+-----------------+----------+
| phone           | count(1) |
+-----------------+----------+
| NULL            | 70       |
|                 | 40       |  -- 空字串 ''
| +86-13390889711 | 1        |
| +86-13405053385 | 1        |
+-----------------+----------+

mysql> ALTER TABLE User ADD CONSTRAINT uk_phone UNIQUE (phone);
ERROR 1062 (23000): Duplicate entry '' for key 'uk_phone'

根本原因

• NULL ≠ 空字串：SQL 標準規定 NULL 不是值，多個 NULL 允許進入唯一索引（InnoDB/MyISAM 均如此）
• 空字串 '' 是具體值：多行空字串視為重複值，違反唯一約束
• 業務代碼常將「未填寫」存為 '' 而非 NULL，導致大量重複空值

解決步驟

1. 確認分布：SELECT phone, COUNT(*) FROM User GROUP BY phone;
2. 驗證 NULL vs 空字串：

   
   SELECT COUNT(*) FROM User WHERE phone IS NULL;   -- 70
   SELECT COUNT(*) FROM User WHERE phone = '';      -- 40
   

3. 統一為 NULL（推薦）：

   
   UPDATE User SET phone = NULL WHERE phone = '';
   -- Query OK, 40 rows affected
   

4. 創建唯一索引：

   
   ALTER TABLE User ADD CONSTRAINT uk_phone UNIQUE (phone);
   -- Query OK
   

替代方案：部分唯一索引（MySQL 8.0.13+）

-- 僅對非空值強制唯一
CREATE UNIQUE INDEX uk_phone_not_null ON User (phone) WHERE phone IS NOT NULL;

優雅解決「多 NULL/空值共存」且無需清洗歷史資料。

最佳實踐建議

• 資料庫層面：欄位預設 NULL，非業務必填勿設 NOT NULL
• 應用層面：未填寫時傳 NULL 而非 ''，ORM 配置 empty_string_is_null=true
• 遷移腳本：上線前跑 UPDATE ... SET col=NULL WHERE col=''
• 檢查腳本：SELECT col, COUNT(*) FROM t GROUP BY col HAVING COUNT(*)>1 AND col IS NOT NULL AND col<>'' 定期掃描潛在衝突

小結

唯一索引允許多 NULL，不允許多 ''。統一空值語義為 NULL 是根治之道。MySQL 8.0.13+ 可用 WHERE phone IS NOT NULL 部分唯一索引優雅繞過歷史包袱。