MySQL不为人知的主键与唯一索引约束

本日和各人简朴聊聊MySQL的束缚主键与独一索引束缚：

PRIMARY KEY and UNIQUE Index Constraints

文章不长，担保有收成。

触发束缚检测的机缘：

• insert

• update

当检测到违背束缚时，差异存储引擎的处理赏罚举措是纷歧样的。

假如存储引擎支持事宜，SQL会自动回滚。

例子：

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

个中第二条insert会由于违背束缚，而导致回滚。

凡是可以行使：

show warnings; 

来查察违背束缚后的错误提醒。

假如存储引擎不支持事宜，SQL的执行会间断，此时也许会导致后续有切合前提的行不被操纵，呈现不切合预期的功效。

例子：

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执行后，猜猜会获得什么功效集？

意料一：2, 6, 7, 11

意料二：1, 5, 6, 10

.

.

.

都差池，正确谜底是：2, 5, 6, 10

第一行id=1，加1后，没有违背unique束缚，执行乐成；

第二行id=5，加1后，因为id=6的记录存在，违背uinique束缚，SQL终止，修改失败；

第三行id=6，第四行id=10便不再执行；

画外音：这太操蛋了，一个update语句，部门执行乐成，部门执行失败。

为了停止这种环境呈现，请行使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执行后，猜猜会产生什么？

插入id=10的记录，会违背unique束缚，此时执行update flag=’false’，于是有一行记录被update了。

这相等于执行：

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

细心看，insert的功效返回，提醒：

Query OK, 2 rows affected 

故意思么？

画外音：本文全部尝试，基于MySQL5.6。 

总结，对付主键与独一索引束缚：

• 执行insert和update时，会触发束缚搜查
• InnoDB违背束缚时，会回滚对应SQL
• MyISAM违背束缚时，会间断对应的SQL，也许造成不切合预期的功效集
• 可以行使 insert … on duplicate key 来指定触发束缚时的举措
• 凡是行使 show warnings; 来查察与调试违背束缚的ERROR

互联网大数据量高并发量营业，为了各人的身心康健，请行使InnoDB。

【编辑保举】

1. MySQL数据库怎样去掉数据库中一再记录？
2. 怎样行使Redis做MySQL的缓存
3. 阿里P8架构师谈：MySQL慢查询优化、索引优化、以及表等优化总结
4. 四种NoSQL数据库范例比对
5. 大牛理会怎样快速进修MySQL数据库秘笈

（编辑：湖南网）

【声明】本站内容均来自网络，其相关言论仅代表作者个人观点，不代表本站立场。若无意侵犯到您的权利，请及时与联系站长删除相关内容!