萬盛學電腦網

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

MySQL 文本文件導入導出實戰:LOAD DATA INFILE 與 SELECT INTO OUTFILE 高效大量資料交換

當資料量達到百萬級,逐條 INSERT 效能不可接受。MySQL 內建 LOAD DATA INFILE (導入) 與 SELECT INTO OUTFILE (導出) 直接操作文本文件,繞過 SQL 解析層,速度比 INSERT 快 10~20 倍。本文完整示範建表、準備文本、導入導出參數、編碼處理、Windows/Linux 路徑差異與權限陷阱。

環境準備

CREATE TABLE person (
  id INT NOT NULL AUTO_INCREMENT,
  name VARCHAR(40) NOT NULL,
  age INT,
  city VARCHAR(20),
  salary INT,
  PRIMARY KEY (id)
) ENGINE=InnoDB DEFAULT CHARSET=gb2312;

文本文件格式規範 (data.txt)

張三	31	北京	3000
李四	25	杭州	4000
王五	45	N	4500
小明	29	天津	N
  • 欄位間用 Tab (\t) 分隔
  • NULL 值用 N 或 \N 表示
  • 無標題行,純資料
  • 編碼需與 CHARSET / SET NAMES 一致

導入:LOAD DATA INFILE

LOAD DATA LOCAL INFILE 'c:/data.txt'
INTO TABLE person (name, age, city, salary)
CHARACTER SET gb2312
FIELDS TERMINATED BY '\t'
LINES TERMINATED BY '\r\n'
IGNORE 0 LINES;

關鍵參數解析

導出:SELECT INTO OUTFILE

SELECT name, age, city, salary
INTO OUTFILE 'c:/data_out.txt'
CHARACTER SET gb2312
FIELDS TERMINATED BY '\t'
ENCLOSED BY '"'
LINES TERMINATED BY '\r\n'
FROM person;

權限與路徑陷阱

  • 需 FILE 權限:GRANT FILE ON *.* TO 'user'@'host';
  • 導出路徑必須是 MySQL 服務進程可寫入的伺服器端路徑 (非客戶端)
  • secure_file_priv 限制導入導出目錄:SHOW VARIABLES LIKE 'secure_file_priv';
  • Windows 路徑用正斜線 C:/data/out.txt 或雙反斜線 C:\\data\\out.txt

標準備份/遷移:mysqldump + iconv 編碼轉換

# 1. 導出 (指定字符集避免亂碼)
mysqldump --default-character-set=latin1 -uroot -p --database db > /tmp/old.sql

# 2. 編碼轉換 (latin1 → utf8)
iconv -f gbk -t utf-8 -c /tmp/old.sql > /tmp/new.sql

# 3. 修改 new.sql 開頭加入
SET NAMES utf8;

# 4. 導入新庫
mysql -uroot -p new_db < /tmp/new.sql

效能對比 (百萬筆測試)

參數說明常用值
LOCAL從客戶端讀取文件 (無需 FILE 權限)建議加上
CHARACTER SET指定文件編碼gb2312, utf8mb4
FIELDS TERMINATED BY欄位分隔符'\t', ',', '|'
ENCLOSED BY欄位包圍符 (可選)'"'"
LINES TERMINATED BY行結束符Windows '\r\n', Linux '\n'
IGNORE n LINES跳過前 n 行 (如標題)1
SET col = expr導入時轉換/計算欄位SET created_at = NOW()
文本導入導出是 DBA 與數據工程師的基本功。掌握 LOAD DATA 參數、編碼對齊、secure_file_priv 權限三大關鍵,百萬級資料交換輕鬆搞定。
copyright © 萬盛學電腦網 all rights reserved
方式耗時適用場景
逐條 INSERT~45 分鐘極小資料、應用層邏輯複雜
批次 INSERT (1000 行/批)~3 分鐘中等資料、應用層可控
LOAD DATA INFILE~15 秒大量資料導入、ETL、初始化
mysqldump + 導入~2 分鐘全庫遷移、跨版本升級