萬盛學電腦網

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

MySQL 匯出 Excel/CSV 中文編碼轉換實戰:CONVERT 函數與 mysqldump 選擇指南

場景

MySQL 資料庫編碼 `utf8mb4`,需匯出 Excel/CSV 給 Windows Excel 開啟 (預設編碼 GBK/CP936),直接匯出中文會亂碼。

方案一:SQL 查詢導出檔案 + CONVERT 編碼轉換 (適用中小量資料)


-- 進入 mysql 客戶端
USE target_db;
-- 設定輸出檔案路徑 (伺服器端路徑,需 FILE 權限與 secure_file_priv 允許)
SELECT 
    id,
    CONVERT(title USING gbk) AS title_gbk,
    CONVERT(content USING gbk) AS content_gbk,
    create_time
FROM articles
WHERE status = 1
INTO OUTFILE '/var/lib/mysql-files/articles_export.csv'
FIELDS TERMINATED BY ',' 
ENCLOSED BY '"' 
LINES TERMINATED BY '\r\n'
CHARACTER SET gbk;  -- 指定輸出檔案編碼為 GBK

關鍵點: `CHARACTER SET gbk` 在 `INTO OUTFILE` 子句中指定**輸出檔案編碼**;`CONVERT(col USING gbk)` 將欄位資料從儲存編碼 (utf8mb4) 轉為 GBK 位元組。兩者配合,Excel 直接開啟無亂碼。

方案二:mysqldump 導出 INSERT 語句 + 圖形工具匯入 Excel

  1. 導出 SQL:`mysqldump -u root -p --default-character-set=utf8mb4 --no-create-info --extended-insert=FALSE db tbl > data.sql`
  2. 使用 MySQL Workbench / Navicat / DBeaver 開啟 SQL 檔,執行查詢結果 → 匯出為 CSV/Excel (工具內建編碼轉換)。

方案三:命令列直出 CSV (無編碼轉換,後續處理)


# 導出 UTF-8 CSV (Excel 2016+ 可直接開啟 UTF-8 CSV,舊版需資料 → 從文字/CSV 匯入指定 UTF-8)
mysql -u root -p --default-character-set=utf8mb4 -e "
  SELECT 'ID','Title','Content','CreateTime'
  UNION ALL
  SELECT id, title, content, create_time FROM articles WHERE status=1
" db_name | sed 's/\t/,/g' > articles_utf8.csv
# 注意: mysql 批次模式輸出為 TAB 分隔,sed 替換為逗號,簡易處理未處理欄位內逗號/換行

方案四:Python/Pandas 程式化匯出 (最靈活、推薦生產)


import pandas as pd
import pymysql

conn = pymysql.connect(host='localhost', user='root', passwd='pwd', db='db', charset='utf8mb4')
df = pd.read_sql("SELECT id, title, content, create_time FROM articles WHERE status=1", conn)
# 匯出 Excel (支援超大欄位、多工作表、樣式)
df.to_excel('articles.xlsx', index=False, engine='openpyxl')
# 匯出 GBK CSV (給舊版 Excel)
df.to_csv('articles_gbk.csv', index=False, encoding='gbk', errors='ignore')
conn.close()

工具版本不匹配問題 (Workbench 匯入/匯出報錯)

現象:`mysqldump version mismatch` / `Incompatible version`。
**解決**:指定正確 `mysqldump` 路徑:
`/usr/local/mysql/bin/mysqldump` (Linux) 或 `C:\Program Files\MySQL\MySQL Server 8.0\bin\mysqldump.exe` (Windows)。
Workbench 偏好設定 → `Administration` → `Path to mysqldump Tool` 設定正確路徑。

權限問題排查

  • `ERROR 1290 (HY000): The MySQL server is running with the --secure-file-priv option` → 檔案只能寫入/讀取 `secure_file_priv` 指定目錄。
    解決:`SHOW VARIABLES LIKE 'secure_file_priv';` 查看目錄,將 `INTO OUTFILE` 路徑設在該目錄下,或修改 `my.cnf` `secure_file_priv=''` 重啟 (不建議生產)。
  • Windows 權限不足 → 以管理員執行 CMD / PowerShell。

總結選型建議

需求推薦方案
一次性、小量、DBA 手動操作方案一 `INTO OUTFILE + CONVERT + CHARACTER SET`
定期自動化、大數據、需格式化方案四 Python/Pandas 排程
結構+資料完整遷移方案二 mysqldump + 圖形工具
純資料同步異構系統方案三 CSV + ETL 工具
copyright © 萬盛學電腦網 all rights reserved