背景

在程序設計中，我們往往需要確保數(shù)據(jù)的唯一性，比如在常見的注冊模塊，我們需要確保一個手機號只能注冊為一個賬號。

這種情況下，我們的程序往往是第一道關卡，用戶來注冊之前，首先判斷這個手機號是否已經注冊，如果已經注冊則返回錯誤信息,或直接去登錄。

但是我們不能確保同時有兩個人使用同一個手機號注冊到我們的系統(tǒng)中，因此這里就需要在更深的層次去確保手機號在系統(tǒng)的唯一性了。

不同存儲方案，解決方式不一樣。

對于常用的MySQL數(shù)據(jù)庫，我們可以使用唯一索引的方式來作為我們的最后一道防線。

但是最近在使用數(shù)據(jù)庫的唯一索引時，發(fā)現(xiàn)一個比較奇怪的現(xiàn)象。

MySQL數(shù)據(jù)庫，使用InnoDB存儲引擎，創(chuàng)建了唯一索引時，在insert操作時，如果唯一索引上的字段有為NULL的情況，則可以無限插入。

這有點匪夷所思，但是現(xiàn)實就是這么一個情況。

現(xiàn)在就來具體分析這樣的一個案例，來看看底層對于唯一索引是怎么設計的，來規(guī)避在數(shù)據(jù)庫設計上犯錯和踩坑。

案例

假設現(xiàn)在有一個用于保存用戶信息的數(shù)據(jù)表user，是使用email注冊的，當前使用email作為唯一索引，同時這一基本規(guī)則也被其他依賴系統(tǒng)作為設計數(shù)據(jù)模型的設計基礎。

假設現(xiàn)在設計這樣一個user表：

