Как вывести результаты запроса 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

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

10.12.2008 15:59:51
Вы можете использовать REPLACE()в своем запросе экранирование кавычек.
dsm 10.12.2008 16:08:01
Посмотрите на мой ответ в [этот stackoverflow] [1] [1]: stackoverflow.com/questions/12242772/…
biniam 1.04.2015 16:14:33
Принятый ответ на этот вопрос stackoverflow, вероятно, является наилучшим способом: stackoverflow.com/questions/3760631/mysql-delimiter-question
Samuel Åslund 22.01.2016 16:28:15
Я написал запрос на функцию отслеживания ошибок MariaDB ( jira.mariadb.org/browse/MDEV-12879 ). Вы можете проголосовать за него.
Jared Beck 23.05.2017 18:27:32
30 ОТВЕТОВ
РЕШЕНИЕ

С 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), это решение не подходит.

1743
12.10.2017 19:34:45
@ Томас, если у вас есть доступ к удаленной файловой системе и MySQL, вы должны иметь возможность писать куда-нибудь. вместо / tmp попробуйте /home/yourusername/file.csv - если это не удастся, а набор результатов не так велик, вы можете скопировать вывод из вашего SSH-клиента и вставить на локальный компьютер.
Michael Butler 9.03.2012 15:40:10
Вопрос задан MySQL, а не "соответствует стандартам".
Paul Tomblin 11.07.2012 13:28:05
Как включить заголовок?
Bogdan Gusiev 25.01.2013 10:01:04
@BogdanGusiev, вы можете включить заголовок, добавив "SELECT 'order_id'," product_name "," qty 'UNION "перед реальным запросом. Первый запрос select возвращает заголовок, второй запрос возвращает реальные данные; СОЮЗ объединяет это.
petrkotek 14.06.2013 04:35:50
@ Майкл Батлер: Нет, вообще-то. Если вы используете Amazon RDS, Heroku или любое другое решение для хостинга, вы вряд ли сможете просто писать на чужой сервер.
Ken Kinder 26.06.2013 15:12:59

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

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

10
10.12.2008 23:34:58
Каковы ограничения этого с точки зрения размера файла / записи / чтения / и т. Д.?
Zach Smith 10.10.2018 08:21:28

mysql --batch, -B

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

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

202
30.09.2009 10:51:27
это предпочтительное решение: 1) списки значений, разделенных табуляцией, распространены в UNIX 2) импорт TSV изначально поддерживается большинством систем импорта, включая Excel, таблицы OpenOffice и т. д. 3) нет необходимости экранировать символы кавычек для текстовых полей 4 ) облегчает экспорт из командной строки
Joey T 11.12.2012 01:18:48
это лучшее решение, потому что, в отличие от первого, не нужно иметь разрешения на таких серверах, как RDS
Muayyad Alsadi 13.02.2013 10:31:17
Полезный трюк: если вы сохраните файл с разделителями табуляции как .xls вместо .csv, он откроется в Excel без необходимости преобразования текста в данные и без каких-либо проблем с региональными настройками.
serbaut 11.04.2013 21:05:01
@Joey T - вам все еще нужно избегать вкладок и возврата каретки. Также, если содержимое поля выглядит как поле в кавычках или как экранированное, импортированный контент может не выглядеть как оригинал.
mc0e 10.10.2013 08:22:48
у вас будут проблемы с NULL .. они будут выглядеть как строковые нули ..
Jonathan 10.10.2018 10:18:26
$ mysql your_database --password=foo < my_requests.sql > out.csv

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

... .sql | sed 's/\t/,/g' > out.csv
450
17.10.2012 17:05:59
не только это, но и позволяет создавать контент с удаленного хоста базы данных, в отличие от записи в локальную файловую систему.
tmarthal 8.07.2011 02:38:07
Это табуляция, а не запятая.
Flimm 30.08.2011 15:13:01
@Flimm, при условии, что у вас нет встроенных запятых / табуляций в полях, которые вы можете преобразовать, отправив результат в| sed 's/\t/,/g'
John Carter 10.11.2011 04:42:28
sed 'fix' не компенсирует запятые, которые могут появляться в любых из выбранных данных, и будет искажать выводимые столбцы соответствующим образом
Joey T 11.12.2012 01:17:35
Негатив действителен ... он может сработать для вас сейчас, но вполне может укусить вас в будущем, когда ваши данные будут содержать табуляцию или запятую и т. Д.
John Hunt 30.04.2014 12:44:40

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

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

