MySQL 導入 CSV 數據實戰：Shell 迴圈讀取、字符集三碼合一、REPLACE INTO 幂等寫入

當 LOAD DATA INFILE 不可用（權限受限、版本限制、遠程導入）時，通用 Shell + MySQL 客戶端腳本可實現 CSV 穩健導入，關鍵在於字符集一致性與幂等寫入。

場景與前置條件

• 源文件：employee.csv（UTF-8 編碼，列：ds, id, name）
• 目標表：wechat_employee(ds VARCHAR(20), femployeeid INT, femployeename VARCHAR(256)) ENGINE=InnoDB DEFAULT CHARSET=utf8
• MySQL 版本：5.5+（不支援 LOAD DATA LOCAL INFILE 或受 secure_file_priv 限制）

一、字符集三碼合一驗證（最關鍵步驟）

-- 1. 服務端字符集
SHOW VARIABLES LIKE 'character_set_%';
-- 期望：client/connection/database/results/server 均為 utf8/utf8mb4

-- 2. 表字符集
SHOW CREATE TABLE wechat_employee;
-- 期望：DEFAULT CHARSET=utf8 (或 utf8mb4)

-- 3. 客戶端連接字符集（導入前設置）
SET NAMES utf8;  -- 或 utf8mb4
-- 等價於：
SET character_set_client = utf8;
SET character_set_connection = utf8;
SET character_set_results = utf8;

原則：文件編碼 = 客戶端連接編碼 = 表存儲編碼，任一不一致導致亂碼。

二、Shell 導入腳本（幂等 REPLACE INTO）

#!/bin/bash
# import_employee.sh
# 用法：chmod +x import_employee.sh && ./import_employee.sh

CSV_FILE="./employee.csv"
DB_USER="root"
DB_PASS="655453"
DB_NAME="test"
TABLE="wechat_employee"

# 逐行讀取 CSV（假設無標題行，無引號包裹，純逗號分隔）
while IFS=',' read -r ds id name; do
  # 跳過空行
  [[ -z "$ds" ]] && continue
  
  # 顯式指定字符集執行 REPLACE INTO（主鍵/唯一鍵衝突時覆蓋）
  mysql -u"$DB_USER" -p"$DB_PASS" "$DB_NAME" \
    --default-character-set=utf8 \
    -e "REPLACE INTO \`$TABLE\` (\`ds\`, \`femployeeid\`, \`femployeename\`) VALUES ('$ds', '$id', '$name');"
    
  # 進度提示
  echo "Imported: ds=$ds, id=$id, name=$name"
done < "$CSV_FILE"

echo "Import completed."

三、關鍵技術點解析

四、進階版：Python 腳本（健壯性更強）

#!/usr/bin/env python3
import csv, mysql.connector, sys

def import_csv(csv_path, db_config, table):
    with open(csv_path, 'r', encoding='utf-8') as f:
        reader = csv.DictReader(f)  # 假設有標題行
        conn = mysql.connector.connect(**db_config)
        cursor = conn.cursor()
        sql = f"REPLACE INTO `{table}` (`ds`, `femployeeid`, `femployeename`) VALUES (%s, %s, %s)"
        batch = []
        for row in reader:
            batch.append((row['ds'], int(row['id']), row['name']))
            if len(batch) >= 1000:
                cursor.executemany(sql, batch)
                conn.commit()
                batch.clear()
        if batch:
            cursor.executemany(sql, batch)
            conn.commit()
        cursor.close()
        conn.close()

if __name__ == '__main__':
    config = {
        'host': 'localhost', 'user': 'root', 'password': '655453',
        'database': 'test', 'charset': 'utf8', 'use_unicode': True
    }
    import_csv('./employee.csv', config, 'wechat_employee')

五、常見問題排查

1. 亂碼：檢查文件 file -i employee.csv 編碼、MySQL 連接字符集、表字符集三者一致。
2. 主鍵衝突報錯：改用 INSERT IGNORE（跳過）或 ON DUPLICATE KEY UPDATE（部分更新）。
3. 速度慢：批次提交、關閉 autocommit/unique_checks/foreign_key_checks、使用 LOAD DATA（若可用）。
4. CSV 含換行/引號：Shell read 無法正確解析，必須用 Python csv 模組或專業 ETL 工具。