如何将MySQL表数据导出为CSV并转换字符编码？附多方案尝试求助

Hey there! Let's tackle your problem of exporting daily transaction data from that 200-column MySQL table for ETL. You've already dabbled with Python approaches, so I'll refine those plus share MySQL-specific shell script solutions (since you couldn't find alternatives to Oracle's sqlplus).

1. Optimized Python Approaches #

Your existing Python methods can be tweaked to handle large 200-column datasets more efficiently, especially if daily transaction volumes are high.

1.1 Pandas Method (Memory-Optimized) #

Here's a complete, refined version of your partial code that avoids loading the entire table into memory:

import MySQLdb as mdb
import pandas as pd

# Database credentials
host = "your_db_host"
user = "your_username"
pass_ = "your_password"
db = "your_database"

# Query for daily transactions (adjust date filter to match your schema)
query = """
SELECT * 
FROM your_transaction_table 
WHERE transaction_date = CURDATE()
"""

# Configure export settings
chunk_size = 10000  # Process data in 10k-row batches to save RAM
csv_output_path = "daily_transactions.csv"

with mdb.connect(host=host, user=user, passwd=pass_, db=db) as conn:
    # Iterate over chunks instead of loading full dataset
    for chunk in pd.read_sql(query, conn, chunksize=chunk_size):
        # Append to CSV (write header only on first chunk)
        chunk.to_csv(
            csv_output_path,
            mode='a',
            header=not pd.io.common.path_exists(csv_output_path),
            index=False,
            sep='|'  # Use a separator that won't conflict with text fields (ETL-friendly)
        )

Key optimizations:

• chunksize: Prevents RAM overload with large 200-column datasets
• mode='a': Safely appends batches without overwriting progress
• Custom separator (|): Avoids issues with commas in transaction descriptions or notes

1.2 Raw Python (No Pandas, Lower Overhead) #

If you want a lighter-weight alternative without Pandas' dependencies:

import MySQLdb as mdb
import csv

host = "your_db_host"
user = "your_username"
pass_ = "your_password"
db = "your_database"
query = """
SELECT * 
FROM your_transaction_table 
WHERE transaction_date = CURDATE()
"""

csv_output_path = "daily_transactions_raw.csv"

with mdb.connect(host=host, user=user, passwd=pass_, db=db) as conn:
    cursor = conn.cursor(mdb.cursors.DictCursor)  # Returns rows as dictionaries for easy header mapping
    cursor.execute(query)
    
    # Extract column names for CSV header
    column_names = [desc[0] for desc in cursor.description]
    
    with open(csv_output_path, 'w', newline='', encoding='utf-8') as csvfile:
        writer = csv.DictWriter(csvfile, fieldnames=column_names, delimiter='|')
        writer.writeheader()
        
        # Fetch rows in batches to conserve memory
        while True:
            rows = cursor.fetchmany(10000)
            if not rows:
                break
            writer.writerows(rows)

This approach uses less RAM than Pandas since it skips DataFrame creation—ideal for resource-constrained servers.

2. MySQL-Specific Shell Script Solutions (What You Were Missing!) #

Forget Oracle's sqlplus—here are two robust shell scripts tailored for MySQL, perfect for daily cron jobs:

2.1 Lightweight mysql Command Line Export #

This is the simplest option for pure CSV ETL exports:

#!/bin/bash

# Database configuration
DB_HOST="your_db_host"
DB_USER="your_username"
OUTPUT_FILE="/path/to/daily_transactions_$(date +%Y%m%d).csv"

# Query for daily transactions
QUERY="SELECT * FROM your_transaction_table WHERE transaction_date = CURDATE();"

# Export to CSV with clean formatting
mysql -h $DB_HOST -u $DB_USER --defaults-extra-file=~/.my.cnf \
  --batch \
  --skip-column-names=false \
  --delimiter='|' \
  -e "$QUERY" > $OUTPUT_FILE

# Optional: Compress to save storage
gzip $OUTPUT_FILE

Security Note: Store your password in a restricted ~/.my.cnf file (chmod 600) instead of hardcoding it:

[client]
password=your_database_password

Key flags explained:

• --batch: Disables interactive mode for clean CSV output
• --skip-column-names=false: Includes column headers (critical for ETL field mapping)
• --delimiter='|': Uses a safe separator for transaction data

2.2 Robust mysqldump Export #

Use this if you need to preserve data types or want a backup-friendly export:

#!/bin/bash

DB_HOST="your_db_host"
DB_USER="your_username"
DB_NAME="your_database"
TABLE_NAME="your_transaction_table"
OUTPUT_FILE="/path/to/daily_transactions_$(date +%Y%m%d).txt"

# Export only daily transactions (skip table structure if you only need data)
mysqldump -h $DB_HOST -u $DB_USER --defaults-extra-file=~/.my.cnf \
  $DB_NAME $TABLE_NAME \
  --where="transaction_date = CURDATE()" \
  --no-create-info \
  --fields-terminated-by='|' > $OUTPUT_FILE

# Compress the output
gzip $OUTPUT_FILE

This is great if you need to export data that aligns with MySQL's native formatting for later reimports.

3. Pro Tips for ETL-Friendly Exports #

• Date Filtering: Always use CURDATE() or parameterized date ranges to avoid exporting the entire 200-column table daily
• Special Character Handling: Stick with delimiters like |, ^, or tabs that don't appear in your transaction text fields
• Automation: Schedule any of these scripts with cron (e.g., 0 2 * * * /path/to/export_script.sh to run daily at 2 AM)
• Validation: Add a quick check to verify the export file has rows before passing it to your ETL pipeline

内容的提问来源于stack exchange，提问作者amulya349