39
22.06.2010 13:48:00

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

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 /// означает заменить то, что находится между первым // на то, что между вторым //
  • «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
137
3.05.2012 17:13:53
Флаг -e был именно тем, что я искал! Спасибо!
Gaurav Gupta 3.01.2012 08:46:19
Это регулярное выражение довольно просто; как и многие другие ответы на этой странице, он просто очищает вывод от mysql -B. Если вы разделите регулярное выражение на отдельные операторы в отдельных строках, это будет довольно просто (для тех, кто знает регулярные выражения) понять.
Mei 9.03.2012 00:58:33
Работает отлично! Важно сказать, что заголовки уже включены (никаких дополнительных шагов не требуется).
lepe 16.05.2013 08:00:04
На первый взгляд это выглядит довольно разбито. Вкладки и возврат каретки в содержании будут неправильно обработаны. "s / '/ \' /;" вообще ничего не делает, потому что двойные кавычки в команде shell используют обратную косую черту. Многие другие подобные ошибки с обратной косой чертой были потеряны. Нет обработки обратной косой черты в поле БД.
mc0e 10.10.2013 08:26:24
Это сломается, если у вас есть текстовое поле, которое содержит вкладки, обратную косую черту ","и ряд других вещей.
aidan 14.04.2014 03:59:09

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

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

Скрипт perl (снятый откуда-то) отлично работает для преобразования полей с табуляцией в CSV.

5
9.03.2012 01:01:32
Это замечательно. Небольшое улучшение может заключаться в том, чтобы цитировать все, кроме цифр perl -F"\t" -lane 'print join ",", map {s/"/""/g; /^\d+(?:\.\d+)?$/ ? $_ : qq("$_")} @F '- ваши не цитируют1.2.3
artfulrobot 15.03.2017 16:00:40

Как насчет:

mysql your_database -p < my_requests.sql | awk '{print $1","$2}' > out.csv
33
13.05.2013 11:36:53
Мне очень нравится этот. Это намного чище, и мне нравится использование awk. Тем не менее, я бы, наверное, пошел с этим: mysql -uUser -pPassword your_database < my_requests.sql | awk 'BEGIN{OFS="=";} {print $1,$2}' > out.csv
Josh 11.04.2012 00:00:04
Это не работает для значений полей с пробелами.
Barun Sharma 10.04.2015 06:17:39

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

select concat_ws(',',
    concat('"', replace(field1, '"', '""'), '"'),
    concat('"', replace(field2, '"', '""'), '"'),
    concat('"', replace(field3, '"', '""'), '"'))

from your_table where etc;

Объяснение:

  1. Заменить "с ""в каждой области ->replace(field1, '"', '""')
  2. Окружите каждый результат кавычками -> concat('"', result1, '"')
  3. Поместите запятую между каждым цитируемым результатом -> concat_ws(',', quoted1, quoted2, ...)

Это оно!

11
12.06.2019 21:22:58
Обратите внимание, что NULLзначения будут пропущены concat_ws(), что приведет к несоответствию столбца. Чтобы избежать этого, просто используйте вместо этого пустую строку: IFNULL(field, '')(или все, что вы используете для представления NULL)
Marco Roy 21.05.2019 23:11:32

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

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

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

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

Затем вы можете написать свой скрипт экспорта как любой обычный скрипт 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 и text, в которых есть текст, содержащий символы новой строки. Эти поля правильно указаны, и эти символы новой строки в них будут интерпретироваться читателем CSV как часть текста, а не разделители записей. Это то, что потом трудно исправить с помощью sed или около того.

0
2.11.2015 12:09:04
Это далеко не излишне сложное дело, но это единственное решение, которое идет в направлении, которое, вероятно, будет правильным, хотя и с небольшой работой. CSV сложнее, чем кажется на первый взгляд, и вы допустили множество ошибок. например, обратная косая черта в оригинале. Лучше использовать библиотеку, которая проработала все вопросы для записи в CSV.
mc0e 10.10.2013 08:57:33
@ mc0e Обычно вы либо удваиваете цитату, либо избегаете ее. Я решил удвоить цитату, поэтому мне не нужен escape-символ. Разное программное обеспечение имеет разные представления о деталях CSV. Например, LOAD DATA в MySQL действительно обрабатывает \ как escape-символ, тогда как Open Office Calc - нет. Когда я писал ответ, я экспортировал данные в электронную таблицу.
user7610 2.11.2015 12:51:41
Мой код обрабатывает все, что было необходимо для экспорта моего набора данных в тот день;) Ваш ответ также является шагом в правильном направлении, но, как говорится в первом комментарии, он должен 1) подключиться к базе данных SQL из сценария напрямую, как а также 2) использовать правильную библиотеку CSV.
user7610 22.10.2018 09:04:25

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

