加入收藏 | 设为首页 | 会员中心 | 我要投稿 云计算网_泰州站长网 (http://www.0523zz.com/)- 视觉智能、AI应用、CDN、行业物联网、智能数字人!
当前位置: 首页 > 站长学院 > MySql教程 > 正文

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

发布时间:2018-10-06 15:22:33 所属栏目:MySql教程 来源:58沈剑
导读:9月15日技术沙龙 | 与东华软件、AWS、京东金融、饿了么四位大咖探讨精准运维! 今天和大家简单聊聊MySQL的约束主键与唯一索引约束: PRIMARY KEY and UNIQUE Index Constraints 文章不长,保证有收获。 触发约束检测的时机: insert update 当检测到违反约
9月15日技术沙龙 | 与东华软件、AWS、京东金融、饿了么四位大咖探讨精准运维!

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

今天和大家简单聊聊MySQL的约束主键与唯一索引约束:

PRIMARY KEY and UNIQUE Index Constraints

文章不长,保证有收获。

触发约束检测的时机:

  • insert

  • update

当检测到违反约束时,不同存储引擎的处理动作是不一样的。

如果存储引擎支持事务,SQL会自动回滚。

例子:

  1. create table t1 (  
  2. id int(10) primary key  
  3. )engine=innodb;  
  4. insert into t1 values(1);  
  5. insert into t1 values(1); 

其中第二条insert会因为违反约束,而导致回滚。

通常可以使用:

  1. show warnings; 

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

来查看违反约束后的错误提示。

如果存储引擎不支持事务,SQL的执行会中断,此时可能会导致后续有符合条件的行不被操作,出现不符合预期的结果。

例子:

  1. create table t2 (  
  2. id int(10) unique  
  3. )engine=MyISAM;  
  4. insert into t2 values(1);  
  5. insert into t2 values(5);  
  6. insert into t2 values(6);  
  7. insert into t2 values(10);  
  8. 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

指出在违反主键或唯一索引约束时,需要进行的额外操作。

例子:

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

insert执行后,猜猜会发生什么?

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

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

这相当于执行:

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

仔细看,insert的结果返回,提示:

  1. 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数据库秘籍
【责任编辑:庞桂玉 TEL:(010)68476606】
点赞 0

(编辑:云计算网_泰州站长网)

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

    热点阅读