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

Файлы с разделителями-запятыми (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 CLI по умолчанию будет разделять вкладки.

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

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, читая поля напрямую из базы данных, правильно экранируя их и записывая файл с разделителями-запятыми.

Пример на Питоне.