MySQL危险而诡异的update操作和惊魂5分钟
内容导读
互联网集市收集整理的这篇技术教程文章主要介绍了MySQL危险而诡异的update操作和惊魂5分钟，小编现在分享给大家，供广大互联网技能从业者学习和参考。文章包含6526字，纯文字阅读大概需要10分钟。
内容图文
简介
**Part1:**写在最前
上班正忙的不可开交呢，一个消息过来，得知研发人员误操作数据库了…不带where条件，整表更新Orz,还让不让人好好活了，心中一万只XXX啊~无奈，分清事情的轻重，优先处理这起事故。
在简单沟通后，了解到事故的原因是研发人员使用update忘记带where条件。这本身没什么诡异的，诡异的是在决定要不要进行恢复的时候，笔者稍微犹豫了一下，因为看起来是不需要恢复的，那么具体是什么样的情况呢？
**Part2:**危险场景再现
研发人员update使用了错误的语法，本意是update helei3 set a=‘1’ where b=‘a’;
结果写成了update helei3 set a=‘1’ and b=‘a’;
这样的话使得helei3这张表的a列被批量修改为0或1。
过了几秒钟，发现写错并且已经敲了回车后，此时update语句还没有更新完，立即ctrl+c
那么数据到底有没有被写脏？
复现
Part1:创建所需表
首先我们创建测试表，a列b列均为varchar类型
root@127.0.0.1 (helei)> show create table helei3\G
*************************** 1\. row ***************************
Table: helei3
Create Table: CREATE TABLE `helei3` (
`a` varchar(10) DEFAULT NULL,
`b` varchar(255) DEFAULT NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4
表中数据如下
root@127.0.0.1 (helei)> select * from helei3;
+------+------+
| a | b |
+------+------+
| 1 | a |
| 2 | b |
| 3 | c |
+------+------+
3 rows in set (0.00 sec)
Part2:错误语句生成
我们都知道，update的语法是update tablename set col1=val,col2=val2 where xxx;
那么当逗号换成了and，会出现什么样的严重后果呢？
这个时候由于没有where条件，导致整表更新，那猜猜看后续结果是什么
root@127.0.0.1 (helei)> update helei3 set a='1' and b='a';
root@127.0.0.1 (helei)> select * from helei3;
+------+------+
| a | b |
+------+------+
| 1 | a |
| 0 | b |
| 0 | c |
+------+------+
4 rows in set (0.00 sec)
没错，这个SQL将a列整表更新为0，而之所以第一个a=1是由于a=‘1’ and b='a’这个条件是真，所以为1。
Part3:ctrl+c
了解Part2后，我们再看下当update全表更新发现误操作后立即ctrl+c能不能回滚避免误操作。
提前准备好一张50万数据的表
root@127.0.0.1 (helei)> select count(*) from helei;
+----------+
| count(*) |
+----------+
| 500000 |
+----------+
1 row in set (0.06 sec)
root@127.0.0.1 (helei)> show create table helei\G
*************************** 1\. row ***************************
Table: helei
Create Table: CREATE TABLE `helei` (
`id` int(10) unsigned NOT NULL AUTO_INCREMENT,
`c1` int(10) NOT NULL DEFAULT '0',
`c2` int(10) unsigned DEFAULT NULL,
`c5` int(10) unsigned NOT NULL DEFAULT '0',
`c3` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
`c4` varchar(200) NOT NULL DEFAULT '',
PRIMARY KEY (`id`),
KEY `idx_c1` (`c1`),
KEY `idx_c2` (`c2`)
) ENGINE=InnoDB AUTO_INCREMENT=500001 DEFAULT CHARSET=utf8mb4
1 row in set (0.00 sec)
误操作整表更新后等待几秒立即ctrl + c
root@127.0.0.1 (helei)> update helei set c2=1;
^CCtrl-C -- sending "KILL QUERY 2133" to server ...
Ctrl-C -- query aborted.
^CCtrl-C -- sending "KILL 2133" to server ...
Ctrl-C -- query aborted.
ERROR 2013 (HY000): Lost connection to MySQL server during query
root@127.0.0.1 (helei)> select * from helei where c2=1;
Empty set (0.00 sec)
可以看到c2列并没有出现部分更新为1的情况，也就是说整表更新的这条操作回滚了。
细心点可以看到binlog pos号也没有发生变化
root@127.0.0.1 (helei)> show master status;
+------------------+-----------+--------------+------------------+
| File | Position | Binlog_Do_DB | Binlog_Ignore_DB |
+------------------+-----------+--------------+------------------+
| mysql-bin.000004 | 124886658 | | |
+------------------+-----------+--------------+------------------+
1 row in set (0.00 sec)
Part4:诡异
前三章看完后，我们来看下有什么地方是诡异的，在生产环境中，由于不知道刚刚那条SQL是否已经更新了部分数据，我们采取了这种方式来验证。
root@127.0.0.1 (helei)> select * from helei3 where a='0';
+------+------+
| a | b |
+------+------+
| 0 | b |
| 0 | c |
+------+------+
2 rows in set (0.00 sec)
root@127.0.0.1 (helei)> select * from helei3 where a=0;
+------+------+
| a | b |
+------+------+
| 0 | b |
| 0 | c |
| zz | zz |
+------+------+
3 rows in set (0.00 sec)
发现数据不一致，生产环境的更唬人一些，列中并没有存储0，而都是字母或纯数字，当我执行上述两个SQL的时候，发现结果差了非常多，还爆出了很多的warnings。
| Warning | 1292 | Truncated incorrect DOUBLE value: ‘XXX’ |
那么我想知道刚刚的误操作到底是不是生效了呢，为什么会出现差个引号结果就差这么多呢？
分析
Part1:构建数据
root@127.0.0.1 (helei)> insert into helei3 values('zz','zz');
root@127.0.0.1 (helei)> select * from helei3;
+------+------+
| a | b |
+------+------+
| 1 | a |
| 0 | b |
| 0 | c |
| zz | zz |
+------+------+
4 rows in set (0.00 sec)
Part2:查询对比
那么这时我们执行一条查询会有两种结果
root@127.0.0.1 (helei)> select * from helei3 where a='0';
+------+------+
| a | b |
+------+------+
| 0 | b |
| 0 | c |
+------+------+
2 rows in set (0.00 sec)
root@127.0.0.1 (helei)> select * from helei3 where a=0;
+------+------+
| a | b |
+------+------+
| 0 | b |
| 0 | c |
| zz | zz |
+------+------+
3 rows in set (0.00 sec)
这是为什么呢？
Part3:root cause
root@127.0.0.1 (helei)> select 'zz'=0;
+--------+
| 'zz'=0 |
+--------+
| 1 |
+--------+
1 row in set, 1 warning (0.00 sec)
root@127.0.0.1 (helei)> select 'zz3'=0;
+---------+
| 'zz3'=0 |
+---------+
| 1 |
+---------+
1 row in set, 1 warning (0.00 sec)
root@127.0.0.1 (helei)> select '3'=0;
+-------+
| '3'=0 |
+-------+
| 0 |
+-------+
1 row in set (0.00 sec)
可以看出，当包含字母的时候，mysql认为=0是真，并抛出warning。
root@127.0.0.1 (helei)> show warnings;
+---------+------+----------------------------------------+
| Level | Code | Message |
+---------+------+----------------------------------------+
| Warning | 1292 | Truncated incorrect DOUBLE value: 'zz' |
+---------+------+----------------------------------------+
1 row in set (0.00 sec)
Part4:MySQL Doc
In InnoDB, all user activity occurs inside a transaction. If autocommit mode is enabled, each SQL statement forms a single transaction on its own. By default, MySQL starts the session for each new connection with autocommit enabled, so MySQL does a commit after each SQL statement if that statement did not return an error. If a statement returns an error, the commit or rollback behavior depends on the error.
Part5:我的理解
InnoDB存储引擎符合事务的ACID特性。 它将一次完成所有操作，或者在中断时不会执行操作和回滚。 InnoDB也是MySQL 5.5及以上版本的默认引擎。
但是对于非事务性的MyISAM存储引擎。 他的原子操作是一行一行完成的。 所以如果你中断这个过程，那就会更新/删除到哪里就到哪里了。
内容总结
以上是互联网集市为您收集整理的MySQL危险而诡异的update操作和惊魂5分钟全部内容，希望文章能够帮你解决MySQL危险而诡异的update操作和惊魂5分钟所遇到的程序开发问题。 如果觉得互联网集市技术教程内容还不错，欢迎将互联网集市网站推荐给程序员好友。
内容备注
版权声明：本文内容由互联网用户自发贡献，该文观点与技术仅代表作者本人。本站仅提供信息存储空间服务，不拥有所有权，不承担相关法律责任。如发现本站有涉嫌侵权/违法违规的内容， 请发送邮件至 gblab@vip.qq.com 举报，一经查实，本站将立刻删除。
内容手机端
扫描二维码推送至手机访问。