MySQL 表導出 Excel 實戰：INTO OUTFILE 與工具方案對比

MySQL 表導出 Excel 實戰：INTO OUTFILE 與工具方案對比

MySQL 原生 SELECT ... INTO OUTFILE 是導出數據最直接、性能最高的方式，但生成的是純文本（TSV/CSV），非真正的 .xls/.xlsx。本文實測導出流程、編碼陷阱、以及輔助工具方案對比。

一、原生導出語法

USE test;
SELECT * FROM i_student INTO OUTFILE 'd:/1.xls';

二、生成文件特性與打開提示

• 文件實為 Tab 分隔文本（TSV），無表頭，無格式，僅裸數據。
• Excel 打開會彈出「文件格式與擴展名不匹配」警告：

點「是」後正常顯示數據：

三、核心限制與權限要求

1. 權限：需 FILE 權限（GRANT FILE ON *.* TO user@host;）。
2. 路徑：文件寫在 MySQL 服務端主機 磁盤，非客戶端。secure_file_priv 限制目錄（SHOW VARIABLES LIKE 'secure_file_priv';）。
3. 不覆蓋：目標文件存在會報錯，需先刪除或改名。
4. 無表頭：需應用層自行添加或用 UNION SELECT 'col1','col2' ... 技巧。
5. 編碼：按 character_set_filesystem 寫入，建議服務端/連接均為 UTF-8。

四、進階導出技巧

1. 自定義分隔符與換行符（生成標準 CSV）

SELECT * FROM i_student 
INTO OUTFILE '/tmp/student.csv'
FIELDS TERMINATED BY ',' ENCLOSED BY '"' ESCAPED BY '\\'
LINES TERMINATED BY '\r\n';

2. 帶表頭導出（UNION 技巧）

(SELECT 'id', 'name', 'age')
UNION ALL
(SELECT id, name, age FROM i_student)
INTO OUTFILE '/tmp/student_with_header.csv'
FIELDS TERMINATED BY ',' ENCLOSED BY '"';

3. 任意查詢結果導出

SELECT s.name, c.class_name 
FROM i_student s 
JOIN i_class_student cs ON s.id=cs.student_id
JOIN i_class c ON cs.class_id=c.id
WHERE c.id=1
INTO OUTFILE '/tmp/class1_students.csv'
FIELDS TERMINATED BY ',';

五、工具方案對比（推薦生產環境）

六、最佳實踐建議

1. 大數據導出（>100萬行）：服務端 INTO OUTFILE 生成 CSV → 壓縮 → 下載/傳輸 → 下游系統加載。
2. 業務報表（<5萬行）：DBeaver/Navicat 直接導出 .xlsx，含表頭、樣式、凍結窗格。
3. 自動化系統：Python pandas.read_sql + to_excel（分塊寫入）或 openpyxl 流式模式。
4. 安全合規：導出文件加密存儲、傳輸加密、訪問審計、定期清理。

總結：INTO OUTFILE 是「數據搬運工」，工具導出是「報表生成器」。按場景選對工具，事半功倍。