MySQL危險而詭異的update操作和驚魂5分鐘

MySQL危險而詭異的update操作和驚魂5分鐘

簡介

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存儲引擎。 他的原子操作是一行一行完成的。 所以如果你中斷這個過程，那就會更新/刪除到哪里就到哪里了。

——總結——

通過本文，您能了解到update中使用了and這種錯誤語法帶來的嚴重后果，以及在SQL語句執行完之前，ctrl +c 到底有沒有效果~由于筆者的水平有限，編寫時間也很倉促，文中難免會出現一些錯誤或者不準確的地方，不妥之處懇請讀者批評指正。

喜歡的讀者可以點個贊來個關注，您的贊美和關注是對筆者繼續發文的最大鼓勵與支持！