簡單介紹 MySQL 的 SELECT INTO OUTFILE 和 LOAD DATA LOCAL INFILE ...
今天遇到一個需求,要用自動任務將一張表的數據導入另一張表。具體場景及限制:將資料庫A中表A的數據導入到資料庫B的表B中(增量數據或全量數據兩種方式);體系1和體系2只能分別訪問資料庫A和資料庫B。附圖:
實現方式:體系1將表A的數據導出成文件,存入MongoDB,成功後通知體系2,體系2進行數據導入。
重點來啦:在數據導出和數據導入的時候,有幾下幾種方式,此處具體介紹方法三
方法一:mysqldump,但是mysqldump是在操作系統命令行下運行的,並不滿足這種場景。
方法二:通過sql進行查詢,再通過io寫成文件。此方式不做評價...ps:各種工具的導入導出就算了,有興趣的自行瞭解
方法三:導出==》SELECT語句 INTO OUTFILE 路徑+目標文件 [option]
導入==》LOAD DATA LOCAL INFILE 路徑+目標文件 INTO TABLE 表名 [option]
其中option參數常用的5個選項:
FIELDS TERMINATED BY '字元串':設置字元串為欄位的分割符,預設值為 \t;
FIELDS ENCLOSED BY '字元':設置字元串括上欄位的值,預設值為 無任何符號;
FIELDS OPTIONALLY ENCLOSED BY '字元':設置字元串括上char varchar text等字元型欄位,預設值為 無任何符號;
LINES STARTING BY '字元串':設置每一行開頭的字元,預設值為 無任何字元;
FIELDS ESCAPED BY '字元':設置轉義字元,預設值為 \;
LINES TERMINATED BY '字元串':設置每行結束符,預設值為 \n;
註意:1.導入的時候數據文件要和導入的表的結構相對應(欄位長度、類型、列等等)
2.導出的文件不是sql,不包含表結構,只是純數據文件,且每一條數據占一行
3.路徑問題:有時候隨便選擇一個路徑並不能導出數據文件,會報錯
如:The MySQL server is running with the --secure-file-priv option so it cannot execute this statement
解決方式:
a)先通過SHOW VARIABLES LIKE '%secure%' 查詢出預設的路徑,使用預設路徑進行導出,執行結果如下圖
b)修改 mysql 的 my.ini 配置文件中secure_file_priv來進行管理,
i.不允許MySQL進行導入導出
# Secure File Priv.
secure-file-priv = null
ii.只允許MySQL在指定的目錄下進行導入導出操作
# Secure File Priv.
secure-file-priv = /目錄/
iii.不對MySQL的導入導出進行限制(註釋或刪除secure-file-priv配置)
# Secure File Priv.
#secure-file-priv="C:/ProgramData/MySQL/MySQL Server 5.7/Uploads"
最後附上eg:
SELECT * FROM pub_parameter INTO OUTFILE 'C:\\ProgramData\\MySQL\\MySQL Server 5.7\\Uploads\\test8.sql'SELECT * FROM pub_parameter INTO OUTFILE 'C:\\ProgramData\\MySQL\\MySQL Server 5.7\\Uploads\\test5.sql' FIELDS TERMINATED BY '|'