绑定完请刷新页面
取消
刷新

分享好友

×
取消 复制
我CA,一个SQL语句为啥只执行了一半?
2021-04-08 15:59:47
今天和大家简单聊聊MySQL的约束主键与索引约束
PRIMARY KEY and UNIQUE Index Constraints
了解诡异异常。
 
触发约束检测的时机:
(1)insert;
(2)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。
 
总结
对于主键与索引约束
(1)执行insert和update时,会触发约束检查;
(2)InnoDB违反约束时,会回滚对应SQL;
(3)MyISAM违反约束时,会中断对应的SQL,可能造成不符合预期的结果集;
(4)可以使用 insert … on duplicate key 来指定触发约束时的动作;
(5)通常使用 show warnings; 来查看与调试违反约束的ERROR;

互联网大数据量高并发量业务,为了大家的身心健康,请使用InnoDB
架构师之路-分享可落地的架构文章


分享好友

分享这个小栈给你的朋友们,一起进步吧。

SQL Server专区
创建时间:2020-05-14 14:17:02
SQL Server 是Microsoft 公司推出的关系型数据库管理系统
展开
订阅须知

• 所有用户可根据关注领域订阅专区或所有专区

• 付费订阅:虚拟交易,一经交易不退款;若特殊情况,可3日内客服咨询

• 专区发布评论属默认订阅所评论专区(除付费小栈外)

技术专家

查看更多
  • 小雨滴
    专家
戳我,来吐槽~