Jak wypisać wyniki zapytań MySQL w formacie CSV?

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

Który jest oddzielony tabulatorami. Rura to tak, aby uzyskać prawdziwy CSV (dzięki @ therefromhere):

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

Mysql --batch, - B

Wyświetla wyniki za pomocą tab jako separatora kolumn, z każdym wierszem na nowa linia. Dzięki tej opcji mysql nie używa pliku historii. Tryb wsadowy powoduje, że format wyjściowy nie jest tabelaryczny i znaki specjalne. Ucieczka może być wyłączona za pomocą trybu raw; zobacz opis opcji --raw.

To da Ci plik oddzielony tabulatorami. Ponieważ przecinki (lub łańcuchy zawierające przecinki) nie są unikalne, nie jest łatwo zmienić ogranicznik na przecinek.

Jest to dość paskudny sposób na zrobienie tego. Znalazłem to gdzieś, nie mogę wziąć żadnego kredytu

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

Wyjaśnienie Regex:

• S / / / oznacza zastąpienie tego, co jest między pierwszym / / tym, co jest między drugim / /
• "g" na końcu jest modyfikatorem, który oznacza "wszystkie instancje, nie tylko pierwsze"
• ^ (w tym kontekście) oznacza początek linii
• $ (w tym kontekście) oznacza koniec linii

Więc, składając to wszystko razem:

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 tylko, przepuść go przez 'tr':

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

Uwaga: nie obsługuje ani osadzonych przecinków, ani osadzonych tabulatorów.

Rozwiązanie OUTFILE podane przez Paula Tomblina powoduje, że plik zostanie zapisany na samym serwerze MySQL, więc będzie to działać tylko wtedy, gdy masz dostęp do pliku , a także dostęp do loginu lub inne sposoby pobierania pliku z tego pola.

Jeśli nie masz takiego dostępu, a rozdzielane tabulatorami wyjście jest rozsądnym substytutem pliku CSV( np. jeśli twoim celem końcowym jest import do Excela), rozwiązanie Serbauta (za pomocą mysql --batch i opcjonalnie --raw) jest dobrym rozwiązaniem.

To uratowało mnie kilka razy. Szybko i działa!

--Nr serii

--raw

Przykład:

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

A może:

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

MySQL Workbench potrafi eksportować zestawy rekordów do pliku CSV i zdaje się bardzo dobrze obsługiwać przecinki w polach. Plik CSV otwiera się w OpenOffice.

Wszystkie dotychczasowe rozwiązania, z wyjątkiem Mysql workbench, są nieprawidłowe i prawdopodobnie niebezpieczne (czyli problemy z bezpieczeństwem) dla przynajmniej niektórych możliwych treści w mysql db.

Mysql Workbench (i podobnie PHPMyAdmin) zapewniają formalnie poprawne rozwiązanie, ale są przeznaczone do pobierania danych wyjściowych do lokalizacji użytkownika. Nie są tak przydatne do takich rzeczy jak automatyzacja eksportu danych.

Nie jest możliwe wygenerowanie wiarygodnie poprawnego pliku csv z wyjścia mysql -B -e 'SELECT ...' ponieważ nie może to zakodować zwrotów karetki i białych spacji w polach. Znacznik '- s' dla mysql nie powoduje ucieczki ukośnika wstecznego i może prowadzić do poprawnego rozwiązania. Jednak używanie języka skryptowego (takiego z przyzwoitymi wewnętrznymi strukturami danych, a nie bash) i bibliotek, w których problemy z kodowaniem zostały już starannie opracowane, jest o wiele bezpieczniejsze.

Myślałem o napisaniu scenariusza do tego, ale jak tylko pomyślałem o tym, jak to nazwę, przyszło mi do głowy, aby szukać wcześniej istniejące dzieło o tej samej nazwie. Chociaż nie przejrzałem tego dokładnie, rozwiązanie na https://github.com/robmiller/mysql2csv wygląda obiecująco. W zależności od aplikacji, podejście yaml do określania poleceń SQL może, ale nie może się podobać. Nie jestem również zachwycony wymogiem na nowszą wersję ruby niż jest Standardowo z moim laptopem Ubuntu 12.04 lub serwerami Squeeze Debiana. Tak Wiem, że mógłbym użyć RVM, ale wolałbym nie utrzymywać tego dla taki prosty cel.

Mam nadzieję, że ktoś wskaże odpowiednie narzędzie, które zostało trochę Przetestowane. W przeciwnym razie prawdopodobnie zaktualizuję to, gdy znajdę lub napiszę.

Z linii poleceń, możesz to zrobić:

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*

Podziękowania: eksport tabeli z Amazon RDS do pliku csv

1. logika:

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

Podczas tworzenia tabeli CSV serwer tworzy plik w formacie tabeli w katalog bazy danych. Plik zaczyna się od nazwy tabeli i ma .rozszerzenie frm. Silnik pamięci masowej tworzy również plik danych. Jego nazwa zaczyna się od nazwy tabeli i ma .Rozszerzenie CSV. Plik danych jest zwykły plik tekstowy. Podczas przechowywania danych w tabeli, magazyn silnik zapisuje go do pliku danych w wartościach oddzielonych przecinkami format.

Wiele odpowiedzi na tej stronie jest słabych, ponieważ nie obsługują ogólnego przypadku tego, co może wystąpić w formacie CSV. np. przecinki i cudzysłowy osadzone w polach i innych warunkach, które zawsze pojawiają się w końcu. Potrzebujemy ogólnego rozwiązania, które będzie działać dla wszystkich ważnych danych wejściowych CSV.

Oto proste i mocne rozwiązanie w Pythonie:

#!/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)

