當資料量達到百萬級,逐條 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;
關鍵參數解析
| 參數 | 說明 | 常用值 |
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() |
導出: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
效能對比 (百萬筆測試)
| 方式 | 耗時 | 適用場景 |
| 逐條 INSERT | ~45 分鐘 | 極小資料、應用層邏輯複雜 |
| 批次 INSERT (1000 行/批) | ~3 分鐘 | 中等資料、應用層可控 |
| LOAD DATA INFILE | ~15 秒 | 大量資料導入、ETL、初始化 |
| mysqldump + 導入 | ~2 分鐘 | 全庫遷移、跨版本升級 |
文本導入導出是 DBA 與數據工程師的基本功。掌握 LOAD DATA 參數、編碼對齊、secure_file_priv 權限三大關鍵,百萬級資料交換輕鬆搞定。
copyright ©
萬盛學電腦網 all rights reserved