本文主要向大家介绍了MySQL数据库之MySQL危险而诡异的update操作和惊魂5分钟 ,通过具体的内容向大家展现,希望对大家学习MySQL数据库有所帮助。
简介
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 + croot@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 causeroot@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)本文由职坐标整理并发布,希望对同学们学习MySQL有所帮助,更多内容请关注职坐标数据库MySQL数据库频道!