Nazwij ten plik tab2csv, umieść go na swojej ścieżce, nadaj mu uprawnienia wykonawcze, a następnie użyj listy:

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

Pyton Funkcje obsługi CSV obejmują narożniki dla formatów wejściowych CSV.

Można to poprawić, aby obsługiwać bardzo duże pliki za pomocą podejścia strumieniowego.

Aby rozwinąć poprzednie odpowiedzi, poniższy one-liner eksportuje pojedynczą tabelę jako plik oddzielony tabulatorami. Nadaje się do automatyzacji, eksportowania bazy danych każdego dnia.

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

Wygodnie, możemy użyć tej samej techniki do wypisania tabel MySQL i opisania pól w jednej tabeli:

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    

Jest to proste i działa na wszystkim bez potrzeby trybu wsadowego lub plików wyjściowych:

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

from your_table where etc;

Wyjaśnienie:

1. Zastąp " z "" w każdym polu -- > replace(field1, '"', '""')
2. otocz każdy wynik cudzysłowem --> concat ( '" ', result1,'"')
3. Umieść przecinek pomiędzy każdym cytowanym wynikiem --> concat_ws ( ' ,', quoted1, quoted2, ...)

Również, jeśli wykonujesz zapytanie w wierszu poleceń Bash, wierzę, że polecenie tr może być użyte do zastąpienia domyślnych tabulatorów dowolnymi ogranicznikami.

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

Alternatywnie do odpowiedzi powyżej, możesz mieć tabelę MySQL, która korzysta z silnika CSV.

Wtedy będziesz miał plik na dysku twardym, który zawsze będzie w formacie CSV, który możesz po prostu skopiować bez przetwarzania go.

Oto co robię:

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

Skrypt Perla (wycinany z innego miejsca) wykonuje niezłą robotę konwertując pola z tabulatorami do pliku CSV.

Nie do końca jako format CSV, ale tee polecenie z MySQL client może być użyte do zapisania wyjścia dolokalnego pliku :

tee foobar.txt
SELECT foo FROM bar;

Możesz go wyłączyć używając notee.

Problem z SELECT … INTO OUTFILE …; polega na tym, że wymaga uprawnień do zapisu plików na serwerze.

Bazując na user7610, oto najlepszy sposób, aby to zrobić. W przypadku {[1] } było 60 minut problemów z własnością i nadpisywaniem plików.

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);

?>

Ta odpowiedź używa Pythona i popularnej biblioteki stron trzecich, PyMySQL . Dodaję go, ponieważ Biblioteka csv Pythona jest wystarczająco potężna, aby poprawnie obsługiwać wiele różnych smaków .csv i żadne inne odpowiedzi nie używają kodu Pythona do interakcji z bazą danych.

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)

Korzystając z rozwiązania opublikowanego przez Tima, stworzyłem skrypt bash, aby ułatwić ten proces (wymagane jest hasło roota, ale możesz łatwo zmodyfikować skrypt, aby poprosić o innego użytkownika):

#!/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

Utworzy plik o nazwie: baza danych.stolik.csv

Wypróbuj ten kod:

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';

Więcej informacji: http://dev.mysql.com/doc/refman/5.1/en/select-into.html

Jeśli masz skonfigurowany PHP na serwerze, możesz użyć mysql2csv, aby wyeksportować (właściwie poprawny) plik CSV dla abitrycznego zapytania mysql. Zobacz moja odpowiedź w MySQL-wybierz * w OUTFILE LOCAL ? dla trochę więcej kontekstu / informacji.

Starałem się zachować nazwy opcji z mysql więc powinno wystarczyć podanie opcji --file i --query:

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

"Install" mysql2csv via

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

(pobierz zawartość gist, sprawdź sumę kontrolną i zrób to wykonywalny).

Jeśli NA używanym komputerze jest zainstalowany PHP, możesz napisać skrypt PHP, aby to zrobić. Wymaga instalacji PHP z zainstalowanym rozszerzeniem MySQL.

Możesz wywołać interpreter PHP z linii poleceń w następujący sposób:

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

Włączam przełącznik --php-ini, ponieważ może być konieczne użycie własnej konfiguracji PHP, która umożliwia rozszerzenie MySQL. W PHP 5.3.0 + to rozszerzenie jest domyślnie włączone, więc nie jest już konieczne używanie konfiguracji do włącz to.

Następnie możesz napisać swój skrypt eksportu jak każdy normalny skrypt 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";
    }
?>

Zaletą tej metody jest to, że nie ma ona problemów z polami varchar i text, które mają tekst zawierający znaki nowej linii. Pola te są poprawnie cytowane, a nowe wiersze w nich zawarte będą interpretowane przez czytnik CSV jako część tekstu, a nie separatory rekordów. To jest coś, co jest trudne do skorygowania później sed lub tak.

Mały skrypt bash do wykonywania prostych zapytań do zrzutów CSV, zainspirowany 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"

Następujący skrypt bash działa dla mnie. Opcjonalnie pobiera również schemat dla żądanych tabel.

#!/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

Możesz użyć poniższego polecenia ze swojej lokalizacji SQL:

"mysql-H (hostname / IP>) - u (username) - p(password) databasename outputFILE(.txt/xls)"

E. g Nazwa hosta-x.x.x.X

Uname-username

Password-Hasło

DBName-employeeDB

QueryFile-pracownik.sql

OutputFile-outputFile.xls

Mysql-hx.x.X. X-uusername-ppassword employeeDB outputFile.xls

Make na pewno wykonujesz polecenie z katalogu, w którym znajduje się SQL, lub wspomnij pełną ścieżkę lokalizacji sql w powyższym poleceniu.