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

开发 开发工具 MySQL
今天和大家简单聊聊MySQL的约束主键与唯一索引约束,当检测到违反约束时,不同存储引擎的处理动作是不一样的,如果存储引擎支持事务,SQL会自动回滚。

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

  1. PRIMARY KEY and UNIQUE Index Constraints 

了解诡异异常。 触发约束检测的时机:

  • insert;
  • update;

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

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

例子:

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

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

通常可以使用:

  1. show warnings; 

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

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

 

例子:

  1. create table t2 ( 
  2. id int(10) unique 
  3. )engine=MyISAM
  4.   
  5. insert into t2 values(1); 
  6. insert into t2 values(5); 
  7. insert into t2 values(6); 
  8. insert into t2 values(10); 
  9.   
  10. update t2 set idid=id+1; 
  11.   

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的约束冲突,可以使用:

  1. insert … on duplicate key 

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

例子:

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

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

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

这相当于执行:

  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。

【本文为51CTO专栏作者“58沈剑”原创稿件,转载请联系原作者】

戳这里,看该作者更多好文

 

 

责任编辑:赵宁宁 来源: 51CTO专栏
相关推荐

2015-07-27 10:24:01

苹果中国

2020-06-22 13:48:08

SQL查询SELECT

2010-01-21 10:42:54

福布斯比尔·盖茨

2013-02-25 10:11:35

4GLTE商用网络

2020-12-04 10:11:26

Unsafejava并发包

2019-10-10 10:54:16

戴尔

2012-09-10 10:05:11

光纤入户光纤宽带

2013-11-27 15:48:56

移动中间件厂商

2018-06-03 08:49:21

2022-02-12 17:48:03

InnoDBMySQL查询数据

2011-04-27 16:34:06

withSQL Server

2016-12-16 13:07:30

云存储运营混合云

2012-01-13 16:00:05

爱国者冯军苹果

2010-09-17 16:21:33

系统升级

2019-07-09 14:42:17

SQLexplain索引

2016-01-05 13:52:05

Kotlin掌握语言

2023-10-21 12:42:19

模型训练

2017-02-27 16:54:20

HTTPS网络流量互联网

2011-08-17 10:53:16

Firefox 7

2009-04-30 09:01:25

微软操作系统Windows 7
点赞
收藏

51CTO技术栈公众号