MySQL 唯一索引創建失敗排查：NULL 與空字符串的區別陷阱

故障現場

表 User(phone VARCHAR(20)) 存在數據：

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

執行 ALTER TABLE User ADD CONSTRAINT uk_phone UNIQUE(phone); 報錯：

ERROR 1062 (23000): Duplicate entry '' for key 'uk_phone'

根因分析

• NULL ≠ 空字符串：SQL 標準規定 NULL 代表「未知/無值」，多個 NULL **不視為重複**，唯一索引允許任意數量 NULL。
• 空字符串 '' 是具體值：長度為 0 的字符串，**視為重複值**，唯一索引僅允許一條。
• 表中 40 條 phone = '' 觸發重複鍵錯誤。

修復步驟

-- 1. 確認空字符串數量
SELECT COUNT(1) FROM User WHERE phone = '';

-- 2. 將空字符串統一轉為 NULL (業務語義一致)
UPDATE User SET phone = NULL WHERE phone = '';
-- Query OK, 40 rows affected

-- 3. 再次創建唯一索引
ALTER TABLE User ADD CONSTRAINT uk_phone UNIQUE(phone);
-- Query OK, 0 rows affected

最佳實踐：從源頭規避

1. 表定義階段強制約束

CREATE TABLE User (
    phone VARCHAR(20) DEFAULT NULL,  -- 顯式 DEFAULT NULL
    -- 或添加 CHECK (MySQL 8.0.16+)
    CONSTRAINT chk_phone CHECK (phone IS NULL OR phone <> '')
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

2. 應用層統一入參處理

// Java 示例
String phone = StringUtils.trimToNull(request.getParameter("phone"));
// 前端傳 "" → null，傳 "  " → null，傳 "138" → "138"

3. 數據遷移/ETL 清洗規則

• 字符串類型字段：**全量 trim → 空串轉 NULL** 入庫。
• 數值/日期類型：非法值統一轉 NULL，不存 0/1970-01-01 等魔術值。

延伸：唯一索引與 NULL 的行為差異

驗證腳本

-- 檢查表中是否存在空字符串將導致唯一索引失敗的列
SELECT
    TABLE_NAME,
    COLUMN_NAME,
    SUM(CASE WHEN COLUMN_NAME = '' THEN 1 ELSE 0 END) AS empty_count,
    SUM(CASE WHEN COLUMN_NAME IS NULL THEN 1 ELSE 0 END) AS null_count
FROM INFORMATION_SCHEMA.COLUMNS
JOIN 相應表 -- 需動態構建
WHERE DATA_TYPE IN ('char','varchar','text')
GROUP BY TABLE_NAME, COLUMN_NAME
HAVING empty_count > 1;

理解 NULL 與空字符串的語義邊界，在建模、開發、遷移三階段統一規範，徹底消除唯一索引創建失敗這類低級錯誤。