MySQL 數據導出 Excel 完整指南：從 CSV 到函數計算實戰

MySQL 數據導出 Excel 完整指南：從 CSV 到函數計算實戰

將 MySQL 查詢結果導出 Excel 並進行後續分析是數據運營的高頻需求。本文梳理從導出、編碼處理到 Excel 進階函數計算的完整鏈路。

一、標準導出流程：CSV 中轉法

1. MySQL 客戶端導出 CSV（默認逗號分隔）。
2. 整數類型字段預加單引號：CONCAT('\'', field)，防止 Excel 自動科學計數法或截斷。
3. 文本編輯器打開 CSV，將單引號替換為空。
4. Excel → 數據 → 從文本/CSV → 選擇「文本」格式導入，確保長數字、身份證號不丟失精度。

二、常用 Excel 分析函數速查

三、進階導出方案：命令行與腳本

1. mysqldump 導出 SQL 再轉 Excel

mysqldump -u root -p db_name table_name > data.sql

再用工具（如 MySQL Workbench、Navicat）匯入並導出 Excel。

2. SELECT ... INTO OUTFILE 直接生成文件

SELECT * INTO OUTFILE '/tmp/data.xls' 
FIELDS TERMINATED BY '\t' OPTIONALLY ENCLOSED BY '' 
LINES TERMINATED BY '\n' FROM table_name;

注意：需 FILE 權限，輸出目錄需 MySQL 進程寫權限，且文件不能預存在。

3. PHP/Python 腳本直接輸出 Excel (推薦)

# PHP 示例：設置 Header 強制下載
header("Content-Type: application/vnd.ms-excel");
header("Content-Disposition: attachment; filename=export.xls");
header("Pragma: no-cache");
echo "姓名\t年齡\t時間\n";
while($row = $stmt->fetch()) {
    echo "{$row['name']}\t{$row['age']}\t{$row['time']}\n";
}

優點：靈活控制格式、編碼、樣式；可結合 PhpSpreadsheet 生成真正 .xlsx，支持公式、樣式、多工作表。

四、編碼坑點與解決

• MySQL 默認 utf8mb4，Excel 老版本偏好 GBK/GB2312。
• 導出後中文亂碼：iconv -f utf8 -t gb2312 -o out.xls in.xls；若報錯 illegal input sequence，先用文本編輯器另存為 ANSI 編碼。
• 最佳實踐：腳本導出時直接用 mb_convert_encoding 轉 GBK，或生成 UTF-8 BOM 的 CSV，Excel 可直接識別。

五、避坑清單

1. 長數字（身份證、銀行卡）必須以文本格式導入/導出。
2. 大數據量避免用 SELECT * INTO OUTFILE 產生巨大文件，分批導出或用流式處理。
3. 定時任務導出建議加壓縮（gzip/bzip2）節省空間。