導出方法對比
| 方法 | 適用場景 | 優點 | 缺點 |
| SELECT INTO OUTFILE | 程式化導出、單表、需 FILE 權限 | 靈活控制格式、無需外部工具 | 只能導出數據、需服務器端文件權限 |
| mysqldump | 備份、遷移、多表、結構+數據 | 功能全、支援壓縮、跨版本、遠程導入 | 大表較慢、需足夠磁碟空間 |
| mysqlpump (5.7+) | 大庫並行備份 | 並行導出、壓縮、更快 | 不支援 --single-transaction |
SELECT INTO OUTFILE 詳解
USE phptest;
SELECT * FROM driver
INTO OUTFILE 'a.txt'
FIELDS TERMINATED BY ',' ENCLOSED BY '"' ESCAPED BY '\\'
LINES TERMINATED BY '\n';
- 文件生成在服務器
datadir/db_name/ 下
- 需
FILE 權限:GRANT FILE ON *.* TO 'user'@'host';
- 文件不能預存(安全限制),需刪除後重跑
- 路徑技巧:
'./a.txt' (datadir)、'/a.txt' (根目錄、需權限)
mysqldump 實戰(參考文章 177797)
# 完整備份(結構+數據)
mysqldump --opt phptest > a.sql
# 只結構
mysqldump -d phptest > schema.sql
# 只數據
mysqldump -t phptest > data.sql
# 指定格式導出純文本(每表兩文件)
mysqldump -T ./ --fields-terminated-by=, --fields-enclosed-by=\" phptest driver
導入方法
LOAD DATA INFILE(高速批量載入)
LOAD DATA INFILE 'driver.txt'
INTO TABLE driver
FIELDS TERMINATED BY ',' ENCLOSED BY '"' ESCAPED BY '\\'
LINES TERMINATED BY '\n'
IGNORE 1 LINES; -- 跳過標題行
關鍵參數:
LOCAL:從客戶端讀取文件(需客戶端與服務端都啟用 local_infile=1)
REPLACE / IGNORE:主鍵/唯一鍵衝突處理
SET col = expr:導入時轉換(如 SET created_at = NOW())
mysqlimport(LOAD DATA 的命令列封裝)
mysqlimport --fields-terminated-by=, --fields-enclosed-by=\" \
--local -u root -p phptest driver.txt
- 表名 = 文件名(去副檔名)
--local 讀取本地文件
編碼與格式陷阱
| 問題 | 解決 |
| 中文亂碼 | 導出導入指定 --default-character-set=utf8mb4,表/庫同字符集 |
| 換行符不一致 | 統一 LINES TERMINATED BY '\n',前端 \r\n 預處理 |
| NULL 值 | FIELDS ESCAPED BY '\\\\',NULL 輸出為 \N |
| 自增 ID 衝突 | 導入前 SET @max_id = (SELECT MAX(id) FROM t); ALTER TABLE t AUTO_INCREMENT = @max_id + 1; |
大數據量優化
- 導出:
--quick --single-transaction(InnoDB)
- 導入:
SET autocommit=0; SET unique_checks=0; SET foreign_key_checks=0; 導入後恢復
- 分檔並行:
pt-parallel-dump / mydumper / mysqlpump
- 禁用二進制日誌:
SET sql_log_bin=0;(需 SUPER 權限)
推薦工具鏈
- 日常備份:
mysqldump --single-transaction + gzip + 異地存儲
- 大表遷移:
mydumper/myloader 並行導出導入
- 異構同步:
pt-archiver、Canal (Binlog 訂閱)
- 圖形化:MySQL Workbench、DataGrip、Navicat