MySQL 資料匯出匯入實戰 (二)：SELECT INTO OUTFILE 與 mysqldump 進階用法

方法一：SELECT ... INTO OUTFILE (伺服器端匯出)

USE phptest;
SELECT * FROM driver INTO OUTFILE 'a.txt';
-- 預設輸出至 datadir/phptest/a.txt
-- 欄位分隔 \t、行分隔 \n、無引用字元

自訂格式參數

SELECT * FROM driver 
INTO OUTFILE '/tmp/driver.csv'
FIELDS TERMINATED BY ',' 
       ENCLOSED BY '"' 
       ESCAPED BY '\\'
LINES TERMINATED BY '\r\n';

• TERMINATED BY：欄位/行分隔符
• ENCLOSED BY：引用字元 (加 OPTIONALLY 僅 CHAR/VARCHAR 加引號)
• ESCAPED BY：逸出字元
• 需 FILE 權限，檔案不可存在

方法二：mysqldump (邏輯備份、跨版本遷移首選)

# 完整備份
mysqldump -u root -p --all-databases --single-transaction --master-data=2 > full.sql

# 單庫備份 (含建表、資料)
mysqldump -u root -p dbname --add-drop-table --extended-insert > db.sql

# 僅結構
mysqldump -u root -p dbname --no-data > schema.sql

# 僅資料
mysqldump -u root -p dbname --no-create-info > data.sql

匯入方式

# 邏輯備份還原
mysql -u root -p dbname < dump.sql

# LOAD DATA INFILE (高速大量載入)
LOAD DATA INFILE '/tmp/driver.csv'
INTO TABLE driver
FIELDS TERMINATED BY ',' ENCLOSED BY '"'
LINES TERMINATED BY '\r\n'
IGNORE 1 LINES;  -- 跳過標題列

權限與安全

• SELECT INTO OUTFILE：需 FILE 權限，檔案寫在 Server 端
• LOAD DATA INFILE：需 FILE 權限，預設讀 Server 端；LOAD DATA LOCAL INFILE 讀 Client 端 (需啟用 local-infile=1)
• 生產環境建議關閉 FILE 權限，改用 mysqldump + 應用層下載