版版

MySQL

MySQL 如何快速複製、搬移資料表的常見三種問題

MySQL

MySQL

在網站開發、製作、資料分析時,MySQL 資料庫我們常常會遇到以下 3 個問題 :

  1. 如何複製現有資料表到指定的測試資料表中
  2. 如何依據時間、比數來對現有資料表進行分類
  3. 如何將過期資料進行備份,讓維持資料庫查詢效率

遇到這些問題,我們先在這依據問題,直接進行解答 :

如何複製現有資料表到指定的測試資料表中

INSERT '測試的資料表名稱' SELECT * FROM '要複製的資料表名稱'

如何依據時間、比數來對現有資料表進行分類

INSERT '測試 N 月資料表名稱' SELECT * FROM '要複製的資料表名稱'
WHERE time >= 'N月份資料開始時間' AND time < 'N月份資料結束時間';

如何將過期資料進行備份,讓維持資料庫查詢效率

INSERT '過期資料備份資料表' SELECT * FROM '現有資料表' WHERE time < '資料時間';
DELETE FROM '現有資料表' WHERE time < '資料時間';
OPTIMIZE TABLE '現有資料表';

如果你是新手,又或者你是第一次接觸,對 MySQL 語法及指令並不是很了解,接下來會針對上面問題一一做解說,希望能幫助你更加理解。

如何複製現有資料表到指定的測試資料表中

假設我們有一個 table 名叫 table_data,我們希望將裡面的資料,複製到測試資料表 table_data_test中,我們可以這樣做 :

INSERT table_data_test SELECT * FROM table_data

用法解說

INSERT '測試的資料表名稱' SELECT * FROM '要複製的資料表名稱'

對這段語法意思不太明白沒關係,以下用()來加上解說,你可以這樣理解 :

INSERT(寫入) '測試的資料表名稱' SELECT(資料查詢) *(所有資料欄位) FROM(資料來自於哪) '要複製的資料表名稱'

如何依據時間、比數來對現有資料表進行分類

同樣,我們先假設有一個 table 名叫 table_data,我們希望依據時間月份來分類,接下來我們用數字代表月份,將 1~3月的資料,分別寫入到 test_01 , test_02 , test_03三張資料表中,我們可以這樣做 :

INSERT test_01 SELECT * FROM table_data 
WHERE time >= '2020-01-01 00:00:00' AND time < '2020-02-01 00:00:00';

INSERT test_01 SELECT * FROM table_data 
WHERE time >= '2020-02-01 00:00:00' AND time < '2020-03-01 00:00:00';

INSERT test_01 SELECT * FROM table_data 
WHERE time >= '2020-03-01 00:00:00' AND time < '2020-04-01 00:00:00';

在這裡,因為我們會需要分別寫入3張不同的資料表,所以可以看到上面我們會有三個寫入語法,分別針對1~3月做個別處理,但其實語法只有一個,用法解說

INSERT '測試 N 月份資料 資料表名稱' SELECT * FROM '要複製的資料表名稱'
WHERE time >= 'N月份資料開始時間' AND time < 'N月份資料結束時間';


同樣,如果你對對這段指令不熟悉,以下用()來加上解說,你可以這樣理解 :

INSERT(寫入) '測試的資料表名稱' SELECT(資料查詢) *(所有資料欄位) FROM(資料來自於哪) '要複製的資料表名稱' ;(寫入指令到這結束)

如何將過期資料進行備份,讓維持資料庫查詢效率

假設我們目前有一個資料表 table_data ,我們希望將3月之前過期的資料,搬移到另外建立好的資料表 table_old,並確保現有的 table_data 只保留4月開始未過期的資料,這邊我們會需要分為三個步驟

如何將過期資料進行備份,這邊我們會需要分為三個步驟

  1. 將過期資料進行搬移

    INSERT table_old SELECT * FROM table_data WHERE time < '2020-04-01 00:00:00';

  2. 將現有資料表 table_data 過期資料刪除

    DELETE FROM table_data WHERE time < '2020-04-01 00:00:00';

  3. 優化資料表 table_data,釋放空間

    OPTIMIZE TABLE table_data;

第一步 將過期資料進行搬移

我們先將過期資料搬移,這個跟先前的用法一樣,只須要注意設定好搬移的時間。

INSERT '過期資料備份資料表' SELECT * FROM '現有資料表' WHERE time < '資料時間';

搬移的資料過多,有上百萬、上千萬筆,那這邊你會需要格外注意!

MySQL 執行的指令時,執行時間長短取決要處理的資料量,一旦你輸入的指令需要搬移上千萬筆資料,往往會造成短時間網站無法預覽,查詢過慢、卡住的狀況。你會需要等 MySQL 跑完,它才能在執行其他指令。

然而絕大多數網站都是24小時 online,你絕對不會希望他為了處理過期資料,造成網站面臨停機、當機的狀況;所以在執行指令之前,穩健保險的做法是你必須要先清楚要處理的資料量,如果資料量過多,請分批、分時處理。

第二步 將現有資料表 table_data 過期資料刪除

資料搬移完成後,我們就可以開始對過期的資料進行刪除,在這要注意的地方同第一步,再次強調【如果資料量過多,請分批、分時處理】,以免造成網站無法預覽。

DELETE FROM '現有資料表' WHERE time < '資料時間';

第三步 優化資料表 table_data,釋放空間

對於 MySQL 來說,你不斷新增資料,容量就不斷往上加,但當你刪除資料後,它所占去的容量並不會減少,官方對此提供了 OPTIMIZE TABLE 來優化資料表,用法如下 :

OPTIMIZE TABLE table_data;

用法說明

OPTIMIZE TABLE '你要優化的資料表名稱'

只要經過以上一、二步驟,就可以將過期的資料進行備份,以備日後查詢,避免資料過多查詢緩慢的狀況,再透過 OPTIMIZE TABLE 對資料表進行優化,便可以使資料表優化、容量減小

發佈留言