今天和大家簡單聊聊MySQL的約束主鍵與***索引約束：

PRIMARY KEY and UNIQUE Index Constraints

文章不長，保證有收獲。

觸發(fā)約束檢測的時機：

• insert 

• update

當檢測到違反約束時，不同存儲引擎的處理動作是不一樣的。

如果存儲引擎支持事務(wù)，SQL會自動回滾。

例子： 

create table t1 (  
id int(10) primary key  
)engine=innodb;  
insert into t1 values(1);  
insert into t1 values(1); 

其中第二條insert會因為違反約束，而導(dǎo)致回滾。

通?？梢允褂茫?nbsp;

show warnings; 

來查看違反約束后的錯誤提示。

如果存儲引擎不支持事務(wù)，SQL的執(zhí)行會中斷，此時可能會導(dǎo)致后續(xù)有符合條件的行不被操作，出現(xiàn)不符合預(yù)期的結(jié)果。

例子： 

create table t2 (  
id int(10) unique  
)engine=MyISAM;  
insert into t2 values(1);  
insert into t2 values(5);  
insert into t2 values(6);  
insert into t2 values(10);  
update t2 set id=id+1; 

update執(zhí)行后，猜猜會得到什么結(jié)果集？

猜想一：2, 6, 7, 11

猜想二：1, 5, 6, 10

.

.

.

都不對，正確答案是：2, 5, 6, 10

***行id=1，加1后，沒有違反unique約束，執(zhí)行成功；

第二行id=5，加1后，由于id=6的記錄存在，違反uinique約束，SQL終止，修改失?。?/p>

第三行id=6，第四行id=10便不再執(zhí)行；

畫外音：這太操蛋了，一個update語句，部分執(zhí)行成功，部分執(zhí)行失敗。

為了避免這種情況出現(xiàn)，請使用InnoDB存儲引擎，InnoDB在遇到違反約束時，會自動回滾update語句，一行都不會修改成功。

畫外音：大家把存儲引擎換成InnoDB，把上面的例子再跑一遍，印象更加深刻。

另外，對于insert的約束沖突，可以使用：

insert … on duplicate key

指出在違反主鍵或***索引約束時，需要進行的額外操作。

例子： 

create table t3 (  
id int(10) unique,  
flag char(10) default 'true'  
)engine=MyISAM;  
insert into t3(id) values(1);  
insert into t3(id) values(5);  
insert into t3(id) values(6);  
insert into t3(id) values(10);  
insert into t3(id) values(10) on duplicate key update flag='false';  

insert執(zhí)行后，猜猜會發(fā)生什么？

插入id=10的記錄，會違反unique約束，此時執(zhí)行update flag=’false’，于是有一行記錄被update了。 

這相當于執(zhí)行： 

update t3 set flag='false' where id=10;  

仔細看，insert的結(jié)果返回，提示： 

Query OK, 2 rows affected 

有意思么？

畫外音：本文所有實驗，基于MySQL5.6。 

總結(jié)，對于主鍵與***索引約束：

• 執(zhí)行insert和update時，會觸發(fā)約束檢查
• InnoDB違反約束時，會回滾對應(yīng)SQL
• MyISAM違反約束時，會中斷對應(yīng)的SQL，可能造成不符合預(yù)期的結(jié)果集
• 可以使用 insert … on duplicate key 來指定觸發(fā)約束時的動作
• 通常使用 show warnings; 來查看與調(diào)試違反約束的ERROR 

互聯(lián)網(wǎng)大數(shù)據(jù)量高并發(fā)量業(yè)務(wù)，為了大家的身心健康，請使用InnoDB。