36
28.08.2018 15:10:56
Спасибо миллион Дэвид. Потратив 3 часа на то, чтобы новые строки правильно выводили содержимое HTML в данных, я использовал MySQL Workbench, и через 2 минуты у меня был готов файл CSV.
mr-euro 11.07.2012 17:02:49
Я только что обнаружил, что его тоже можно сохранить как XML, и это здорово. Я надеюсь перейти с одного приложения на другое с помощью XSLT для преобразования этого XML-файла в файл CSV, подходящий для импорта в целевое приложение.
David Oliver 5.08.2012 21:27:56
MySQL верстак является лучшим вариантом для функции импорта и экспорта.
cijagani 5.08.2015 03:20:03
Я только что успешно экспортировал более полумиллиона строк, используя инструментальные средства mysql, поэтому большие файлы не кажутся проблемой. Вам просто нужно убедиться, что вы удалили ограничение на выбор перед выполнением запроса, и вам также может потребоваться увеличить следующие значения в вашем файле my.ini: max_allowed_packet = 500M, net_read_timeout = 600, net_write_timeout = 600
Vincent 22.04.2018 15:47:17
MySQL Workbench имеет неловкую утечку памяти при экспорте данных в CSV, если у вас большой набор данных, например, 1 или 2 миллиона строк, 12 ГБ ОЗУ (протестировано на моей машине с Linux) недостаточно, и память не очищается, если вы убить или остановить это. Для большого набора данных это не решение.
Ostico 16.05.2019 15:00:53

Только для Unix / Cygwin , передайте через 'tr':

mysql <database> -e "<query here>" | tr '\t' ',' > data.csv

Примечание: это не обрабатывает ни встроенные запятые, ни вкладки.

90
2.11.2015 12:09:47

Используя решение, опубликованное Тимом, я создал этот сценарий 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

3
16.05.2013 08:04:06

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

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

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

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

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

28
4.03.2019 00:34:29
Вы правы, для сложных строк в таблице вы должны использовать приличную библиотеку, а не просто bash. Я думаю, что у nodejs есть лучшие решения для такого рода действий. как этот пример
yeya 28.07.2016 15:28:51
Привет, хороший ответ, я хотел бы добавить ссылку на решение Python, предложенное ниже stackoverflow.com/a/35123787/1504300 , которое работает хорошо и очень просто. Я пытался отредактировать ваше сообщение, но редактирование было отклонено
reallynice 2.06.2017 12:44:22
  1. логика:

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

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

16
7.05.2014 08:41:04
Приятно, так как это правильно без каких-либо дополнительных настроек.
Andrew Schulman 3.01.2018 21:53:29

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

tee foobar.txt
SELECT foo FROM bar;

Вы можете отключить его, используя notee.

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

5
29.05.2014 18:14:01
Если вместо .txt используется расширение .csv, есть ли какие-либо проблемы с форматированием, о которых следует знать?
datalifenyc 8.05.2018 21:53:56
@myidealab Проблемы с форматированием возникают из-за запятых и т. д., которые не экранируются. CSV - это простой текстовый формат, поэтому не возникает проблем с форматированием, если просто поменять расширение.
jkmartindale 20.06.2019 17:24:04

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

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

0
1.05.2018 07:39:58
Считайте так же, если я запускаю следующий запрос, я получаю ошибку. Мой запрос: justpaste.it/2fp6f Любая помощь, спасибо. @Indrajeet Singh
zus 19.02.2020 05:34:03

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

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    
8
23.10.2014 16:09:02
Чтобы преобразовать в CSV:mysql -B -D mydatabase -e 'select * from mytable' | sed -e 's/\t/,/g'
DSimon 12.01.2015 21:15:04
Использование sed -e 's/\t/,/g'безопасно только в том случае, если вы уверены, что ваши данные не содержат запятых или вкладок.
awatts 20.10.2015 16:23:18

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

Это не круто, но это сработало за 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);

?>
7
29.05.2015 18:36:18
Красиво, чисто и быстро работает - это отличное решение, если вы можете запустить PHP в этой среде!
John Fiala 5.04.2018 22:01:55

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

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

