MySQL危險(xiǎn)而詭異的update操作和驚魂5分鐘

MySQL危險(xiǎn)而詭異的update操作和驚魂5分鐘

簡介

Part1:寫在最前

上班正忙的不可開交呢，一個(gè)消息過來，得知研發(fā)人員誤操作數(shù)據(jù)庫了....不帶where條件，整表更新Orz,還讓不讓人好好活了，心中一萬只XXX啊~無奈，分清事情的輕重，優(yōu)先處理這起事故。

在簡單溝通后，了解到事故的原因是研發(fā)人員使用update忘記帶where條件。這本身沒什么詭異的，詭異的是在決定要不要進(jìn)行恢復(fù)的時(shí)候，筆者稍微猶豫了一下，因?yàn)榭雌饋硎遣恍枰謴?fù)的，那么具體是什么樣的情況呢？

Part2:危險(xiǎn)場景再現(xiàn)

研發(fā)人員update使用了錯(cuò)誤的語法，本意是update helei3 set a='1' where b='a';

結(jié)果寫成了update helei3 set a='1' and b='a';

這樣的話使得helei3這張表的a列被批量修改為0或1。

過了幾秒鐘，發(fā)現(xiàn)寫錯(cuò)并且已經(jīng)敲了回車后，此時(shí)update語句還沒有更新完，立即ctrl+c

那么數(shù)據(jù)到底有沒有被寫臟？

復(fù)現(xiàn)

Part1:創(chuàng)建所需表

首先我們創(chuàng)建測(cè)試表，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

表中數(shù)據(jù)如下

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:錯(cuò)誤語句生成

我們都知道，update的語法是update tablename set col1=val,col2=val2 where xxx;

那么當(dāng)逗號(hào)換成了and，會(huì)出現(xiàn)什么樣的嚴(yán)重后果呢？

這個(gè)時(shí)候由于沒有where條件，導(dǎo)致整表更新，那猜猜看后續(xù)結(jié)果是什么

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)

沒錯(cuò)，這個(gè)SQL將a列整表更新為0，而之所以第一個(gè)a=1是由于a='1' and b='a'這個(gè)條件是真，所以為1。

Part3:ctrl+c

了解Part2后，我們?cè)倏聪庐?dāng)update全表更新發(fā)現(xiàn)誤操作后立即ctrl+c能不能回滾避免誤操作。

提前準(zhǔn)備好一張50萬數(shù)據(jù)的表

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列并沒有出現(xiàn)部分更新為1的情況，也就是說整表更新的這條操作回滾了。

細(xì)心點(diǎn)可以看到binlog pos號(hào)也沒有發(fā)生變化

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:詭異

前三章看完后，我們來看下有什么地方是詭異的，在生產(chǎn)環(huán)境中，由于不知道剛剛那條SQL是否已經(jīng)更新了部分?jǐn)?shù)據(jù)，我們采取了這種方式來驗(yàn)證。

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)

發(fā)現(xiàn)數(shù)據(jù)不一致，生產(chǎn)環(huán)境的更唬人一些，列中并沒有存儲(chǔ)0，而都是字母或純數(shù)字，當(dāng)我執(zhí)行上述兩個(gè)SQL的時(shí)候，發(fā)現(xiàn)結(jié)果差了非常多，還爆出了很多的warnings。

| Warning | 1292 | Truncated incorrect DOUBLE value: 'XXX'  |

那么我想知道剛剛的誤操作到底是不是生效了呢，為什么會(huì)出現(xiàn)差個(gè)引號(hào)結(jié)果就差這么多呢？

分析

Part1:構(gòu)建數(shù)據(jù)

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:查詢對(duì)比

那么這時(shí)我們執(zhí)行一條查詢會(huì)有兩種結(jié)果

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)

可以看出，當(dāng)包含字母的時(shí)候，mysql認(rèn)為=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存儲(chǔ)引擎符合事務(wù)的ACID特性。 它將一次完成所有操作，或者在中斷時(shí)不會(huì)執(zhí)行操作和回滾。 InnoDB也是MySQL 5.5及以上版本的默認(rèn)引擎。

但是對(duì)于非事務(wù)性的MyISAM存儲(chǔ)引擎。 他的原子操作是一行一行完成的。 所以如果你中斷這個(gè)過程，那就會(huì)更新/刪除到哪里就到哪里了。

——總結(jié)——

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

喜歡的讀者可以點(diǎn)個(gè)贊來個(gè)關(guān)注，您的贊美和關(guān)注是對(duì)筆者繼續(xù)發(fā)文的最大鼓勵(lì)與支持！