詳解MySQL的字段默認(rèn)null對唯一索引的影響
正文
在日常業(yè)務(wù)開發(fā)中，會經(jīng)常遇到需要保證唯一性的數(shù)據(jù)業(yè)務(wù)，如用戶注冊業(yè)務(wù)。一般注冊業(yè)務(wù)中允許用戶以手機(jī)號或email注冊賬號，且需要保證唯一，不允許重復(fù)注冊。當(dāng)用戶輸入手機(jī)號或email登錄時，程序會判定輸入信息的存在與否性，存在則走登錄，不存在則走注冊。而保證唯一性就不僅僅需要在程序端做判斷，還需要MySQL的唯一索引去做最后一道防線。那么唯一索引在一些業(yè)務(wù)中使用，如果唯一索引字段中默認(rèn)值設(shè)置為了null，會造成什么后果呢？
在阿里的《阿里巴巴Java開發(fā)手冊》中關(guān)于MySQL-索引規(guī)范中寫道： 【強(qiáng)制】業(yè)務(wù)上具有唯一特性的字段，即使是多個字段的組合，也必須創(chuàng)建唯一索引。
說明：
不要以為唯一索引影響了insert速度，這個速度的損耗可以忽略不計，但提高查找的速度是明顯的；
另外，即使在應(yīng)用層做了非常完善的校驗控制，只要沒有唯一索引，根據(jù)墨菲定律，必然有臟數(shù)據(jù)產(chǎn)生。
看一下為何唯一索引為影響insert速度
在MySQL中，唯一索引樹是一個非聚簇索引，每次插入數(shù)據(jù)時，都會在唯一索引樹上進(jìn)行遍歷查找該插入值是否唯一，這也就是為什么會影響insert的速度，因為多一步遍歷判斷唯一性。
MySQL版本：在docker中啟動一個mysql
mysql> select version(); +-----------+ | version() | +-----------+ | 8.0.18 | +-----------+ 1 row in set (0.00 sec)
假設(shè)只存在郵箱注冊：
#建表語句 CREATE TABLE `user_1` ( `id` int(10) unsigned NOT NULL AUTO_INCREMENT COMMENT '主鍵', `email` varchar(32) NOT NULL DEFAULT '' COMMENT '郵箱', `name` varchar(11) DEFAULT '' COMMENT '名字', PRIMARY KEY (`id`), UNIQUE KEY `uk-email` (`email`) ) ENGINE=InnoDB DEFAULT CHARSET=utf8;
insert數(shù)據(jù)
#第一次插入：
insert into user(email,name) values('aaa@qq.com','aaa');
Affected rows: 1, Time: 0.003000s
#再次插入同樣的數(shù)據(jù)：
insert into user(email,name) values('aaa@qq.com','aaa');
1062 - Duplicate entry 'aaa@qq.com' for key 'uk-email', Time: 0.005000s
此時對于唯一性來說是沒問題的，可以保證業(yè)務(wù)的email的唯一性。假設(shè)隨著業(yè)務(wù)的發(fā)展，此時需要增加手機(jī)號注冊功能，那么表中就需要增加手機(jī)號字段，且需要保證手機(jī)號和郵箱的關(guān)聯(lián)唯一性。
#建表語句，注意此時phone字段的默認(rèn)值為null CREATE TABLE `user_2` ( `id` int(10) unsigned NOT NULL AUTO_INCREMENT COMMENT '主鍵', `email` varchar(32) NOT NULL DEFAULT '' COMMENT '郵箱', `phone` char(11) DEFAULT NULL COMMENT '手機(jī)號', `name` varchar(11) DEFAULT '' COMMENT '名字', PRIMARY KEY (`id`), UNIQUE KEY `uk-email-phone` (`email`,`phone`) ) ENGINE=InnoDB DEFAULT CHARSET=utf8;
insert數(shù)據(jù)
insert into user_2(email,name) values('aaa@qq.com','aaa');
Affected rows: 1, Time: 0.003000s
insert into user_2(email,name) values('aaa@qq.com','aaa');
Affected rows: 1, Time: 0.003000s
insert into user_2(email,name) values('aaa@qq.com','aaa');
Affected rows: 1, Time: 0.003000s
insert into user_2(email,phone,name) values('bbb@qq.com','13333333333','bbb');
Affected rows: 1, Time: 0.003000s
insert into user_2(email,phone,name) values('bbb@qq.com','13333333333','bbb');
1062 - Duplicate entry 'bbb@qq.com-13333333333' for key 'uk-email-phone', Time: 0.002000s
此時會發(fā)現(xiàn)，不帶phone值得前三條數(shù)據(jù)都能插入成功，帶上郵箱和手機(jī)號的值卻能正常判斷唯一性
mysql> select * from user_2; +----+------------+-------------+------+ | id | email | phone | name | +----+------------+-------------+------+ | 1 | aaa@qq.com | NULL | aaa | | 2 | aaa@qq.com | NULL | aaa | | 3 | aaa@qq.com | NULL | aaa | | 4 | bbb@qq.com | 13333333333 | bbb | +----+------------+-------------+------+ 4 rows in set (0.00 sec)
這時就需要牽扯到MySQL的唯一索引機(jī)制了：在MySQL官方文檔中MySQL索引文檔，描述到：
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.
解釋一下：唯一索引創(chuàng)建一個約束，使得索引中的所有值都必須是不同的。如果嘗試添加一個鍵值與現(xiàn)有行匹配的新行，則會發(fā)生錯誤。如果在唯一索引中為列指定前綴值，則列值在前綴長度內(nèi)必須是唯一的。唯一索引允許包含空值的列有多個空值。
先看下explain執(zhí)行計劃：
mysql> explain select * from user_2 where email='aaa@qq.com' and phone is NULL; +----+-------------+--------+------------+------+----------------+----------------+---------+-------------+------+----------+-----------------------+ | id | select_type | table | partitions | type | possible_keys | key | key_len | ref | rows | filtered | Extra | +----+-------------+--------+------------+------+----------------+----------------+---------+-------------+------+----------+-----------------------+ | 1 | SIMPLE | user_2 | NULL | ref | uk-email-phone | uk-email-phone | 132 | const,const | 3 | 100.00 | Using index condition | +----+-------------+--------+------------+------+----------------+----------------+---------+-------------+------+----------+-----------------------+ 1 row in set, 1 warning (0.01 sec) mysql> mysql> explain select * from user_2 where email='bbb@qq.com' and phone='13333333333'; +----+-------------+--------+------------+-------+----------------+----------------+---------+-------------+------+----------+-------+ | id | select_type | table | partitions | type | possible_keys | key | key_len | ref | rows | filtered | Extra | +----+-------------+--------+------------+-------+----------------+----------------+---------+-------------+------+----------+-------+ | 1 | SIMPLE | user_2 | NULL | const | uk-email-phone | uk-email-phone | 132 | const,const | 1 | 100.00 | NULL | +----+-------------+--------+------------+-------+----------------+----------------+---------+-------------+------+----------+-------+ 1 row in set, 1 warning (0.00 sec)
有沒有發(fā)現(xiàn)一個有趣的現(xiàn)象，雖然兩個sql語句都使用到了uk-email-phone唯一索引，但是 第一條sql的type為ref 第二條sql的type為const 我們知道，explain執(zhí)行計劃中，const一般是主鍵查詢或者唯一索引查詢是才會出現(xiàn)，而ref一般是使用普通索引時出現(xiàn)。所以，可以得出結(jié)論，MySQL在底層對唯一索引的null值做了特殊處理。
我們通過查看源碼文件的1863行，有這么個注釋：
Scans a unique non-clustered index at a given index entry to determine whether a uniqueness violation has occurred for the key value of the entry. Set shared locks on possible duplicate records
意思是掃描給定索引項處的唯一非聚集索引以確定條目的鍵值是否發(fā)生唯一性沖突。對可能重復(fù)的記錄設(shè)置共享鎖。
也就是說row_ins_scan_sec_index_for_duplicate()該方法就是處理唯一索引的，繼續(xù)往下看，在1892行，有一串注釋：
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
如果二級索引是唯一的，但是唯一索引的字段存在NULL，則不會發(fā)生唯一性沖突，在此代碼中定義了NULL != NULL
繼續(xù)往下走，在1996行，走到了row_ins_dupl_error_with_rec()函數(shù)，該函數(shù)在1825行。在該函數(shù)中有以下代碼：
/* In a unique secondary index we allow equal key values if they
contain SQL NULLs
在唯一的二級索引中，如果包含sql NULL值
*/
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異常.
經(jīng)驗
唯一索引重復(fù)插入之終極解決方案：給字段設(shè)置空字符串初始值，NOT NULL DEFAULT ''即可，不要用null值作為初始值。
以上就是詳解MySQL的字段默認(rèn)null對唯一索引的影響的詳細(xì)內(nèi)容，更多關(guān)于MySQL字段默認(rèn)null唯一索引的資料請關(guān)注腳本之家其它相關(guān)文章！
相關(guān)文章
解決mysql數(shù)據(jù)庫導(dǎo)入sql文件不成功的問題
這篇文章主要介紹了解決mysql數(shù)據(jù)庫導(dǎo)入sql文件不成功的問題,具有很好的參考價值,希望對大家有所幫助,如有錯誤或未考慮完全的地方,望不吝賜教2023-11-11
MySQL 邏輯備份與恢復(fù)測試的相關(guān)總結(jié)
數(shù)據(jù)庫邏輯備份就是備份軟件按照我們最初所設(shè)計的邏輯關(guān)系，以數(shù)據(jù)庫的邏輯結(jié)構(gòu)對象為單位，將數(shù)據(jù)庫中的數(shù)據(jù)按照預(yù)定義的邏輯關(guān)聯(lián)格式一條一條生成相關(guān)的文本文件，以達(dá)到備份的目的。本文將具體介紹MySQL 邏輯備份的相關(guān)概念及如何做恢復(fù)測試。2021-05-05
淺談MySQL中優(yōu)化sql語句查詢常用的30種方法
本篇文章是對MySQL中優(yōu)化sql語句查詢常用的30種方法進(jìn)行了詳細(xì)的分析介紹，需要的朋友參考下2013-06-06
mysql存儲過程之循環(huán)語句（WHILE，REPEAT和LOOP）用法分析
這篇文章主要介紹了mysql存儲過程之循環(huán)語句（WHILE，REPEAT和LOOP）用法,結(jié)合實例形式分析了mysql存儲過程循環(huán)語句WHILE，REPEAT和LOOP的原理、用法及相關(guān)操作注意事項,需要的朋友可以參考下2019-12-12
執(zhí)行計劃是指一條SQL語句在經(jīng)過MySQL查詢優(yōu)化器的優(yōu)化會后,具體的執(zhí)行方式,下面這篇文章主要給大家介紹了關(guān)于MySQL執(zhí)行計劃的相關(guān)資料,文中通過示例代碼介紹的非常詳細(xì),需要的朋友可以參考下2022-04-04
這篇文章主要介紹了MySQL kill指令的使用方法，幫助大家更好的理解和使用MySQL，感興趣的朋友可以了解下2020-12-12
本文主要介紹了mysql的binlog三種配置模式小結(jié),主要是binlog_format的值有3個選項,文中通過示例代碼介紹的非常詳細(xì),對大家的學(xué)習(xí)或者工作具有一定的參考學(xué)習(xí)價值,需要的朋友們下面隨著小編來一起學(xué)習(xí)學(xué)習(xí)吧2024-07-07
利用Shell腳本實現(xiàn)遠(yuǎn)程MySQL自動查詢
本篇文章是對利用Shell腳本實現(xiàn)遠(yuǎn)程MySQL自動查詢的方法進(jìn)行了詳細(xì)的分析介紹，需要的朋友參考下2013-06-06
win10下mysql 8.0.11壓縮版安裝詳細(xì)教程
這篇文章主要為大家詳細(xì)介紹了win10下mysql 8.0.11壓縮版安裝詳細(xì)教程，具有一定的參考價值，感興趣的小伙伴們可以參考一下2018-05-05
關(guān)于SQL語句中的AND和OR執(zhí)行順序遇到的問題
在SQL語句中的AND和OR執(zhí)行順序中我們經(jīng)常會遇到一些問題，下面有簡單的解決方法，小編來和大家一起來看看2019-05-05