24
23.05.2017 12:18:20
это один сумасшедший вкладчик. hatsoff
Elias Escalante 7.11.2017 02:47:56

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

--batch Печать результатов с использованием табуляции в качестве разделителя столбцов, с каждой строкой в ​​новой строке.

--raw отключает экранирование символов (\ n, \ t, \ 0 и \)

Пример:

mysql -udemo_user -p -h127.0.0.1 --port=3306 \
   --default-character-set=utf8mb4 --database=demo_database \
   --batch --raw < /tmp/demo_sql_query.sql > /tmp/demo_csv_export.tsv

Для полноты вы можете преобразовать в CSV (но будьте осторожны, потому что вкладки могут быть внутри значений полей - например, текстовые поля)

tr '\t' ',' < file.tsv > file.csv

53
16.04.2019 10:31:32
Если я чего-то не пропустил, это создает файл TSV, а не файл CSV ...
PressingOnAlways 12.07.2016 00:45:53
@PressingOnAlways Да. Цитата документа MySQL: «Печать результатов с использованием табуляции в качестве разделителя столбцов, с каждой строкой в ​​новой строке». Но разбор не должен быть проблемой с любым разделителем. Я использовал его для создания файлов Excel для моего клиента.
hrvoj3e 12.07.2016 06:50:49

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

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

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

26
12.06.2019 21:26:37
Еще более надежным решением было бы на самом деле соединиться с базой данных с помощью Python, тогда вам будет проще делать то, что вам нужно, для работы с большими наборами данных (чанкованием результатов, потоковой передачей и т. Д.).
Josh Rumbut 4.02.2016 15:52:50
@JoshRumbut, очень поздно, но я сделал stackoverflow.com/a/41840534/2958070, чтобы дополнить ваш комментарий
Ben 30.08.2019 16:28:55

В этом ответе используется 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 is not much better, but this is an example
# https://stackoverflow.com/questions/12461484
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)
13
12.06.2019 21:23:46
Ответ с использованием Python уже был предоставлен. stackoverflow.com/a/35123787/5470883
Alexander Baltasar 25.01.2017 09:39:07
@AlexanderBaltasar, верно, и это выглядит полезным, но он не использует код Python для взаимодействия с базой данных. Смотрите комментарий по этому вопросу.
Ben 26.01.2017 02:29:46

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

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

7
1.06.2018 11:59:28

Крошечный 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"
1
1.03.2018 12:23:23

Следующий скрипт 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
1
14.05.2018 15:19:27

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

Я пытался сохранить имена опций с 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

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

3
16.05.2018 11:31:57

Вы можете использовать следующую команду из вашего редактора SQL / терминала:

" mysql -h (имя хоста / IP>) -u (имя пользователя) -p (пароль) имя базы данных <(query.sql)> outputFILE (.txt / .xls) "

например, имя хоста -xxxx

uname - имя пользователя

пароль - пароль

DBName - employeeDB

queryFile - employee.sql

outputFile - outputFile.xls

mysql -hx.xxx -uusername -ppassword employeeDB <employee.sql> outputFile.xls

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

-2
21.12.2018 13:16:24

Что сработало для меня:

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;
3
7.11.2018 06:15:26

Следующее дает вывод с разделителями табуляции и действительный вывод CSV. В отличие от большинства других ответов, этот метод корректно обрабатывает экранирование вкладок, запятых, кавычек и новых строк без какого-либо потокового фильтра, такого как sed, awk или tr. В примере показано, как передать удаленную таблицу mysql непосредственно в локальную базу данных sqlite с помощью потоков. Это работает без разрешения FILE или разрешения SELECT INTO OUTFILE. Я добавил новые строки для удобства чтения.

mysql -B -C --raw -u 'username' --password='password' --host='hostname' 'databasename'
-e 'SELECT
    CONCAT('\''"'\'',REPLACE(`id`,'\''"'\'', '\''""'\''),'\''"'\'') AS '\''id'\'',
    CONCAT('\''"'\'',REPLACE(`value`,'\''"'\'', '\''""'\''),'\''"'\'') AS '\''value'\''
    FROM sampledata'
2>/dev/null | sqlite3 -csv -separator $'\t' mydb.db '.import /dev/stdin mycsvtable'

2>/dev/nullНеобходимо подавить предупреждение о пароле в командной строке.

Если ваши данные имеют значения NULL, вы можете использовать функцию IFNULL () в запросе.

0
17.07.2019 17:40:14