萬盛學電腦網

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

MySQL CSV 匯出匯入完全指南:命令列、SQL 語法與 phpMyAdmin 三大方案對比

CSV 作為通用資料交換格式,在 MySQL 匯出匯入場景極為常見。本文對比原生 SQL、命令列工具、圖形介面三大方案,提供場景化選型建議。

方案一:原生 SQL 語法(SELECT ... INTO OUTFILE / LOAD DATA INFILE)

匯出 CSV

SELECT * FROM `test_info`
INTO OUTFILE '/tmp/test.csv'
FIELDS TERMINATED BY ',' 
OPTIONALLY ENCLOSED BY '"' 
ESCAPED BY '"'
LINES TERMINATED BY '\r\n';

匯入 CSV

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'; 查看允許路徑
  • 檔案必須在資料庫伺服器本機,不支援遠程路徑
  • 匯出檔案不可覆蓋既有檔案(安全機制)

方案二:mysqldump 命令列工具

# 匯出為 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  (建表結構)

優勢:支援並行、壓縮、遠程執行、結構與資料分離。

方案三:phpMyAdmin 圖形介面(最推薦非自動化場景)

  1. 選中目標資料表
  2. 點擊「匯出」標籤
  3. 格式選「CSV」或「CSV for MS Excel」
  4. 勾選「將欄位名稱放在第一行」
  5. 編碼選 UTF-8,點擊「執行」下載

匯入同理:選擇「匯入」標籤 → 選擇 CSV 檔 → 格式選 CSV → 執行。

方案對比決策矩陣

維度SQL INTO OUTFILEmysqldump --tabphpMyAdmin
自動化腳本✅ 極佳✅ 極佳❌ 人工操作
大數據量(>1GB)⚠️ 單執行緒慢✅ 支援並行❌ 瀏覽器超時
遠程資料庫❌ 需伺服器本機路徑✅ 遠程執行✅ Web介面
結構+資料同步❌ 僅資料✅ 同時產出✅ 可選
權限要求FILE 權限 + secure_file_privFILE 權限 + 目錄權限一般查詢權限即可
編碼處理依字元集參數依字元集參數自動處理 UTF-8/BOM

進階技巧:PHP 程式化匯出(大數據分批、記憶體控制)

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);
}

常見編碼問題解決

  • Excel 開啟 CSV 亂碼:匯出時加 BOM(fwrite($fp, "\xEF\xBB\xBF");)或使用 UTF-8 with BOM
  • 中文欄位名亂碼:確保連線字元集 SET NAMES utf8mb4
  • 換行符不一致:Windows \r\n、Linux \n、Mac 老版 \r,按目標環境設定

選型建議:自動化備份/ETL 用 mysqldump --tab;臨時分析/業務匯出用 phpMyAdmin;嵌入應用程式用 PHP 程式化匯出。避免在生產環境直接執行 INTO OUTFILE 佔用磁碟 IO。

copyright © 萬盛學電腦網 all rights reserved