MySQL执行update操作后引起的问题及解决

共计 5486 个字符，预计需要花费 14 分钟才能阅读完成。

简介

Part1: 写在最前

上班正忙的不可开交呢，一个消息过来，得知研发人员误操作 MySQL 数据库了 …. 不带 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’ |

MySQL 执行 update 操作后引起的问题及解决

那么我想知道刚刚的误操作到底是不是生效了呢，为什么会出现差个引号结果就差这么多呢？

分析

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 存储引擎。他的原子操作是一行一行完成的。所以如果你中断这个过程，那就会更新 / 删除到哪里就到哪里了。

—— 总结 ——

通过本文，您能了解到 update 中使用了 and 这种错误语法带来的严重后果，以及在 SQL 语句执行完之前，ctrl +c 到底有没有效果~ 由于笔者的水平有限，编写时间也很仓促，文中难免会出现一些错误或者不准确的地方，不妥之处恳请读者批评指正。

本文永久更新链接地址 ：http://www.linuxidc.com/Linux/2017-11/148302.htm