我操作MySQL的驚險一幕_ZenDei技術網路在線

前幾天因工作需要，組長給我安排了一個數據清洗的任務。任務：把 A 表的數據洗到 B 表。我的第一反應，什麼是「洗」？洗數據是什麼？洗錢我倒是知道。 ...

背景

前幾天因工作需要，組長給我安排了一個數據清洗的任務。

任務：把 A 表的數據洗到 B 表。

我的第一反應，什麼是「洗」？洗數據是什麼？洗錢我倒是知道。

不過我不能慌啊，於是問了問組長。

我：組長，把 A 表的數據洗到 B 表是什麼意思？

組長一臉無奈，手捂住臉，恨鐵不成鋼，然後調整過來，還是很耐心地跟我講的，大概意思就是我們現在 B 表需要 A 表的數據， A 表中和 B 表中欄位含義一樣，但是值可能不一樣，這就需要我們進行處理，在將 A 表數據搞到 B 表的過程中，把數據搞正確。

基於我理解能力有限，當時並不是很懂所謂的「洗數據」，而且這個 A 表的欄位也和 B 表的欄位沒怎麼對上，A 的欄位明顯多於 B 的欄位，某些欄位命名也和 B 不一樣，但是表達的意思是一樣的，該如何洗？

於是瘋狂搜索如何洗數據！

我這裡就舉個例子來說明，分別給出 A 表和 B 表，當然我只列出了一部分欄位，現在假設就這麼多欄位。

A 表

A 表欄位：name, province_id, city_id, area_id, tech_id, crop_id, field_id, create_time, update_time, xxx, yyy, zzz, ...

A 表的欄位是多於 B 表的，我需要將 A 表的數據洗到 B 表，只處理我需要的欄位，不需要的就不用理。

A 表中有 2 萬多條記錄，B 表我自己插入的有 200 多條記錄。

當然，給出 A 表後，還給了個實體模型（JavaBean，Entity，超多種叫法，花里胡哨的）

public class A {
    private String name;
    private Long provinceId;
    private Long cityId;
    private Long areaId;
    private Long techId;
    private Long cropId;
    private Date createTime;
    private Date updateTime;
    ...
}

B 表

B 表欄位：name, province_id, city_id, area_id, mature_id, crop_id, create_time, update_time

還原現場

下麵我模擬測試環境中資料庫的那兩張表，上面是 A 表（2 萬多條記錄，這裡我只模擬了 7 條），下麵的 B 表（200 多條記錄）。

洗數據思路

首先，我是先找出 A 表中能和 B 表對上意思的欄位，然後將 A 表數據全部插入到 B 表中。

於是，我便找出瞭如下這些欄位：

name, province_id, city_id, area_id, tech_id, crop_id, update_time

之後在 B 表中對新插入的數據進行處理，即洗數據。

寫 SQL 操作

主要的 SQL 語句是：

INSERT INTO 目標表(欄位1, 欄位2, ...) SELECT 欄位1, 欄位2, ... FROM 來源表 WHERE 條件;

於是，便這樣操作：

INSERT INTO b(name, province_id, city_id, area_id, mature_id, crop_id, update_time) 
SELECT name, province_id, city_id, area_id, tech_id, crop_id, update_time FROM a;

操作是正常的，成功將 A 中 2 萬多條記錄全部插入到了 B 中。

但是！我漏了一個欄位，就是 create_time 。

於是，想著對這個欄位進行更新，將 A 中這個欄位更新到 B 中。

於是寫了一條SQL語句。

UPDATE b(create_time) SET create_time = (SELECT create_time FROM a);

> 1064 - You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near '(create_time) SET create_time = (SELECT create_time FROM a)' at line 1

噢，看來不能這樣更新啊！（一個大嘴巴子過去，寫錯了還沒發現）

換種寫法：

UPDATE b AS tb, (SELECT create_time FROM a) AS ta SET tb.create_time = ta.create_time;

可以，噩夢開始了！

更新了非常久，看著十幾秒的SQL執行到六七百秒還沒執行完，心裡著實很慌！眼看著資料庫可能會崩，我不得不向我的組長求救了！

這時問題出現了，有人資料庫連接不上了，可見這嚴重性，已經影響到其他人的使用了！

於是我組長來幫我處理了，想著 kill 掉我的 navicat ，但是 kill 掉還是沒效果，畢竟這個 SQL 已經在執行了。

我：能不能重啟這個 MySQL 服務？

