今天和大家簡(jiǎn)單聊聊MySQL的約束主鍵與唯一索引約束：
PRIMARY KEY and UNIQUE Index Constraints
文章不長(zhǎng)，保證有收獲。
觸發(fā)約束檢測(cè)的時(shí)機(jī)：
- insert
- update
當(dāng)檢測(cè)到違反約束時(shí)，不同存儲(chǔ)引擎的處理動(dòng)作是不一樣的。
如果存儲(chǔ)引擎支持事務(wù)，SQL會(huì)自動(dòng)回滾。
例子：
create table t1 (
id int(10) primary key
)engine=innodb;
insert into t1 values(1);
insert into t1 values(1);
其中第二條insert會(huì)因?yàn)檫`反約束，而導(dǎo)致回滾。
通常可以使用：
show warnings;
來(lái)查看違反約束后的錯(cuò)誤提示。
如果存儲(chǔ)引擎不支持事務(wù)，SQL的執(zhí)行會(huì)中斷，此時(shí)可能會(huì)導(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í)行后，猜猜會(huì)得到什么結(jié)果集？
猜想一：2, 6, 7, 11
猜想二：1, 5, 6, 10
.
.
.
都不對(duì)，正確答案是：2, 5, 6, 10
第一行id=1，加1后，沒(méi)有違反unique約束，執(zhí)行成功；
第二行id=5，加1后，由于id=6的記錄存在，違反uinique約束，SQL終止，修改失敗；
第三行id=6，第四行id=10便不再執(zhí)行；
畫(huà)外音：這太操蛋了，一個(gè)update語(yǔ)句，部分執(zhí)行成功，部分執(zhí)行失敗。
為了避免這種情況出現(xiàn)，請(qǐng)使用InnoDB存儲(chǔ)引擎，InnoDB在遇到違反約束時(shí)，會(huì)自動(dòng)回滾update語(yǔ)句，一行都不會(huì)修改成功。
畫(huà)外音：大家把存儲(chǔ)引擎換成InnoDB，把上面的例子再跑一遍，印象更加深刻。
另外，對(duì)于insert的約束沖突，可以使用：
insert … on duplicate key
指出在違反主鍵或唯一索引約束時(shí)，需要進(jìn)行的額外操作。
例子：
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í)行后，猜猜會(huì)發(fā)生什么？
插入id=10的記錄，會(huì)違反unique約束，此時(shí)執(zhí)行update flag=’false’，于是有一行記錄被update了。
這相當(dāng)于執(zhí)行：
update t3 set flag='false' where id=10;
仔細(xì)看，insert的結(jié)果返回，提示：
Query OK, 2 rows affected
有意思么？
畫(huà)外音：本文所有實(shí)驗(yàn)，基于MySQL5.6。
總結(jié)，對(duì)于主鍵與唯一索引約束：
- 執(zhí)行insert和update時(shí)，會(huì)觸發(fā)約束檢查
- InnoDB違反約束時(shí)，會(huì)回滾對(duì)應(yīng)SQL
- MyISAM違反約束時(shí)，會(huì)中斷對(duì)應(yīng)的SQL，可能造成不符合預(yù)期的結(jié)果集
- 可以使用 insert … on duplicate key 來(lái)指定觸發(fā)約束時(shí)的動(dòng)作
- 通常使用 show warnings; 來(lái)查看與調(diào)試違反約束的ERROR
