Как экспортировать файл CSV из командной строки MySQL – CloudSavvy IT

Файлы с разделителями-запятыми (CSV) – это способ передачи данных между приложениями. Базы данных, такие как MySQL, и программы для работы с электронными таблицами, такие как Excel, поддерживают импорт и экспорт через CSV, поэтому вы можете использовать файлы CSV для обмена данными между ними.

CSV-файлы являются открытым текстом, поэтому они легковесны и их легко экспортировать из MySQL.

С сервера базы данных

Если у вас есть доступ к серверу, на котором работает MySQL, вы можете экспортировать выбор с помощью INTO OUTFILE команда.

SELECT id, column1, column2 FROM table
INTO OUTFILE '/tmp/mysqlfiles/table.csv'
FIELDS TERMINATED BY ','
ENCLOSED BY '"'
LINES TERMINATED BY 'n'

Это выведет файл CSV в /tmp/mysqlfiles/table.csvили там, где вы его настроили. Вы должны убедиться, что пользователь работает с MySQL (обычно mysql или root) имеет право собственности и право записи в каталог.

Вы также должны убедиться, что secure_file_priv настройка позволяет MySQL получить доступ к этому каталогу. Это по умолчанию блокирует доступ на чтение и запись из SQL-запросов. Это хорошая вещь; если ваш код уязвим для внедрения SQL, любой потенциальный злоумышленник будет иметь доступ только к MySQL, а не к остальной части файловой системы.

Вы можете внести в белый список определенные каталоги, отредактировав свой конфигурационный файл MySQL (обычно расположенный по адресу /etc/my.cnf) включать:

[mysqld]
secure-file-priv = "/tmp/mysqlfiles"

Что позволит MySQL читать и писать в /tmp/mysqlfiles/ (который вы должны будете создать с помощью mkdir). Как только MySQL сможет экспортировать файлы, вы сможете запускать запрос и выводить файлы CSV.

С ENCLOSED BY настройки, запятые будут правильно экранированы, например:

"3","Escape, this","also, this"

Которые вы можете взять и импортировать непосредственно в любую программу электронных таблиц или другое программное обеспечение.

Помните, что экспортированный CSV-файл не включает заголовки столбцов, но столбцы будут в том же порядке, что и SELECT заявление. Кроме того, нулевые значения будут экспортированы как N, что является ожидаемым поведением, но если вы хотите изменить это, вы можете изменить выбор, обернув ifnull(field, "") вокруг ваших полей в вашем SELECT заявление.

Из командной строки MySQL

Если у вас есть только доступ из командной строки к экземпляру MySQL, а не доступ к самому серверу (например, когда он не управляется вами, в случае Amazon RDS), проблема немного сложнее. Пока вы можете использовать FIELDS TERMINATED BY ',' на сервере для создания списка, разделенного запятыми, интерфейс командной строки MySQL по умолчанию разделяется на вкладки.

Просто введите запрос из командной строки и направьте его в файл:

mysql -u root -e "select * from database;" > output.tsv

Поскольку выходные данные MySQL разделены вкладками, это называется файлом TSV для «значений, разделенных табуляцией», и может работать вместо вашего файла CSV в некоторых программах, таких как импорт электронных таблиц. Но это не файл CSV, и конвертировать его в один сложно.

Вы можете просто заменить каждую вкладку запятой, которая будет работать, но приведет к сбою, если во входных данных есть запятые. Если вы на 100% уверены, что в вашем файле TSV нет запятых (обратитесь к grep), вы можете заменить вкладки на sed:

sed "s/t/,/g" output.tsv > output.csv

Но если у вас есть запятые в ваших данных, вам придется использовать намного длиннее регулярное выражение:

sed "s/'/'/;s/t/","/g;s/^/"/;s/$/"/;s/n//g" output.tsv > output.csv

Это будет правильно экранировать поля с кавычками, что решит проблему с запятыми.

Примечание: символ табуляции t является не стандарт, В macOS и BSD он недоступен, что приводит к путанице в каждой строчной букве «t», вызывающей sed вставить ошибочные запятые. Чтобы решить эту проблему, вам нужно использовать буквенный символ табуляции вместо t:

sed "s/  /,/g" output.tsv > output.csv

Если ваши входные данные содержат вкладки, вам не повезло, и вам придется самостоятельно создавать файл CSV с использованием языка сценариев.

Сделайте это вручную с реальным языком программирования

MySQL (и большинство баз данных) предназначены для взаимодействия, поэтому у вас, вероятно, уже есть какой-то язык программирования, связанный с MySQL. Большинство языков также могут записывать на диск, поэтому вы можете создавать свои собственные выходные скрипты CSV, читая поля из базы данных напрямую, правильно их экранируя и записывая файл с разделителями-запятыми.

Пример в Python,