MySQL主键与唯一索引约束的核心差异详解

在MySQL中，当我们执行INSERT或UPDATE操作时，数据库会对主键和唯一索引约束进行检查。如果使用了支持事务的InnoDB存储引擎，一旦违反约束，会自动回滚整个SQL语句；而在处理海量数据、高并发的互联网业务场景时，确保数据的一致性和操作的原子性尤为重要，因此强烈建议大家选择InnoDB作为默认存储引擎。

今天我们来聊聊MySQL里的主键约束与唯一索引约束：PRIMARY KEY和UNIQUE Index Constraints。

文章不长，但保证你能有所收获。

什么情况下会触发主键与唯一索引约束检查？

执行INSERT语句时；执行UPDATE语句时；

当检测到违反约束时，不同存储引擎的处理动作一样吗？

不一样。

(1) 第一种情况：如果存储引擎支持事务，SQL会自动回滚。

举个例子：

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

其中第二条insert语句会因为违反主键约束而导致整条插入操作被回滚。

我们通常可以使用：

show warnings;

来查看违反约束后的具体错误提示信息。

(2) 第二种情况：如果存储引擎不支持事务，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后变为2，没有违反unique约束，执行成功；
第二行id=5，加1后变为6，由于表中已存在id=6的记录，违反了unique约束，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’，于是有一条记录的flag字段被更新了。

这相当于执行了：

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

仔细看，INSERT语句的返回结果提示：

Query OK, 2 rows affected

这有意思么？

画外音：本文所有实验，均基于MySQL 5.6版本进行。

总结

关于主键与唯一索引约束：

执行INSERT和UPDATE时，会触发约束检查；
InnoDB违反约束时，会回滚对应SQL语句；
MyISAM违反约束时，会中断对应SQL的执行，可能造成不符合预期的结果集；
可以使用 insert … on duplicate key 来指定触发约束时需要执行的操作；
通常使用 show warnings; 来查看与调试违反约束的错误信息；

在处理互联网海量数据、高并发业务时，为了保障数据一致性与开发者的身心舒畅，请务必使用InnoDB存储引擎。

知其然，更应知其所以然。

掌握其背后的思路，远比记住结论更重要。