CSV 作為通用資料交換格式,在 MySQL 匯出匯入場景極為常見。本文對比原生 SQL、命令列工具、圖形介面三大方案,提供場景化選型建議。
SELECT * FROM `test_info`
INTO OUTFILE '/tmp/test.csv'
FIELDS TERMINATED BY ','
OPTIONALLY ENCLOSED BY '"'
ESCAPED BY '"'
LINES TERMINATED BY '\r\n';
LOAD DATA INFILE '/tmp/test.csv'
INTO TABLE `test_info`
FIELDS TERMINATED BY ','
OPTIONALLY ENCLOSED BY '"'
ESCAPED BY '"'
LINES TERMINATED BY '\r\n';
| 參數 | 預設值 | 說明 | 建議設定 |
|---|---|---|---|
| FIELDS TERMINATED BY | \t | 欄位分隔符 | , (CSV標準) |
| OPTIONALLY ENCLOSED BY | 無 | 欄位包圍字元(含分隔符時自動加上) | " |
| ESCAPED BY | \ | 跳脫字元 | " 或 \ |
| LINES TERMINATED BY | \n | 行結束符 | \r\n (Windows相容) |
FILE 權限:執行帳號需具備 GRANT FILE ON *.* TO 'user'@'host';secure_file_priv:MySQL 5.7+ 限制檔案讀寫目錄,SHOW VARIABLES LIKE 'secure_file_priv'; 查看允許路徑# 匯出為 CSV 格式(需配合 --tab 參數)
mysqldump -u root -p --tab=/tmp/export_dir --fields-terminated-by=',' --fields-enclosed-by='"' --lines-terminated-by='\r\n' db_name table_name
# 產生兩個檔案:
# /tmp/export_dir/table_name.txt (資料)
# /tmp/export_dir/table_name.sql (建表結構)
優勢:支援並行、壓縮、遠程執行、結構與資料分離。
匯入同理:選擇「匯入」標籤 → 選擇 CSV 檔 → 格式選 CSV → 執行。
| 維度 | SQL INTO OUTFILE | mysqldump --tab | phpMyAdmin |
|---|---|---|---|
| 自動化腳本 | ✅ 極佳 | ✅ 極佳 | ❌ 人工操作 |
| 大數據量(>1GB) | ⚠️ 單執行緒慢 | ✅ 支援並行 | ❌ 瀏覽器超時 |
| 遠程資料庫 | ❌ 需伺服器本機路徑 | ✅ 遠程執行 | ✅ Web介面 |
| 結構+資料同步 | ❌ 僅資料 | ✅ 同時產出 | ✅ 可選 |
| 權限要求 | FILE 權限 + secure_file_priv | FILE 權限 + 目錄權限 | 一般查詢權限即可 |
| 編碼處理 | 依字元集參數 | 依字元集參數 | 自動處理 UTF-8/BOM |
function exportCsv(PDO $pdo, string $table, string $filePath, int $batchSize = 10000): void {
$fp = fopen($filePath, 'w');
// 寫入標題行
$stmt = $pdo->query("DESCRIBE `$table`");
$headers = array_column($stmt->fetchAll(PDO::FETCH_ASSOC), 'Field');
fputcsv($fp, $headers);
$offset = 0;
do {
$stmt = $pdo->prepare("SELECT * FROM `$table` LIMIT ?, ?");
$stmt->bindValue(1, $offset, PDO::PARAM_INT);
$stmt->bindValue(2, $batchSize, PDO::PARAM_INT);
$stmt->execute();
$rows = $stmt->fetchAll(PDO::FETCH_NUM);
foreach ($rows as $row) {
fputcsv($fp, $row);
}
$offset += $batchSize;
} while (count($rows) === $batchSize);
fclose($fp);
}
fwrite($fp, "\xEF\xBB\xBF");)或使用 UTF-8 with BOMSET NAMES utf8mb4\r\n、Linux \n、Mac 老版 \r,按目標環境設定選型建議:自動化備份/ETL 用 mysqldump --tab;臨時分析/業務匯出用 phpMyAdmin;嵌入應用程式用 PHP 程式化匯出。避免在生產環境直接執行 INTO OUTFILE 佔用磁碟 IO。