Complete Guide: MySQL Query to CSV Export

MySQL provides several built-in methods for exporting query results to CSV format from the Linux command line, with the most efficient being the built-in SELECT INTO OUTFILE command and various command-line processing approaches. The SELECT INTO OUTFILE method is specifically designed for this purpose and handles CSV formatting automatically, including proper escaping of special characters like quotes.

Contents

• Built-in MySQL Methods
• Command Line Processing Approaches
• Third-Party Tools Integration
• Advanced CSV Formatting Techniques
• Performance Comparison
• Best Practices for Production Use

Built-in MySQL Methods

SELECT INTO OUTFILE Method

The most efficient built-in method uses MySQL’s native SELECT INTO OUTFILE syntax, which directly writes query results to a file with CSV formatting:

SELECT id, name, email
FROM students
INTO OUTFILE '/tmp/students.csv'
FIELDS TERMINATED BY ',' 
ENCLOSED BY '"'
LINES TERMINATED BY '\n';

Key advantages:

• Handles CSV formatting automatically
• Properly escapes quotes and special characters
• More efficient than post-processing with external tools
• Built-in MySQL error handling

Command line execution:

mysql -u uid -ppwd -D dbname -e "
SELECT id, name, email
FROM students
INTO OUTFILE '/tmp/students.csv'
FIELDS TERMINATED BY ',' 
ENCLOSED BY '\"'
LINES TERMINATED BY '\n';
"

Important considerations:

• The MySQL server must have write permissions to the output directory
• The file must not already exist (MySQL will return an error)
• The path must be absolute and accessible by the MySQL server process
• The MySQL user needs FILE privilege

Using mysql -e with Custom Formatting

For cases where INTO OUTFILE isn’t suitable, you can use mysql -e with command-line processing:

mysql -u uid -ppwd -D dbname -e "SELECT id, name, email FROM students" \
| awk -F'\t' 'BEGIN {OFS=","} {for(i=1;i<=NF;i++) gsub(/"/,"\"\"",$i); print}' \
> output.csv

This approach processes the tab-separated output from MySQL and converts it to CSV format.

Command Line Processing Approaches

Enhanced Script with Proper CSV Handling

Here’s an improved version of your current approach that handles multiple columns and quote escaping:

mysql -u uid -ppwd -D dbname -e "SELECT id, name, email FROM students" \
| awk -F'\t' 'BEGIN {OFS=","} {
  for(i=1; i<=NF; i++) {
    if($i ~ /"/) {
      gsub(/"/,"\"\"",$i)
    }
    if($i ~ /[ ,\t]/) {
      $i = "\"" $i "\""
    }
  }
  print
}' > output.csv

Using Perl for Advanced CSV Processing

Perl offers robust CSV handling with Text::CSV module:

mysql -u uid -ppwd -D dbname -e "SELECT id, name, email FROM students" \
| perl -e '
use Text::CSV;
my $csv = Text::CSV->new({ binary => 1, auto_diag => 2 });
while (<>) {
  chomp;
  my @fields = split(/\t/, $_);
  $csv->print(*STDOUT, \@fields);
  print "\n";
}
' > output.csv

Python Solution for Complex CSV Requirements

For the most robust CSV handling:

mysql -u uid -ppwd -D dbname -e "SELECT id, name, email FROM students" \
| python3 -c "
import csv
import sys
import re

reader = csv.reader(sys.stdin, delimiter='\t')
writer = csv.writer(sys.stdout, quoting=csv.QUOTE_ALL)
for row in reader:
    writer.writerow(row)
" > output.csv

Third-Party Tools Integration

Using csvkit

csvkit provides excellent CSV handling utilities:

# Install csvkit first
pip install csvkit

mysql -u uid -ppwd -D dbname -e "SELECT id, name, email FROM students" \
| csvformat -t -D ',' | csvquote > output.csv

Using MySQL Workbench Alternative

If available, you can use MySQL Workbench’s command-line export capabilities:

mysqlexport --user=uid --password=pwd --database=dbname \
--query="SELECT id, name, email FROM students" \
--export_type=csv --output_file=output.csv

Advanced CSV Formatting Techniques

Handling Special Characters and Unicode

For data containing special characters, Unicode, or newlines:

mysql -u uid -ppwd -D dbname -e "SELECT id, name, email FROM students" \
| perl -e '
use Text::CSV;
use Encode;
my $csv = Text::CSV->new({ binary => 1, auto_diag => 2, encoding => \"UTF-8\" });
while (<>) {
  chomp;
  my @fields = split(/\t/, $_);
  for my $field (@fields) {
    $field = decode(\"UTF-8\", $field) if utf8::is_utf8($field);
  }
  $csv->print(*STDOUT, \@fields);
  print \"\n\";
}
' > output.csv

Batch Processing with Multiple Queries

For exporting multiple tables or complex batch operations:

#!/bin/bash

# Export script with error handling
mysql -u uid -ppwd -D dbname << 'EOF' | python3 -c "
import csv
import sys

table_name = sys.argv[1] if len(sys.argv) > 1 else 'students'
reader = csv.reader(sys.stdin, delimiter='\t')
writer = csv.writer(sys.stdout, quoting=csv.QUOTE_ALL)
for row in reader:
    writer.writerow(row)
" "${table_name}.csv"

SELECT * FROM students;
EOF

Performance Comparison

Best Practices for Production Use

Security Considerations

• Avoid storing passwords in command history by using configuration files
• Use MySQL authentication plugins when available
• Set appropriate file permissions on output files

# Secure MySQL configuration file
~/.my.cnf:
[client]
user = uid
password = pwd

# Usage
mysql dbname -e "SELECT INTO OUTFILE '/secure/path.csv' ..."

Error Handling and Validation

#!/bin/bash

# Robust export script
OUTPUT_FILE="/tmp/export_$(date +%Y%m%d_%H%M%S).csv"

if mysql -u uid -ppwd -D dbname -e "
SELECT id, name, email 
FROM students 
INTO OUTFILE '${OUTPUT_FILE}'
FIELDS TERMINATED BY ',' 
ENCLOSED BY '\"'
LINES TERMINATED BY '\n';
"; then
    echo "Export successful: ${OUTPUT_FILE}"
    chmod 644 "${OUTPUT_FILE}"
else
    echo "Export failed" >&2
    exit 1
fi

Automated Scheduled Exports

For regular exports, consider using cron:

# Add to crontab
0 2 * * * /path/to/export_script.sh >> /var/log/mysql_export.log 2>&1

The SELECT INTO OUTFILE method remains the most efficient built-in solution for MySQL to CSV exports on Linux, offering the best performance and CSV quality while handling edge cases like quote escaping automatically. For environments where file system access is restricted, the command-line processing approaches with awk, perl, or python provide excellent alternatives with varying levels of complexity and robustness.

Sources

1. MySQL Documentation - SELECT INTO OUTFILE
2. MySQL Documentation - FIELDS and LINES Options
3. Text::CSV Perl Module Documentation
4. csvkit Command Line Tools
5. MySQL Command-Line Client Reference

Conclusion

• SELECT INTO OUTFILE is the most efficient built-in MySQL method for CSV exports, providing automatic CSV formatting and proper escaping
• Command-line processing with awk, perl, or python offers flexibility when file system access is restricted
• Third-party tools like csvkit provide robust CSV handling but require additional setup
• Security best practices include using configuration files instead of command-line passwords
• Error handling and validation are essential for production export scripts
• Choose the method that best balances performance, security, and operational requirements for your specific environment

The optimal solution depends on your specific requirements, but the built-in MySQL methods generally provide the best performance and reliability for CSV exports from Linux command line.