我CA,一个SQL语句为啥只执行了一半?

今天和大家简单聊聊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;
我CA,一个SQL语句为啥只执行了一半?
来查看违反约束后的错误提示。
 
如果存储引擎不支持事务,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执行后,猜猜会发生什么?
我CA,一个SQL语句为啥只执行了一半?
插入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
架构师之路-分享可落地的架构文章
 
相关推荐
InnoDB并发如此高,原因竟然在这?
数据库索引,终于懂了
InnoDB索引,终于懂了
 
讨论
你遇到过MySQL的什么坑?求

发布者:糖太宗,转载请注明出处:https://www.qztxs.com/archives/science/technology/6621

(0)
打赏 微信扫一扫 微信扫一扫 支付宝扫一扫 支付宝扫一扫
上一篇 2022年5月14日 上午12:39
下一篇 2022年5月14日 上午12:40

相关推荐

  • Redis为什么又引入了多线程?难道作者也逃不过“真香定理”?

    相信你一定不止一次见过Redis是单线程模式,不过说实话那只是个老版本,这个问题是一位老哥的大厂面试题,跟我分享了一下。想着自己就知道redis6.0以前一直都是单线程,到了6的版本才加入了多线程,还不是很清楚,在多方打听并且搜索之下总结了这篇文章。 一、问题概述 Redis 6.0 之后的版本抛弃了单线程模型这一设计,原本使用单线程运行的 Redis 也开...

    2022年4月1日
    2000
  • mongodb开启认证

    修改配置文件,开启认证 1 2 3 4 # vi /etc/mongod.conf security: authorization:enablesd   创建密码 db.createUser({ user: “w11scan”, pwd: “w11scan”, roles: [{ role: “dbOwner”, db: “w11scan_conf...

    技术 2022年5月28日
    4800
  • MySQL到底支不支持哈希索引?(收藏)

    经常有朋友问,MySQL的InnoDB到底支不支持哈希索引?   对于InnoDB的哈希索引,确切的应该这么说: (1)InnoDB用户无法手动创建哈希索引,这一层上说,InnoDB确实不支持哈希索引; (2)InnoDB会自调优(self-tuning),如果判定建立自适应哈希索引(Adaptive Hash Index, AHI),能够提升查询效率,In...

    2022年5月10日
    2000
  • 业务层,到底需不需要服务化?

    很多公司,都实施了微服务架构,底层抽象出很多基础数据服务。 基础数据的访问服务化之后,架构如上: (1)站点业务通过RPC接口,调用基础数据服务; (2)基础数据服务通过DAO,从db/cache获取数据; (3)db/cache存储数据;   除了基础数据的访问需要服务化,业务层是否需要服务化?如果需要,什么时机进行服务化?这是本文要讨论的两个问题。 随着...

    2022年5月14日
    2500
  • OpenRASP 部署

    0x00 前言 上周一台web服务器部署单机版OpenRASP之后感觉还不错,想在公司内部推广一下,准备在一些后台系统的服务器上安装agent,单机版部署报警日志查看不是很方便,遂部署管理后台方便管理   0x01 准备 OpenRASP使用了 ElasticSearch 和 MongoDB 两种数据库。前者用来存储报警和统计信息,后者用来存储应用...

    2022年6月13日
    13800

发表回复

您的电子邮箱地址不会被公开。 必填项已用*标注

联系我们

400-800-8888

在线咨询: QQ交谈

邮件:admin@example.com

工作时间:周一至周五,9:30-18:30,节假日休息

关注微信