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

柔情只为你懂 2024-03-31 14:55 48阅读 0赞

#### MySQL更新一条已经存在的sql语句是怎么执行的 ####

*  1. 问题描述
 *  2. 分析验证

## 1. 问题描述 ##

今天看到一个有意思的问题，就是Mysql更新一条已经存在的语句是怎么执行的，结果显示，匹配(rows matched)了一行，修改(Changed)了0行。，实际上执行完成sql会提示Affected rows: 0，是的，你没看错更新0行，那这就有意思了，mysql到底是更新了没有呢，具体是在哪一层更新的呢？根据之前写的文章[《MySQL是如何执行一条SQL更新语句》][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;

3.每种情况分析  
第一个选项是，MySQL读出数据，发现值与原来相同，不更新，直接返回，执行结束。这里我们可以用一个锁实验来确认。  
![在这里插入图片描述][c7e9585ccc824e3690c73261fc1996a2.png]  
Navicat开启两个窗口，sessionA不提交，sessionB阻塞住，等sessionA commit后，sessionB执行成功。session B的update 语句被blocked了，加锁这个动作是InnoDB才能做的，所以排除选项1。

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

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

然后你会说，MySQL怎么这么笨，就不会更新前判断一下值是不是相同吗？如果判断一下，不就不用浪费InnoDB操作，多去更新一次了？  
其实MySQL是确认了的。只是在这个语句里面，MySQL认为读出来的值，只有一个确定的(id=1), 而要写的是(a=3)，只从这两个信息是看不出来“不需要修改”的。作为验证，可以执行下面的例子  
![在这里插入图片描述][de11522aacb342f69b88d66e9fa75ca4.png]执行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;

[MySQL_SQL]: https://blog.csdn.net/m0_37583655/article/details/127662686
[c7e9585ccc824e3690c73261fc1996a2.png]: https://image.dandelioncloud.cn/pgy_files/images/2024/03/31/a32b984271a84c4ea5f03a47798bb955.png
[eaab25e2025c4828b13f95c7d785a872.png]: https://image.dandelioncloud.cn/pgy_files/images/2024/03/31/7e0eec0223524d2c9abb451bc94a2559.png
[de11522aacb342f69b88d66e9fa75ca4.png]: https://image.dandelioncloud.cn/pgy_files/images/2024/03/31/bfe8ceb2ddc44d619b6ba6ae8031c107.png