組長一波連環炮過來了

組長：重啟？你知道這個 MySQL 有多少人在用嗎？又不只是我們在用，你重啟其他人怎麼搞？

我啞口無言，心裡非常忐忑，想著闖禍了，GG，就看著他操作。沒過多久，經過他的一頓操作，終於解決了這個問題。我的心裡的一塊懸著的大石終於放下了，還好解決了。組長牛逼，救世主！

我：如何解決的？

組長：將這個事務回滾解決的，你更新的 SQL 怎麼寫的？（努力回想）

於是寫出了上面寫的 SQL：

UPDATE b AS tb, (SELECT create_time FROM a) AS ta SET tb.create_time = ta.create_time;

組長：你為什麼這樣寫？不應該把子查詢寫在 SET tb.create_time 後面嗎？

我：對啊，我一開始就是把這個子查詢寫在它後面的，但是提示我語法錯誤，我就換了一種寫法。

組長：那你寫寫你說提示錯誤的 SQL。

於是我又丟出來一個 SQL：

UPDATE b SET create_time = (SELECT create_time FROM a);

實際上，這條 SQL 也是不行的，子查詢返回的結果不止一行，而當前 SET 是更新某一行的。

正確的寫法是：

UPDATE b AS tb 
SET create_time = (SELECT create_time FROM a AS ta WHERE tb.id = ta.id AND tb.name = ta.name)

成功更新B表

博客園-SQL把一個表中數據更新到另一個表的多種方法

最後組長深思，你 B 表已經有 2 萬多條記錄了，A 表也有 2 萬多條記錄，你這樣更新，每一次都需要子查詢查出 A 表的 2 萬多條記錄，B 也有 2 萬多條記錄，這樣成笛卡爾積了，你知道什麼是笛卡爾積吧？2 萬 × 2 萬 = 4 億的記錄行了，難怪這麼久。

讓我重新操作，那麼現在我會在原先的 SQL 加上 WHERE 條件，這樣寫：

UPDATE b AS tb, (SELECT create_time, name FROM a) AS ta 
SET tb.create_time = ta.create_time
WHERE tb.id = ta.id AND tb.name = ta.name;

總結

情況：漏了某一個欄位 X，需要將 A 表的這個欄位列值更新到 B 表

條件：A 中的 id 欄位的值等於 B表中的 id 欄位的值且 A 中的 name 欄位的值等於 B 中 name 欄位的值（條件為什麼這樣寫？）。

條件這樣寫主要是因為 表和表之間的關聯關係 可能有多個欄位，此處只選二個欄位，多個依此類推。

操作：

一張表的數據插入到另一張表，可以這樣寫：

INSERT INTO 目標表(欄位1, 欄位2, ...) SELECT 欄位1, 欄位2, ... FROM 來源表 WHERE 條件;

批量更新一張表的某個欄位到另一張表，那麼 SQL 可以類似這樣寫：

# 寫法一
UPDATE b AS tb 
SET create_time = (SELECT create_time FROM a AS ta WHERE tb.id = ta.id AND tb.name = ta.name)

# 寫法二
UPDATE b AS tb, (SELECT create_time, name FROM a) AS ta 
SET tb.create_time = ta.create_time
WHERE tb.id = ta.id AND tb.name = ta.name;

所謂洗數據：在我的理解中，就是把舊數據，按照新數據的規則把舊數據不正確的值修改正確，同時把這些舊數據插入到新數據中，成為新數據。舉個例子，A 表中的 province_id，值為 10 代表廣東，而 B 表中的 province_id ，值為 19 代表廣東，把 A 表中的數據插入到 B 表的過程中，把值為 10 修改為 19，這樣插入的數據才能在 B 表中正確表示廣東，這個過程就是「洗數據」，當然，也可以在插入後再修改，不管過程是怎樣，最終能把數據的值修改正確，就是洗數據！

教訓：

數據量大的表，少在測試環境操作，要操作儘量保證寫的 SQL 是正確的，能在本地環境操作就現在本地環境操作！
能用 Java 代碼進行操作，優先寫 Java 代碼操作！

最後的最後

由本人水平所限，難免有錯誤以及不足之處， 屏幕前的靚仔靚女們 如有發現，懇請指出！

最後，謝謝你看到這裡，謝謝你認真對待我的努力，希望這篇博客對你有所幫助！

你輕輕地點了個贊，那將在我的心裡世界增添一顆明亮而耀眼的星！