萬盛學電腦網

您现在的位置: 萬盛學電腦網 >> 程式設計 >  >> 資料庫 >> MySQL 教學

MySQL 數據導入導出完全指南:SELECT INTO OUTFILE、mysqldump、LOAD DATA、mysqlimport

導出方法對比

方法適用場景優點缺點
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
copyright © 萬盛學電腦網 all rights reserved