一、說明

在OUTFILE /LOAD DATA 邏輯備份中強烈建議備份恢復均明確指定字符集CHARACTER SET charset_name，避免亂碼的出現發生。預設讀取character_set_database引數。

1.1 必須引數

使用該方式進行備份恢復，必須設定--secure-file-priv引數！！匯出匯入檔案必須在指定目錄下。

該引數為靜態引數，重啟生效！

下表為官方文件對該引數介紹：

Property	Value
Command-Line Format	--secure-file-priv=dir_name
System Variable	secure_file_priv
Scope	Global
Dynamic	No
Type	string
Default Value (>= 5.6.34)	platform specific
Default Value (<= 5.6.33)	empty string
Valid Values (>= 5.6.34)	empty string dirname NULL
Valid Values (<= 5.6.33)	empty string dirname

二、 OUTFILE 備份

Outfile備份是服務端命令，匯出的檔案只能在伺服器端。

2.1命令引數

	引數	含義
1	--fields-terminated-by=str	列與列之間分隔
2	--fields-enclosed-by=char	在每一個欄位的前後加上char這個字元
3	--fields-optionally-enclosed-by=char	在每一個非數字的欄位前後加上char這個字元
4	--fields-escaped-by=char	使用char去轉義特殊字元
5	--lines-terminated-by=str	行與行之間分隔   #linux \n   windows \r\n

注：2與3不能同時使用

2.2備份案例   

2.1.1 備份全表

示例：

select * from test into outfile '/home/mysql/test.txt' CHARACTER SET UTF8 fields terminated by '|' enclosed by '"' lines terminated by '\r\n';

2.1.2 備份其中幾列

示例：

select id,name from test into outfile '/home/mysql/test.txt' CHARACTER SET UTF8 fields terminated by '|' enclosed by '"' lines terminated by '\r\n';

2.1.3 備份處理之後的列

示例：

select id,name,id+1 from test into outfile '/home/mysql/test.txt' CHARACTER SET UTF8 fields terminated by '|' enclosed by '"' lines terminated by '\r\n';

2.1.4 按照條件備份

示例：

SELECT * into outfile '/home/mysql/test.txt ' fields terminated by ',' optionally enclosed by '"' lines terminated by '\n' FROM trans_transreq_20180828 a WHERE a.id > 999473433;

三、LOAD DATA 恢復

load data infile是MySQL中用來批量、快速匯入資料的一種方式。必須在mysqld程式（服務）啟動時執行。另外需要注意編碼方式避免出現亂碼現象。

由於安全原因，當讀取位於伺服器上的檔案時，檔案必須處於資料庫目錄或可被所有人讀取。另外，為了對伺服器上檔案使用load data infile，在伺服器主機上必須有file的許可權。

3.1關鍵字說明

關鍵字	作用
LOW_PRIORITY	等到沒有其他人讀這個表的時候，才把資料插入
LOCAL	指定local表明從客戶主機讀檔案
REPLACE | IGNORE	對唯一鍵記錄的處理,不指定直接報錯，後續忽略
PARTITION (partition_name,...)	將資料插入到指定分割槽
CHARACTER SET	字符集設定  預設讀取character_set_database
FIELDS TERMINATED BY	欄位值的分隔符，若不指定則預設為 '\t'
FIELDS ENCLOSED BY	欄位值的包括符，若不指定則預設為 ''
FIELDS ESCAPED BY	欄位值的轉義字元，若不指定則預設為'\\'
LINES TERMINATED BY	指定行分隔符，若不指定則預設為為系統的預設行分隔符（‘\r\n‘ on windows，'\n' on linux）
LINES STARTING BY	若指定該值為xxx，則MySQL會自動去掉xxx及其前面的字元，若某行不包含xxx，則該行將被忽略，若不指定預設為' ' 如： xxx"abc",1   something xxx"def",2   "ghi",3   實際： ("abc", 1), ("def", 2)
IGNORE LINES / ROWS	忽略檔案開頭的指定行，比如指定為2，那麼MySQL只會解析並插入第三行及後面的資料

3.2 恢復案例

匯入檔案均由該匯出命令生成

select * from test.test into outfile '/home/mysql/test.txt' CHARACTER SET UTF8 fields terminated by '|' enclosed by '"' lines terminated by '\r\n';

檔案內容（擷取）

 

3.2.1 恢復全表

前提條件：列數相同，型別符合要求

load data infile '/home/mysql/test.txt' into table test CHARACTER SET UTF8 fields terminated by '|' enclosed by '"' lines terminated by '\r\n';

3.2.2 恢復檔案中其中幾列

不需要第二列，即忽略第二列資料

load data infile '/home/mysql/test.txt' into table test  fields terminated by '|' enclosed by '"' lines terminated by '\r\n' (id,@dummy,create_time);

3.2.3 對INT型別做數學運算

load data infile '/home/mysql/test.txt' into table test  fields terminated by '|' enclosed by '"' lines terminated by '\r\n' (@dummy,name,create_time) set id= @dummy*2;

3.2.4 對字元型別做拼接

load data infile '/home/mysql/test.txt' into table test  fields terminated by '|' enclosed by '"' lines terminated by '\r\n' (id,@dummy,create_time) set name=  concat (@dummy,'a');

3.2.5 對時間欄位做變化

load data infile '/home/mysql/test.txt' into table test  fields terminated by '|' enclosed by '"' lines terminated by '\r\n' (id,name,@dummy) set create_time=CURRENT_TIMESTAMP;

3.2.6 對多個變數做處理

load data infile '/home/mysql/test.txt' into table test  fields terminated by '|' enclosed by '"' lines terminated by '\r\n' (@dummy1,@dummy2,create_time) set id = @dummy1*2 ,name=concat(@dummy2,'a');

3.2.7 使用查詢值作為列

load data infile '/home/mysql/test.txt' into table test  fields terminated by '|' enclosed by '"' lines terminated by '\r\n' (id,@dummy,create_time) set name= ( select 1 from dual);

3.2.8 使用查詢值作為列

關鍵字LOCAL匯入本地檔案。對檔案需要進行轉義

LOAD DATA LOCAL INFILE 'D:\\test.txt' INTO TABLE test CHARACTER SET UTF8 FIELDS TERMINATED BY '|' ENCLOSED BY '"' LINES TERMINATED BY '\r\n';