MySQL更新一条已经存在的sql语句是怎么执行的_affected rows: 0

1. 问题描述

今天看到一个有意思的问题，就是Mysql更新一条已经存在的语句是怎么执行的，结果显示，匹配(rows matched)了一行，修改(Changed)了0行。，实际上执行完成sql会提示Affected rows: 0，是的，你没看错更新0行，那这就有意思了，mysql到底是更新了没有呢，具体是在哪一层更新的呢？根据之前写的文章《MySQL是如何执行一条SQL更新语句》知道更新sql语句是在server层先读取原来值，然后调用数据库引擎，最后由数据库引擎执行完成返回给server层，根据以上分析有三种情况。

仅从现象上看，MySQL内部在处理这个命令的时候，可以有以下三种选择：
1.更新都是先读后写的，MySQL读出数据，发现a的值本来就是2，不更新，直接返回，执行结束。
2.MySQL调用了InnoDB引擎提供的“修改为(1,2)”这个接口，但是引擎发现值与原来相同，不更新，直接返回；
3.InnoDB认真执行了"把这个值修改成(1,2)"这个操作，该加锁的加锁，该更新的更新。

2. 分析验证

1.首先关闭Navicat自动提交事务
关闭Navicat自动提交：菜单栏-》工具-》选项-》选中，自动开始事务

2.创建表

-- -测试update
create table demo(
id int(11) not null primary key auto_increment,
a int(11) default null
)engine=InnoDB;

-- 插入1,2
insert into demo values(1,2);
-- 查看插入是否成功
select * from demo;
1
2
3
4
5
6
7
8
9
10

3.每种情况分析
第一个选项是，MySQL读出数据，发现值与原来相同，不更新，直接返回，执行结束。这里我们可以用一个锁实验来确认。

Navicat开启两个窗口，sessionA不提交，sessionB阻塞住，等sessionA commit后，sessionB执行成功。session B的update 语句被blocked了，加锁这个动作是InnoDB才能做的，所以排除选项1。

第二个选项是，MySQL调用了InnoDB引擎提供的接口，但是引擎发现值与原来相同，不更新，直接返回。有没有这种可能呢？这里我用一个可见性实验来确认。
session A的第二个select 语句是一致性读（快照读)，它是不能看见session B的更新的。
现在它返回的是(1,3)，表示它看见了某个新的版本，这个版本只能是session A自己的update语句做更新的时候生成。

所以，答案应该是选项3，即：InnoDB认真执行了“把这个值修改成(1,2)"这个操作，该加锁的加锁，该更新的更新。

然后你会说，MySQL怎么这么笨，就不会更新前判断一下值是不是相同吗？如果判断一下，不就不用浪费InnoDB操作，多去更新一次了？
其实MySQL是确认了的。只是在这个语句里面，MySQL认为读出来的值，只有一个确定的(id=1), 而要写的是(a=3)，只从这两个信息是看不出来“不需要修改”的。作为验证，可以执行下面的例子
执行sql（注意关闭自动提交，开启两个不同的事务，按照顺序执行）

-- sessionA
begin;
-- 1,2
select * from demo;

-- sessionB
update demo set a=3 where id =1;
-- 1,3
select * from demo;
commit;

-- sessionA
update demo set a=3 where id =1 and a=3;
-- 1,2
select * from demo where id =1;
commit;
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16