CREATE TABLE `user` (
  `id` bigint(20) unsigned NOT NULL AUTO_INCREMENT COMMENT 'primary key',
  `email` varchar(32) NOT NULL DEFAULT '' COMMENT 'email',
  `name` varchar(11) DEFAULT '' COMMENT 'name',
  `age` int(11) DEFAULT NULL COMMENT 'age',
  PRIMARY KEY (`id`),
  UNIQUE KEY `uk-email` (`email`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8;

1@user.com來注冊，執(zhí)行insert語句,執(zhí)行成功

INSERT INTO user (email,name,age) VALUES ('1@user.com','h1',18);

1@user.com再來注冊，則再次執(zhí)行，則報錯。

成功規(guī)避了用戶多次創(chuàng)建導致系統(tǒng)產生臟數(shù)據(jù)問題。

Duplicate entry '1@user.com' for key 'uk-email'

從這里看，user表的設計是符合業(yè)務要求的，并沒有出現(xiàn)同一個email出現(xiàn)多行的情況。

隨著業(yè)務發(fā)展，單單email注冊的模式并不適合移動互聯(lián)網時代，所以現(xiàn)在的要求在原有基礎上增加了手機號的字段，并要求手機號也是唯一的。

于是添加phone字段，并將原有唯一索引刪除，為email和phone設置新的唯一索引。

ALTER TABLE `user` ADD COLUMN `phone` varchar(11) default NULL AFTER `age`;

DROP INDEX `uk-email` ON `user`;

ALTER TABLE `user` ADD UNIQUE KEY `uk-email-phone` (`email`,`phone`);

假設用戶1再來用同樣的email注冊,可以注冊成功：

INSERT INTO user (email,name,age,phone) VALUES (‘1@user.com',‘h1',18,NULL);

查詢數(shù)據(jù)庫數(shù)據(jù)，得到以下結果：

有兩個email為1@user.com的記錄，他們的phone都是NULL，這怎么可能存在？！難道是MySQL出問題了？！不可能，我們再試另外一個數(shù)據(jù)

INSERT INTO user (email,name,age,phone) VALUES ('2@user.com','h2',18,'18812345678');

連續(xù)執(zhí)行兩次，第一次執(zhí)行成功，第二次報錯：

Duplicate entry ‘2@user.com-18812345678’ for key ‘uk-email-phone’

查詢user結果集，得到

從結果看這樣MySQL的唯一索引也算是正常的啊，那這到底是怎么一回事呢？

原因探尋

業(yè)務中希望建立的唯一索引是email + phone的組合，但是由于phone一開始是沒有數(shù)據(jù)的，所以新建字段時默認允許為NULL來兼容老數(shù)據(jù)。

如果程序沒有控制好，數(shù)據(jù)操作直接打到數(shù)據(jù)庫，就產生了兩條email為“1@user.com”且phone為NULL的數(shù)據(jù)，那么就會發(fā)生這種數(shù)據(jù)錯亂的情況。

我從 MySQL 5.7官方文檔 中找到了這個：

Unique Indexes

A UNIQUE index creates a constraint such that all values in the index must be distinct. An error occurs if you try to add a new row with a key value that matches an existing row. If you specify a prefix value for a column in a UNIQUE index, the column values must be unique within the prefix length. A UNIQUE index permits multiple NULL values for columns that can contain NULL.

官方的文檔中明確說明在唯一索引中是允許存在多行值為NULL的數(shù)據(jù)存在的。

當然我們會認為這是MySQL的一個bug，其實早有人這么認為了，并給MySQL提出了這個問題https://bugs.mysql.com/bug.php?id=8173。

但是MySQL的開發(fā)者并不認為這是一個bug，而是本身的一種設計。額，這么說，好像也說得過去。那這里就有一個問題了，我們知道索引是使用B+樹來維護的，但是對于這種非唯一索引是怎么維護的？

帶著這個問題，我覺得有兩種可能：

（1）唯一索引時另外一種數(shù)據(jù)類型，正好把有值為NULL的字段過濾掉了，無需特殊處理。

（2）還是用的B+樹索引，但是對于NULL的索引特殊處理了。

于是我對email=2@user.com且phone= 18812345678的數(shù)據(jù)執(zhí)行了Explain執(zhí)行計劃

explain select * from user where `email` = '2@user.com' and `phone` = '18812345678';

這個查詢正好用到了唯一索引uk-email-phone，索引長度是134。

對email=1@user.com且phone為NULL的執(zhí)行類似Explain執(zhí)行計劃

explain select * from user where `email` = '1@user.com' and `phone` is   NULL;

對比上面兩次不同數(shù)據(jù)的explain執(zhí)行結果，可以看到其實都用了uk-email-phone的唯一索引，不同的是第一個type是const(通過一次索引就可以找到，用于primary key或unique index)，第二個type是ref(非唯一性索引掃描)，且rows為2。

所以猜測這里極有可能是對NULL進行的特殊處理，唯一索引樹還是用的和非NULL一樣的唯一索引樹。

源碼分析

上面利用explain，測試結果是符合自己的猜測行為而已。也許只有源碼中才能比較好的知道答案，基于此，在github上找到MySQL相關的源碼(在此感謝DBA同學在唯一索引源碼分析上的指點)。

在這段源碼https://github.com/mysql/mysql-server/blob/8e797a5d6eb3a87f16498edcb7261a75897babae/storage/innobase/row/row0ins.cc中，有一個方法 row_ins_scan_sec_index_for_duplicate(),這里會掃描唯一非聚簇索引樹，來確定是否會發(fā)生唯一性的沖突。

源碼內有一段注釋

/* If the secondary index is unique, but one of the fields in the
  n_unique first fields is NULL, a unique key violation cannot occur,
  since we define NULL != NULL in this case */

在繼續(xù)往下有一段這樣的邏輯

	 cmp = cmp_dtuple_rec(entry, rec, index, offsets);

    if (cmp == 0 && !index->allow_duplicates) {
      if (row_ins_dupl_error_with_rec(rec, entry, index, offsets)) {
        err = DB_DUPLICATE_KEY;

        thr_get_trx(thr)->error_info = index;

        /* If the duplicate is on hidden FTS_DOC_ID,
        state so in the error log */
        if (index == index->table->fts_doc_id_index &&
            DICT_TF2_FLAG_IS_SET(index->table, DICT_TF2_FTS_HAS_DOC_ID)) {
          ib::error(ER_IB_MSG_958) << "Duplicate FTS_DOC_ID"
                                      " value on table "
                                   << index->table->name;
        }

        goto end_scan;
      }
    } else {
      ut_a(cmp < 0 || index->allow_duplicates);
      goto end_scan;
    }

跳轉到row_ins_dupl_error_with_rec()方法中有一段這樣的邏輯

/* In a unique secondary index we allow equal key values if they
  contain SQL NULLs */

  if (!index->is_clustered() && !index->nulls_equal) {
    for (i = 0; i < n_unique; i++) {
      if (dfield_is_null(dtuple_get_nth_field(entry, i))) {
        return (FALSE);
      }
    }
  }

在唯一索引中有字段為NULL的情況下，返回FALSE，代碼中就沒有拋出DB_DUPLICATE_KEY的異常了。

所以從源碼來看，這里實現(xiàn)了唯一索引允許為NULL的情況了，而且可以知道，這個唯一索引樹和其他的二級索引基本上是沒什么區(qū)別的。

這也是前面explain時及時我們查詢非唯一索引中另一個字段為空的記錄，也還是用到了同樣的索引和相同的索引長度。

反觀來看，如果是我們在未知實現(xiàn)的情況下，要我們來設計，怎么實現(xiàn)允許有字段為NULL的唯一索引呢？是否還有比現(xiàn)有MySQL更好的方式來實現(xiàn)？

結論

所以其實MySQL在唯一索引中允許存在值為NULL的字段。

NULL值在MySQL可以代表是任意值，并且在有字段值為NULL時，不會參與校驗這個組合的唯一索引，所以可能插入業(yè)務上不允許重復的數(shù)據(jù)，導致臟數(shù)據(jù)。

因此在創(chuàng)建屬于唯一索引的列時，最好指定字段值不能為空，在已有值為NULL的情況下，創(chuàng)建的字段不允許為空，且默認值為空字符。

如果已經創(chuàng)建了默認值為NULL的字段，則先將其update為空字符，然后再修改為NOT NULL DEFAULT ‘’。

如上述情況建表語句改為

CREATE TABLE `user` (
  `id` bigint(20) unsigned NOT NULL AUTO_INCREMENT COMMENT 'primary key',
  `email` varchar(32) NOT NULL DEFAULT '' COMMENT 'email',
  `name` varchar(11) DEFAULT '' COMMENT 'name',
  `age` int(11) DEFAULT NULL COMMENT 'age',
  `phone` varchar(11) NOT NULL DEFAULT '',
  PRIMARY KEY (`id`),
  UNIQUE KEY `uk-email-phone` (`email`,`phone`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8;

并非所有數(shù)據(jù)庫都是這樣，SQL Server 2005及更老的版本，只允許有一個NULL值出現(xiàn)。

從https://sqlite.org/faq.html#q26 了解到ANSI SQL-92標準：

A unique constraint is satisfied if and only if no two rows in a table have the same non-null values in the unique columns.（如果且僅當表中沒有兩行在唯一列中具有相同的非空值時，才滿足唯一約束。）

除了MySQL之外，sqlLite、PostgreSQL、Oracle和FireBird也是允許唯一索引上存在多行為NULL。

以上為個人經驗，希望能給大家一個參考，也希望大家多多支持腳本之家。

最新評論