SELECT col1, col2, ...
INTO OUTFILE '/path/to/export.csv'
[CHARACTER SET charset_name]
[FIELDS TERMINATED BY '\t'
[OPTIONALLY] ENCLOSED BY '"'
ESCAPED BY '\\']
[LINES TERMINATED BY '\n']
FROM table_name
WHERE condition
ORDER BY col
LIMIT offset, row_count;
SELECT *
INTO OUTFILE '/tmp/export_$(date +%F).csv'
CHARACTER SET utf8mb4
FIELDS TERMINATED BY ',' ENCLOSED BY '"' ESCAPED BY '\\'
LINES TERMINATED BY '\r\n'
FROM tb1
WHERE 1
ORDER BY id DESC
LIMIT 0, 50;
| 子句 | 作用 | Excel 兼容推薦值 |
|---|---|---|
| FIELDS TERMINATED BY | 字段分隔符 | ',' (CSV) 或 '\t' (TSV) |
| ENCLOSED BY | 字段包圍符 | '"'" 雙引號 |
| ESCAPED BY | 轉義字符 | '\\' 反斜杠 |
| LINES TERMINATED BY | 行結束符 | '\r\n' Windows 換行 |
| CHARACTER SET | 輸出編碼 | utf8mb4 或 gbk (Excel 國內版兼容) |
FILE 權限:GRANT FILE ON *.* TO 'user'@'host';secure_file_priv 目錄下(SHOW VARIABLES LIKE 'secure_file_priv';)chown 或 sudo 讀取utf-8 或 gb2312,"\n 或 \r\n

| 維度 | INTO OUTFILE | phpMyAdmin |
|---|---|---|
| 數據量 | 百萬級+(流式寫入) | 萬級以內(內存受限) |
| 自動化 | ✅ 腳本/定時任務 | ❌ 人工點擊 | 服務端磁盤 | 瀏覽器下載到本地 |
| 權限要求 | FILE 權限 + secure_file_priv | 僅需 SELECT 權限 |
| 格式靈活度 | 完全可控 | 預設模板有限 |
-- 方法:UNION ALL 拼接表頭行
(SELECT 'id','name','email')
UNION ALL
(SELECT id, name, email FROM users INTO OUTFILE '/tmp/users.csv'
FIELDS TERMINATED BY ',' ENCLOSED BY '"' LINES TERMINATED BY '\r\n');
# SSH 直接執行並壓縮傳輸
ssh user@db-server "mysql -e \"SELECT * INTO OUTFILE '/tmp/exp.csv' FIELDS TERMINATED BY ',' FROM db.tbl;\" && gzip -c /tmp/exp.csv" > exp.csv.gz
# 腳本分批次導出
for i in {0..9}; do
mysql -e "SELECT * INTO OUTFILE '/tmp/part_$i.csv' FROM big_tbl WHERE id % 10 = $i ..."
done
自動化/大數據/服務端存儲選 INTO OUTFILE,**臨時/小數據/本地下載**選 phpMyAdmin。關鍵在於**字符集、分隔符、換行符、包圍符**四項參數與 Excel 打開習慣匹配。生產環境建議封裝為標準化導出腳本,納入數據治理體系。