背景
前幾天因作業需要，組長給我安排了一個資料清洗的任務，
任務：把 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)
博客園-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 代碼操作！
最后的最后
由本人水平所限，難免有錯誤以及不足之處， 螢屏前的靚仔靚女們 如有發現，懇請指出！
最后，謝謝你看到這里，謝謝你認真對待我的努力，希望這篇博客對你有所幫助！
你輕輕地點了個贊，那將在我的心里世界增添一顆明亮而耀眼的星！
轉載請註明出處，本文鏈接：https://www.uj5u.com/shujuku/510941.html
標籤：MySQL