Есть ли простой способ запустить 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
Хорошо работает. Еще раз, хотя регулярное выражение доказывает только запись.
Regex Пояснение:
Итак, все вместе:
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
mysql -B
. Если вы разделите регулярное выражение на отдельные утверждения в отдельных строках, это будет довольно просто (для тех, кто знает регулярное выражение) понять.
Unix/Cygwin, пропустите его через 'tr':
mysql <database> -e "<query here>" | tr '\t' ',' > data.csv
N.B.: В нем не используются ни встроенные запятые, ни встроенные вкладки.
Это спасло меня пару раз. Быстро и работает!
Пример:
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
Решение OUTFILE, данное Paul Tomblin, заставляет файл записываться на сервере MySQL, поэтому это будет работать, только если у вас есть FILE, а также доступ к логину или другие средства для извлечения файла из этого поля.
Если у вас нет такого доступа, а вывод с разделителями-табуляторами является разумной заменой CSV (например, если конечная цель заключается в том, чтобы импортировать в Excel), то решение Serbaut (используя mysql --batch
и необязательно --raw
) - это путь.
MySQL Workbench может экспортировать записи в CSV, и, похоже, очень хорошо обрабатывает запятые в полях. CSV открывается в OpenOffice в порядке.
Как насчет:
mysql your_database -p < my_requests.sql | awk '{print $1","$2}' > out.csv
mysql -uUser -pPassword your_database < my_requests.sql | awk 'BEGIN{OFS="=";} {print $1,$2}' > out.csv
Все решения на сегодняшний день, за исключением Mysql workbench one, являются неправильными и, возможно, небезопасными (например, проблемами безопасности), по крайней мере, для некоторого возможного содержимого в mysql db.
Mysql Workbench (и аналогично PHPMyAdmin) предоставляют формально правильное решение, но предназначены для загрузки вывода в пользовательское местоположение. Они не так полезны для таких вещей, как автоматизация экспорта данных.
Невозможно создать достоверно корректный csv из вывода mysql -B -e 'SELECT ...'
, поскольку он не может кодировать возврат каретки и пробел в полях. Флаг '-s' для mysql делает обратное сбрасывание и может привести к правильному решению. Однако использование языка сценариев (один с достойными внутренними структурами данных, а не bash), и библиотеки, в которых проблемы с кодировкой уже были тщательно разработаны, намного безопаснее.
Я подумал о написании script для этого, но как только я подумал о том, что бы я назвал, мне пришло в голову поиск ранее существовавшей работы с тем же именем. В то время как я не перешел на это полностью, решение в https://github.com/robmiller/mysql2csv выглядит многообещающим. В зависимости от вашего приложения подход yaml для указания команд SQL может или не понравится. Я также не в восторге от необходимости в более новой версии рубинов, чем стандартно с моим ноутбуком 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*
Многие ответы на этой странице слабы, потому что они не обрабатывают общий случай того, что может произойти в формате 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.
Это может быть улучшено для обработки очень больших файлов с помощью потокового подхода.
CREATE TABLE () (SELECT data FROM other_table ) ENGINE=CSV ;
При создании таблицы CSV сервер создает файл формата таблицы в каталог базы данных. Файл начинается с имени таблицы и имеет .frm. Механизм хранения также создает файл данных. Его имя начинается с имени таблицы и имеет расширение .CSV. Файл данных простой текстовый файл. Когда вы храните данные в таблице, хранилище двигатель сохраняет его в файл данных в формате значений, разделенных запятыми.
Это просто, и он работает на чем угодно, не требуя пакетного режима или выходных файлов:
select concat_ws(',',
concat('"', replace(field1, '"', '""'), '"'),
concat('"', replace(field2, '"', '""'), '"'),
concat('"', replace(field3, '"', '""'), '"'))
from your_table where etc;
Пояснение:
Что это!
Кроме того, если вы выполняете запрос в командной строке Bash, я считаю, что команда tr
может использоваться для замены вкладок по умолчанию на произвольные разделители.
$ echo "SELECT * FROM Table123" | mysql Database456 | tr "\t" ,
В этом ответе используется Python и популярная сторонняя библиотека PyMySQL. Я добавляю его, потому что библиотека Python csv достаточно мощная, чтобы правильно обрабатывать множество различных разновидностей .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 is not 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)
Чтобы развернуть предыдущие ответы, следующий однострочный файл экспортирует одну таблицу в виде файла с разделителями табуляции. Он подходит для автоматизации, экспортируя базу данных каждый день или около того.
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
mysql -B -D mydatabase -e 'select * from mytable' | sed -e 's/\t/,/g'
sed -e 's/\t/,/g'
безопасно только в том случае, если вы уверены, что ваши данные не содержат запятых или вкладок.
В качестве альтернативы вышеприведенному ответу вы можете иметь таблицу MySQL, в которой используется механизм CSV.
Затем у вас будет файл на вашем жестком диске, который всегда будет в формате CSV, который вы могли бы просто скопировать без его обработки.
Основываясь на user7610, вот лучший способ сделать это. С mysql outfile
было 60 минут владения файлами и проблемы с перезаписью.
Это не круто, но он работал через 5 минут.
php csvdump.php localhost root password database tablename > whatever-you-like.csv
<?php
$server = $argv[1];
$user = $argv[2];
$password = $argv[3];
$db = $argv[4];
$table = $argv[5];
mysql_connect($server, $user, $password) or die(mysql_error());
mysql_select_db($db) or die(mysql_error());
// fetch the data
$rows = mysql_query('SELECT * FROM ' . $table);
$rows || die(mysql_error());
// create a file pointer connected to the output stream
$output = fopen('php://output', 'w');
// output the column headings
$fields = [];
for($i = 0; $i < mysql_num_fields($rows); $i++) {
$field_info = mysql_fetch_field($rows, $i);
$fields[] = $field_info->name;
}
fputcsv($output, $fields);
// loop over the rows, outputting them
while ($row = mysql_fetch_assoc($rows)) fputcsv($output, $row);
?>
Не точно как формат CSV, но команда tee
из клиента MySQL может использоваться для сохранения вывода в локальном файле:
tee foobar.txt
SELECT foo FROM bar;
Вы можете отключить его с помощью notee
.
Проблема с SELECT … INTO OUTFILE …;
заключается в том, что для этого требуется разрешение на запись файлов на сервер.
Вот что я делаю:
echo $QUERY | \
mysql -B $MYSQL_OPTS | \
perl -F"\t" -lane 'print join ",", map {s/"/""/g; /^[\d.]+$/ ? $_ : qq("$_")} @F ' | \
mail -s 'report' person@address
Perl script (снайпер из другого места) делает хорошую работу по преобразованию полей, расположенных на вкладке, в CSV.
perl -F"\t" -lane 'print join ",", map {s/"/""/g; /^\d+(?:\.\d+)?$/ ? $_ : qq("$_")} @F '
- ваш не цитирует 1.2.3
Используя решение, отправленное Tim, я создал этот bash script, чтобы облегчить процесс (запрошен пароль root, но вы можете легко изменить script для запроса любого другого пользователя):
#!/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
Если у вас установлен PHP на сервере, вы можете использовать mysql2csv для экспорта (фактически действительного) CSV файла для произвольного запроса mysql. См. Мой ответ в MySQL - SELECT * INTO OUTFILE LOCAL? для немного больше context/info.
Я попытался сохранить имена опций из mysql
поэтому должно быть достаточно, чтобы предоставить --file
и --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
(загрузите содержимое сущности, проверьте контрольную сумму и сделайте ее исполняемой).
Для меня работает следующий сценарий 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 <<EOF | mysql --host $host -u$user --password=$password $database
SELECT $cols FROM $table INTO OUTFILE '$mysqlfiles$table.csv'
FIELDS TERMINATED BY ','
ENCLOSED BY '"'
LINES TERMINATED BY '\n';
EOF
scp $host:$mysqlfiles/$table.csv $csvoutput$table.csv.raw
(echo "$cols"; cat $csvoutput$table.csv.raw) > $csvoutput$table.csv
rm $csvoutput$table.csv.raw
done
;;
esac
Крошечный скрипт bash для простого запроса к отвалам CSV, вдохновленный https://stackoverflow.com/questions/356578/how-to-output-mysql-query-results-in-csv-format.
#!/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"
Попробуйте этот код:
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
Вы можете использовать следующую команду из вашего редактора SQL/терминала:
" mysql -h (имя хоста /IP>) -u (имя пользователя) -p (пароль) имя базы данных <(query.sql)> outputFILE (.txt/.xls) "
например, имя хоста -x.xxx
uname - имя пользователя
пароль - пароль
DBName - employeeDB
queryFile - employee.sql
outputFile - outputFile.xls
mysql -h xxxx -u имя пользователя -p пароль employeeDB <employee.sql> outputFile.xls
Убедитесь, что вы выполняете команду из каталога, в котором расположен SQL-запрос, или укажите полный путь к местоположению SQL-запроса в приведенной выше команде.
Я использую [SQLyog] (webyog.com/product/sqlyog) для экспорта в csv. Это довольно легко.
Вот несколько ссылок на него: http://sqlyogkb.webyog.com/article/215-export-data
Если на компьютере, который вы используете, установлен PHP, вы можете написать PHP script для этого. Для установки PHP требуется расширение MySQL.
Вы можете вызвать интерпретатор PHP из командной строки следующим образом:
php --php-ini path/to/php.ini your-script.php
Я включаю переключатель --php-ini
, потому что вам может понадобиться использовать собственную конфигурацию PHP, которая позволяет расширение MySQL. На PHP 5.3.0+ это расширение включено по умолчанию, поэтому больше нет необходимости использовать конфигурацию для его включения.
Затем вы можете написать свой экспорт script, как и любой обычный PHP скрипт:
<?php
#mysql_connect("localhost", "username", "password") or die(mysql_error());
mysql_select_db("mydb") or die(mysql_error());
$result = mysql_query("SELECT * FROM table_with_the_data p WHERE p.type = $typeiwant");
$result || die(mysql_error());
while($row = mysql_fetch_row($result)) {
$comma = false;
foreach ($row as $item) {
# Make it comma separated
if ($comma) {
echo ',';
} else {
$comma = true;
}
# Quote the quotes
$quoted = str_replace("\"", "\"\"", $item);
# Quote the string
echo "\"$quoted\"";
}
echo "\n";
}
?>
Преимущество этого метода заключается в том, что он не имеет проблем с varchar и текстовыми полями, в которых есть текст, содержащий новые строки. Эти поля правильно цитируются, и эти новые строки в них будут интерпретироваться читателем CSV как часть текста, а не разделители записей. Это то, что трудно исправить после sed или так.
Что сработало для меня:
SELECT *
FROM students
WHERE foo = 'bar'
LIMIT 0,1200000
INTO OUTFILE './students-1200000.csv'
FIELDS TERMINATED BY ',' ESCAPED BY '"'
ENCLOSED BY '"'
LINES TERMINATED BY '\r\n';
Ни одно из решений в этом потоке не работало для моего конкретного случая, у меня были красивые данные json внутри одного из столбцов, которые могли быть испорчены в моем выводе csv. Для тех, у кого похожая проблема, попробуйте строки, оканчивающиеся на \r\n.
Еще одна проблема для тех, кто пытается открыть csv с помощью Microsoft Excel, имейте в виду, что существует ограничение в 32 767 символов, которое может содержать одна ячейка, при этом она переполняется до строк ниже. Чтобы определить, какие записи в столбце имеют проблему, используйте запрос ниже. Затем вы можете обрезать эти записи или обработать их, как хотите.
SELECT id,name,CHAR_LENGTH(json_student_description) AS 'character length'
FROM students
WHERE CHAR_LENGTH(json_student_description)>32767;
REPLACE()
в вашем запросе, чтобы экранировать кавычки.