萬盛學電腦網

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

MySQL CSV 數據導入導出實戰:LOAD DATA 與 SELECT INTO OUTFILE

MySQL 批量數據遷移利器:CSV 格式高效導入導出指南

MySQL 原生支援 CSV 格式的批量數據交換,通過 LOAD DATA INFILE 導入、SELECT ... INTO OUTFILE 導出,性能遠超 INSERT 語句,適合千萬級數據遷移、異構系統對接、報表導出等場景。

一、CSV 導入:LOAD DATA INFILE


SET NAMES utf8mb4;
LOAD DATA LOCAL INFILE 'C:\\resource.csv'
INTO TABLE resource
FIELDS TERMINATED BY '\t'
OPTIONALLY ENCLOSED BY '"'
LINES TERMINATED BY '\r\n'
(title, singer);

關鍵參數解析

子句說明常見值
FIELDS TERMINATED BY字段分隔符','\t','|'
OPTIONALLY ENCLOSED BY字段包圍符(可選)'"'"
LINES TERMINATED BY行結束符'\n','\r\n'
IGNORE n LINES跳過前 n 行(如標題行)IGNORE 1 LINES
SET col = expr導入時計算衍生列SET created_at=NOW()

權限與安全

  • 需 FILE 權限:GRANT FILE ON *.* TO 'user'@'host';
  • secure_file_priv 限制讀寫目錄(MySQL 5.7+ 預設開啟),需將文件放置於指定目錄或設為空
  • LOCAL 關鍵字:客戶端讀取文件並發送給 Server,繞過 secure_file_priv,需客戶端與 Server 皆支援

二、CSV 導出:SELECT INTO OUTFILE


# Shell 腳本片段
date=$(date -d "$date" "+%Y%m%d")
fileName="data${date}.csv"

/usr/local/mysql/bin/mysql -h "$mysql_host" -u "$mysql_user" -p"$mysql_pass" -e "
  SELECT * INTO OUTFILE '/tmp/${fileName}'
  FIELDS TERMINATED BY ',' OPTIONALLY ENCLOSED BY '\"'
  LINES TERMINATED BY '\n'
  FROM $table
  WHERE InTime >= $minTime AND InTime < $maxTime;
"

# 編碼轉換(UTF-8 → GBK,兼容 Excel 打開)
iconv -c -f UTF-8 -t GBK /tmp/${fileName} > /data/export/${fileName}

注意事項

  • 導出目錄需 MySQL 進程寫入權限(受 secure_file_priv 限制)
  • 文件不可覆蓋,需先刪除或更換文件名
  • 大表導出建議分批次(按主鍵範圍/時間範圍)避免單文件過大
  • 數值型字段導出不帶引號,字符型帶引號,OPTIONALLY ENCLOSED BY 兼容兩者

三、常見問題與解決

  1. ERROR 1290 (HY000): The MySQL server is running with the --secure-file-priv option
    • 查看限制目錄:SHOW VARIABLES LIKE 'secure_file_priv';
    • 將文件放入該目錄,或修改 my.cnf 設 secure_file_priv="" 重啟
  2. 中文亂碼
    • 導入前 SET NAMES utf8mb4;
    • 導出後用 iconv / nkf 轉碼
  3. 日期/時間格式不匹配
    • 導入時用 SET col = STR_TO_DATE(@var, '%Y-%m-%d %H:%i:%s')
    • 導出時用 DATE_FORMAT(col, '%Y-%m-%d')

四、性能對比參考

  • LOAD DATA:~10-50 萬行/秒(SSD、批量提交、關閉索引/外鍵檢查)
  • INSERT:~1-5 萬行/秒(單條提交)
  • 大數據遷移首選 LOAD DATA + 物理備份(xtrabackup)組合策略
copyright © 萬盛學電腦網 all rights reserved