MySQL 原生支援 CSV 格式的批量數據交換,通過 LOAD DATA INFILE 導入、SELECT ... INTO OUTFILE 導出,性能遠超 INSERT 語句,適合千萬級數據遷移、異構系統對接、報表導出等場景。
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 皆支援
# 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}
secure_file_priv 限制)OPTIONALLY ENCLOSED BY 兼容兩者SHOW VARIABLES LIKE 'secure_file_priv';my.cnf 設 secure_file_priv="" 重啟SET NAMES utf8mb4;iconv / nkf 轉碼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)組合策略