Как выводить результаты запросов MySQL в формате CSV?

Есть ли простой способ запустить MySQL-запрос из командной строки Linux и вывести результаты в формате CSV ?

Вот что я делаю сейчас:

mysql -u uid -ppwd -D dbname << EOQ | sed -e 's/ /,/g' | tee list.csv select id, concat("\"",name,"\"") as name from students EOQ 

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

    С http://www.tech-recipes.com/rx/1475/save-mysql-query-results-into-a-text-or-csv-file/

     SELECT order_id,product_name,qty FROM orders WHERE foo = 'bar' INTO OUTFILE '/var/lib/mysql-files/orders.csv' FIELDS TERMINATED BY ',' ENCLOSED BY '"' LINES TERMINATED BY '\n'; 

    Использование этой команды имена столбцов не будут экспортироваться.

    Также обратите внимание, что /var/lib/mysql-files/orders.csv будет находиться на сервере, на котором запущена MySQL. Пользователь, которому запущен процесс MySQL, должен иметь права на запись в выбранный каталог, иначе команда не будет выполнена.

    Если вы хотите записать вывод на свой локальный компьютер с удаленного сервера (особенно с размещенной или виртуализированной машиной, такой как Heroku или Amazon RDS), это решение не подходит.

     $ mysql your_database --password=foo < my_requests.sql > out.csv 

    Это вкладка разделена. Труба это так, чтобы получить настоящий CSV (спасибо @therefromhere):

     ... .sql | sed 's/\t/,/g' > out.csv 

    mysql –batch, -B

    Распечатывайте результаты, используя вкладку в качестве разделителя столбцов, каждая строка на новой строке. С помощью этой опции mysql не использует файл истории. Пакетный режим приводит к нестандартному выходному формату и экранированию специальных символов. Экранирование может быть отключено с использованием режима raw; см. описание опции -raw.

    Это даст вам файл с разделителями вкладок. Поскольку запятые (или строки, содержащие запятую) не сбрасываются, не просто изменить разделитель на запятую.

    Вот довольно грубый способ сделать это. Найденный где-то, не может взять кредит

    mysql --user=wibble --password wobble -B -e "select * from vehicle_categories;" | sed "s/'/\'/;s/\t/\",\"/g;s/^/\"/;s/$/\"/;s/\n//g" > vehicle_categories.csv

    Работает очень хорошо. Еще раз, хотя регулярное выражение доказывает только запись.


    Регулярное выражение Пояснение:

    • s /// означает замену того, что находится между первым // с тем, что находится между вторым //
    • «g» в конце является модификатором, который означает «все экземпляры, а не только первые»,
    • ^ (в этом контексте) означает начало строки
    • $ (в этом контексте) означает конец строки

    Итак, все вместе:

     s/'/\'/ replace ' with \' s/\t/\",\"/g replace all \t (tab) with "," s/^/\"/ at the beginning of the line place a " s/$/\"/ at the end of the line place a " s/\n//g replace all \n (newline) with nothing 

    Только Unix / Cygwin , пропустите его через ‘tr’:

     mysql  -e "" | tr '\t' ',' > data.csv 

    NB: Это не поддерживает ни встроенные запятые, ни встроенные вкладки.

    Решение OUTFILE, данное Paul Tomblin, заставляет файл записываться на сервере MySQL, поэтому он будет работать только в том случае, если у вас есть доступ к файлу, а также доступ к логину или другие средства для извлечения файла из этого поля.

    Если у вас нет такого доступа, а вывод с разделителями табуляции является разумной заменой CSV (например, если конечная цель заключается в том, чтобы импортировать в Excel), то решение Serbaut (с использованием mysql --batch и необязательно --raw ) путь.

    Это спасло меня пару раз. Быстро и работает!

    –batch

    –raw

    Пример:

     sudo mysql -udemo_user -p -h127.0.0.1 --port=3306 \ --default-character-set=utf8 --database=demo_database \ --batch --raw < /var/demo_sql_query.sql > /var/demo_csv_export.csv 

    Как насчет:

     mysql your_database -p < my_requests.sql | awk '{print $1","$2}' > out.csv 

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

    Все решения, имеющиеся на сегодняшний день, за исключением Mysql workbench one, являются неправильными и, вполне возможно, небезопасными (т.е. проблемами безопасности), по крайней мере, для некоторого возможного контента в mysql db.

    Mysql Workbench (и аналогично PHPMyAdmin) предоставляют формально правильное решение, но предназначены для загрузки вывода в местоположение пользователя. Они не так полезны для таких вещей, как автоматизация экспорта данных.

    Невозможно создать достоверно корректный csv из вывода mysql -B -e 'SELECT ...' потому что он не может кодировать возврат каретки и пробел в полях. Флаг ‘-s’ для mysql делает обратное сбрасывание и может привести к правильному решению. Однако использование языка сценариев (один с хорошими внутренними структурами данных, а не bash) и библиотеки, где проблемы с кодировкой уже тщательно разработаны, намного безопаснее.

    Я подумал о написании сценария для этого, но как только я подумал о том, что бы я назвал, мне пришло в голову искать ранее существовавшую работу с тем же именем. В то время как я не прошел его полностью, решение на https://github.com/robmiller/mysql2csv выглядит многообещающим. В зависимости от вашего приложения подход yaml для указания команд SQL может или не понравится. Я также не в восторге от необходимости в более новой версии rubyов, чем стандартно с моим ноутбуком Ubuntu 12.04 или сервером Squeeze Debian. Да, я знаю, что могу использовать RVM, но я бы предпочел не поддерживать это для такой простой цели.

    Надеюсь, кто-то укажет подходящий инструмент, у которого было немного тестирования. В противном случае я, вероятно, обновлю это, когда найду или напишу.

    Из командной строки вы можете сделать это:

     mysql -h *hostname* -P *port number* --database=*database_name* -u *username* -p -e *your SQL query* | sed 's/\t/","/g;s/^/"/;s/$/"/;s/\n//g' > *output_file_name.csv* 

    Кредиты: Экспорт таблицы из Amazon RDS в файл csv

    1. логика:

    CREATE TABLE () (SELECT data FROM other_table ) ENGINE=CSV ;

    Когда вы создаете таблицу CSV, сервер создает файл формата таблицы в каталоге базы данных. Файл начинается с имени таблицы и имеет расширение .frm. Механизм хранения также создает файл данных. Его имя начинается с имени таблицы и имеет расширение .CSV. Файл данных представляет собой текстовый файл. Когда вы храните данные в таблице, механизм хранения сохраняет его в файл данных в формате значений, разделенных запятыми.

    Многие ответы на этой странице слабы, потому что они не обрабатывают общий случай того, что может произойти в формате CSV. например, запятые и цитаты, встроенные в поля и другие условия, которые всегда появляются в конечном итоге. Нам нужно общее решение, которое работает для всех допустимых входных данных CSV.

    Вот простое и сильное решение в Python:

     #!/usr/bin/env python import csv import sys tab_in = csv.reader(sys.stdin, dialect=csv.excel_tab) comma_out = csv.writer(sys.stdout, dialect=csv.excel) for row in tab_in: comma_out.writerow(row) 

    Назовите этот файл tab2csv , поместите его на свой путь, дайте ему права на выполнение, затем используйте его в списке:

     mysql OTHER_OPTIONS --batch --execute='select * from whatever;' | tab2csv >outfile.csv 

    Функции Python CSV-обработки охватывают угловые случаи для формата (ов) CSV.

    Это может быть улучшено для обработки очень больших файлов с помощью streamового подхода.

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

     mysql -B -D mydatabase -e 'select * from mytable' 

    Удобно, мы можем использовать тот же метод, чтобы перечислять таблицы MySQL, и описывать поля в одной таблице:

     mysql -B -D mydatabase -e 'show tables' mysql -B -D mydatabase -e 'desc users' Field Type Null Key Default Extra id int(11) NO PRI NULL auto_increment email varchar(128) NO UNI NULL lastName varchar(100) YES NULL title varchar(128) YES UNI NULL userName varchar(128) YES UNI NULL firstName varchar(100) YES NULL 

    Это просто, и он работает на чем угодно, не требуя пакетного режима или выходных файлов:

     select concat_ws(',', concat('"', replace(field1, '"', '""'), '"'), concat('"', replace(field2, '"', '""'), '"'), concat('"', replace(field3, '"', '""'), '"')) from your_table where etc; 

    Объяснение:

    1. Заменить “на” “в каждом поле -> заменить (поле1, ‘”‘, ‘””‘)
    2. Выровнять каждый результат в кавычках -> concat (” ‘, result1,’ ”)
    3. Поместите запятую между каждым цитируемым результатом -> concat_ws (‘,’, quoted1, quoted2, …)

    Это оно!

    Кроме того, если вы выполняете запрос в командной строке Bash, я считаю, что команда tr может использоваться для замены вкладок по умолчанию на произвольные разделители.

    $ echo "SELECT * FROM Table123" | mysql Database456 | tr "\t" ,

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

    Тогда у вас будет файл на жестком диске, который всегда будет в формате CSV, который вы могли бы просто скопировать, не обработав его.

    Вот что я делаю:

     echo $QUERY | \ mysql -B $MYSQL_OPTS | \ perl -F"\t" -lane 'print join ",", map {s/"/""/g; /^[\d.]+$/ ? $_ : qq("$_")} @F ' | \ mail -s 'report' [email protected] 

    Скрипт perl (sniped из другого места) делает хорошую работу по преобразованию полей, расположенных на вкладке, в CSV.

    Не точно как формат CSV, но команда tee из клиента MySQL может использоваться для сохранения вывода в локальном файле:

     tee foobar.txt SELECT foo FROM bar; 

    Вы можете отключить его с помощью notee .

    Проблема с SELECT … INTO OUTFILE …; заключается в том, что для этого требуется разрешение на запись файлов на сервер.

    Основываясь на user7610, вот лучший способ сделать это. В mysql outfile было 60 минут владения файлами и проблемы с перезаписью.

    Это не круто, но он работал через 5 минут.

    php csvdump.php localhost root password database tablename > whatever-you-like.csv

     name; } fputcsv($output, $fields); // loop over the rows, outputting them while ($row = mysql_fetch_assoc($rows)) fputcsv($output, $row); ?> 

    Этот ответ использует Python и популярную стороннюю библиотеку PyMySQL . Я добавляю это, потому что библиотека csv Python достаточно мощна, чтобы правильно обрабатывать множество различных вариантов .csv и никакие другие ответы не используют код Python для взаимодействия с базой данных.

     import contextlib import csv import datetime import os # https://github.com/PyMySQL/PyMySQL import pymysql SQL_QUERY = """\ SELECT * FROM my_table WHERE my_attribute = 'my_attribute'; """ # embedding passwords in code gets nasty when you use version control # the environment isn't much better, but this is an example # http://stackoverflow.com/questions/12461484/is-it-secure-to-store-passwords-as-environment-variables-rather-than-as-plain-t SQL_USER = os.environ['SQL_USER'] SQL_PASS = os.environ['SQL_PASS'] connection = pymysql.connect(host='localhost', user=SQL_USER, password=SQL_PASS, db='dbname') with contextlib.closing(connection): with connection.cursor() as cursor: cursor.execute(SQL_QUERY) # Hope you have enough memory :) results = cursor.fetchall() output_file = 'my_query-{}.csv'.format(datetime.datetime.today().strftime('%Y-%m-%d')) with open(output_file, 'w', newline='') as csvfile: # http://stackoverflow.com/a/17725590/2958070 about lineterminator csv_writer = csv.writer(csvfile, lineterminator='\n') csv_writer.writerows(results) 

    Используя решение, отправленное Тимом, я создал этот скрипт bash для облегчения процесса (запрошен пароль root, но вы можете легко изменить скрипт, чтобы попросить любого другого пользователя):

     #!/bin/bash if [ "$1" == "" ];then echo "Usage: $0 DATABASE TABLE [MYSQL EXTRA COMMANDS]" exit fi DBNAME=$1 TABLE=$2 FNAME=$1.$2.csv MCOMM=$3 echo "MySQL password:" stty -echo read PASS stty echo mysql -uroot -p$PASS $MCOMM $DBNAME -B -e "SELECT * FROM $TABLE;" | sed "s/'/\'/;s/\t/\",\"/g;s/^/\"/;s/$/\"/;s/\n//g" > $FNAME 

    Он создаст файл с именем: database.table.csv

    Попробуйте этот код:

     SELECT 'Column1', 'Column2', 'Column3', 'Column4', 'Column5' UNION ALL SELECT column1, column2, column3 , column4, column5 FROM demo INTO OUTFILE '/tmp/demo.csv' FIELDS TERMINATED BY ',' ENCLOSED BY '"' LINES TERMINATED BY '\n'; 

    Для получения дополнительной информации: http://dev.mysql.com/doc/refman/5.1/en/select-into.html

    Если у вас установлен PHP на сервере, вы можете использовать mysql2csv для экспорта (фактически действительного) CSV-файла для произвольного запроса mysql. См. Мой ответ в MySQL – SELECT * INTO OUTFILE LOCAL? для немного больше context / info.

    Я попытался сохранить имена опций из mysql поэтому должно быть достаточно, чтобы предоставить параметры --query и --query :

     ./mysql2csv --file="/tmp/result.csv" --query='SELECT 1 as foo, 2 as bar;' --user="username" --password="password" 

    «Установить» mysql2csv через

     wget https://gist.githubusercontent.com/paslandau/37bf787eab1b84fc7ae679d1823cf401/raw/29a48bb0a43f6750858e1ddec054d3552f3cbc45/mysql2csv -O mysql2csv -q && (sha256sum mysql2csv | cmp <(echo "b109535b29733bd596ecc8608e008732e617e97906f119c66dd7cf6ab2865a65 mysql2csv") || (echo "ERROR comparing hash, Found:" ;sha256sum mysql2csv) ) && chmod +x mysql2csv 

    (загрузите содержимое сущности, проверьте контрольную сумму и сделайте ее исполняемой).

    Если на компьютере, который вы используете, установлен PHP, вы можете написать PHP-скрипт для этого. Для установки PHP требуется расширение MySQL.

    Вы можете вызвать интерпретатор PHP из командной строки следующим образом:

     php --php-ini path/to/php.ini your-script.php 

    Я --php-ini ключ --php-ini , потому что вам может понадобиться использовать собственную конфигурацию PHP, которая позволяет расширять MySQL. На PHP 5.3.0+ это расширение включено по умолчанию, поэтому больше нет необходимости использовать конфигурацию для его включения.

    Затем вы можете написать свой сценарий экспорта, как любой обычный PHP-скрипт:

      

    Преимущество этого метода состоит в том, что он не имеет проблем с varchar и текстовыми полями, в которых есть текст, содержащий новые строки. Эти поля правильно цитируются, и эти новые строки в них будут интерпретироваться читателем CSV как часть текста, а не разделители записей. Это то, что трудно исправить после седа или так.

    Крошечный скрипт bash для простого запроса к отвалам CSV, вдохновленный https://stackoverflow.com/a/5395421/2841607 .

     #!/bin/bash # $1 = query to execute # $2 = outfile # $3 = mysql database name # $4 = mysql username if [ -z "$1" ]; then echo "Query not given" exit 1 fi if [ -z "$2" ]; then echo "Outfile not given" exit 1 fi MYSQL_DB="" MYSQL_USER="root" if [ ! -z "$3" ]; then MYSQL_DB=$3 fi if [ ! -z "$4" ]; then MYSQL_USER=$4 fi if [ -z "$MYSQL_DB" ]; then echo "Database name not given" exit 1 fi if [ -z "$MYSQL_USER" ]; then echo "Database user not given" exit 1 fi mysql -u $MYSQL_USER -p -D $MYSQL_DB -B -s -e "$1" | sed "s/'/\'/;s/\t/\",\"/g;s/^/\"/;s/$/\"/;s/\n//g" > $2 echo "Written to $2" 

    Для меня работает следующий сценарий bash. Он также может получить схему для запрошенных таблиц.

     #!/bin/bash # # export mysql data to CSV #https://stackoverflow.com/questions/356578/how-to-output-mysql-query-results-in-csv-format # #ansi colors #http://www.csc.uvic.ca/~sae/seng265/fall04/tips/s265s047-tips/bash-using-colors.html blue='\033[0;34m' red='\033[0;31m' green='\033[0;32m' # '\e[1;32m' is too bright for white bg. endColor='\033[0m' # # a colored message # params: # 1: l_color - the color of the message # 2: l_msg - the message to display # color_msg() { local l_color="$1" local l_msg="$2" echo -e "${l_color}$l_msg${endColor}" } # # error # # show the given error message on stderr and exit # # params: # 1: l_msg - the error message to display # error() { local l_msg="$1" # use ansi red for error color_msg $red "Error:" 1>&2 color_msg $red "\t$l_msg" 1>&2 usage } # # display usage # usage() { echo "usage: $0 [-h|--help]" 1>&2 echo " -o | --output csvdirectory" 1>&2 echo " -d | --database database" 1>&2 echo " -t | --tables tables" 1>&2 echo " -p | --password password" 1>&2 echo " -u | --user user" 1>&2 echo " -hs | --host host" 1>&2 echo " -gs | --get-schema" 1>&2 echo "" 1>&2 echo " output: output csv directory to export mysql data into" 1>&2 echo "" 1>&2 echo " user: mysql user" 1>&2 echo " password: mysql password" 1>&2 echo "" 1>&2 echo " database: target database" 1>&2 echo " tables: tables to export" 1>&2 echo " host: host of target database" 1>&2 echo "" 1>&2 echo " -h|--help: show help" 1>&2 exit 1 } # # show help # help() { echo "$0 Help" 1>&2 echo "===========" 1>&2 echo "$0 exports a csv file from a mysql database optionally limiting to a list of tables" 1>&2 echo " example: $0 --database=cms --user=scott --password=tiger --tables=person --output person.csv" 1>&2 echo "" 1>&2 usage } domysql() { mysql --host $host -u$user --password=$password $database } getcolumns() { local l_table="$1" echo "describe $l_table" | domysql | cut -f1 | grep -v "Field" | grep -v "Warning" | paste -sd "," - 2>/dev/null } host="localhost" mysqlfiles="/var/lib/mysql-files/" # parse command line options while true; do #echo "option $1" case "$1" in # options without arguments -h|--help) usage;; -d|--database) database="$2" ; shift ;; -t|--tables) tables="$2" ; shift ;; -o|--output) csvoutput="$2" ; shift ;; -u|--user) user="$2" ; shift ;; -hs|--host) host="$2" ; shift ;; -p|--password) password="$2" ; shift ;; -gs|--get-schema) option="getschema";; (--) shift; break;; (-*) echo "$0: error - unrecognized option $1" 1>&2; usage;; (*) break;; esac shift done # checks if [ "$csvoutput" == "" ] then error "ouput csv directory not set" fi if [ "$database" == "" ] then error "mysql database not set" fi if [ "$user" == "" ] then error "mysql user not set" fi if [ "$password" == "" ] then error "mysql password not set" fi color_msg $blue "exporting tables of database $database" if [ "$tables" = "" ] then tables=$(echo "show tables" | domysql) fi case $option in getschema) rm $csvoutput$database.schema for table in $tables do color_msg $blue "getting schema for $table" echo -n "$table:" >> $csvoutput$database.schema getcolumns $table >> $csvoutput$database.schema done ;; *) for table in $tables do color_msg $blue "exporting table $table" cols=$(grep "$table:" $csvoutput$database.schema | cut -f2 -d:) if [ "$cols" = "" ] then cols=$(getcolumns $table) fi ssh $host rm $mysqlfiles/$table.csv cat < $csvoutput$table.csv rm $csvoutput$table.csv.raw done ;; esac 
    Давайте будем гением компьютера.