Полное руководство: экспорт запросов MySQL в CSV

MySQL предоставляет несколько встроенных методов для экспорта результатов запросов в формат CSV из командной строки Linux, среди которых наиболее эффективными являются встроенная команда SELECT INTO OUTFILE и различные подходы к обработке командной строки. Метод SELECT INTO OUTFILE специально разработан для этой цели и автоматически обрабатывает форматирование CSV, включая правильное экранирование специальных символов, таких как кавычки.

Содержание

• Встроенные методы MySQL
• Подходы к обработке командной строки
• Интеграция сторонних инструментов
• Продвинутые техники форматирования CSV
• Сравнение производительности
• Лучшие практики для производственного использования

Встроенные методы MySQL

Метод SELECT INTO OUTFILE

Наиболее эффективный встроенный метод использует нативный синтаксис MySQL SELECT INTO OUTFILE, который напрямую записывает результаты запроса в файл с форматированием CSV:

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

Ключевые преимущества:

• Автоматически обрабатывает форматирование CSV
• Правильно экранирует кавычки и специальные символы
• Более эффективен, чем постобработка внешними инструментами
• Встроенная обработка ошибок MySQL

Выполнение из командной строки:

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';
"

Важные замечания:

• Сервер MySQL должен иметь права на запись в выходную директорию
• Файл не должен уже существовать (MySQL вернет ошибку)
• Путь должен быть абсолютным и доступным для процесса сервера MySQL
• Пользователь MySQL требует привилегию FILE

Использование mysql -e с пользовательским форматированием

Для случаев, когда INTO OUTFILE не подходит, можно использовать mysql -e с обработкой командной строки:

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

Этот подход обрабатывает табуляцией-разделенный вывод из MySQL и преобразует его в формат CSV.

Подходы к обработке командной строки

Улучшенный скрипт с корректной обработкой CSV

Вот улучшенная версия вашего текущего подхода, которая обрабатывает несколько столбцов и экранирование кавычек:

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

Использование Perl для продвинутой обработки CSV

Perl предлагает надежную обработку CSV с модулем Text::CSV:

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 для сложных требований к CSV

Для наиболее надежной обработки CSV:

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

Интеграция сторонних инструментов

Использование csvkit

csvkit предоставляет отличные утилиты для работы с CSV:

# Сначала установите csvkit
pip install csvkit

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

Альтернатива с использованием MySQL Workbench

Если доступно, можно использовать возможности экспорта из командной строки MySQL Workbench:

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

Продвинутые техники форматирования CSV

Обработка специальных символов и Unicode

Для данных, содержащих специальные символы, Unicode или символы новой строки:

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

Пакетная обработка с несколькими запросами

Для экспорта нескольких таблиц или сложных пакетных операций:

#!/bin/bash

# Скрипт экспорта с обработкой ошибок
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

Сравнение производительности

Лучшие практики для производственного использования

Вопросы безопасности

• Избегайте хранения паролей в истории команд, используя файлы конфигурации
• Используйте плагины аутентификации MySQL, когда они доступны
• Устанавливайте соответствующие права доступа к файлам вывода

# Безопасный файл конфигурации MySQL
~/.my.cnf:
[client]
user = uid
password = pwd

# Использование
mysql dbname -e "SELECT INTO OUTFILE '/secure/path.csv' ..."

Обработка ошибок и валидация

#!/bin/bash

# Надежный скрипт экспорта
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 "Экспорт выполнен успешно: ${OUTPUT_FILE}"
    chmod 644 "${OUTPUT_FILE}"
else
    echo "Ошибка экспорта" >&2
    exit 1
fi

Автоматизированный запланированный экспорт

Для регулярных экспортов рассмотрите использование cron:

# Добавить в crontab
0 2 * * * /path/to/export_script.sh >> /var/log/mysql_export.log 2>&1

Метод SELECT INTO OUTFILE остается наиболее эффективным встроенным решением для экспорта MySQL в CSV в Linux, предлагая лучшую производительность и качество CSV при автоматической обработке крайних случаев, таких как экранирование кавычек. Для сред, где доступ к файловой системе ограничен, подходы к обработке командной строки с awk, perl или python предоставляют отличные альтернативы с различными уровнями сложности и надежности.

Источники

1. Документация MySQL - SELECT INTO OUTFILE
2. Документация MySQL - Опции FIELDS и LINES
3. Документация модуля Text::CSV Perl
4. Инструменты командной строки csvkit
5. Справочник по клиенту командной строки MySQL

Заключение

• SELECT INTO OUTFILE - наиболее эффективный встроенный метод MySQL для экспорта в CSV, обеспечивающий автоматическое форматирование CSV и правильное экранирование
• Обработка командной строки с awk, perl или python обеспечивает гибкость, когда доступ к файловой системе ограничен
• Сторонние инструменты такие как csvkit обеспечивают надежную обработку CSV, но требуют дополнительной настройки
• Лучшие практики безопасности включают использование файлов конфигурации вместо паролей в командной строке
• Обработка ошибок и валидация необходимы для производственных скриптов экспорта
• Выбирайте метод, который лучше всего сбалансирован производительность, безопасность и операционные требования для вашей конкретной среды

Оптимальное решение зависит от ваших конкретных требований, но встроенные методы MySQL обычно обеспечивают лучшую производительность и надежность для экспорта в CSV из командной строки Linux.