Bagaimana output hasil query MySQL dalam format CSV?

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

Menggunakan perintah ini kolom nama tidak akan diekspor.

Juga perhatikan bahwa /var/lib/mysql-files/order.csv akan di server yang menjalankan MySQL. Pengguna bahwa MySQL adalah proses yang berjalan di bawah harus memiliki izin untuk menulis ke direktori yang dipilih, atau perintah akan gagal.

Jika anda ingin menulis output untuk mesin lokal anda dari remote server (terutama host atau virtualisasi mesin seperti Heroku atau Amazon RDS), solusi ini tidak cocok.

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

Yang tab terpisah. Pipa seperti itu untuk mendapatkan yang benar CSV (terima kasih @therefromhere):

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

mysql --batch, -B

hasil Cetak menggunakan tab sebagai kolom pemisah, dengan masing-masing baris pada baris baru. Dengan opsi ini, mysql tidak menggunakan file history. modus Batch hasil non-tabel output format dan melarikan diri dari karakter khusus. Melarikan diri dapat dinonaktifkan dengan menggunakan mode raw; lihat deskripsi untuk --baku pilihan.

Ini akan memberikan anda sebuah tab file dipisahkan. Sejak koma (atau string yang mengandung koma) yang tidak lolos itu tidak mudah untuk mengubah delimiter koma.

Berikut ini's cukup degil cara untuk melakukannya. Ternyata di suatu tempat, dapat't mengambil kredit apapun

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

Bekerja dengan cukup baik. Sekali lagi meskipun regex membuktikan menulis saja.

Regex Penjelasan:

• s/// berarti pengganti apa's antara yang pertama // dengan apa yang's antara kedua //
• "g" pada akhir adalah pengubah yang berarti "semua contoh, tidak hanya pertama"
• ^ (dalam konteks ini) berarti awal baris
• $ (dalam konteks ini) berarti akhir baris

Jadi, menempatkan itu semua bersama-sama:

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 hanya, pipa melalui 'tr':

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

N. B.: Ini menangani tidak tertanam koma, atau tertanam tab.

Ini menyelamatkan saya beberapa kali. Cepat dan bekerja!

--batch hasil Cetak menggunakan tab sebagai kolom pemisah, dengan masing-masing baris pada baris baru.

--raw jika karakter melarikan diri (\n, \t, \0,\)

Contoh:

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

Untuk kelengkapan anda bisa mengkonversi ke csv (tapi hati-hati karena tab bisa dalam bidang nilai - nilai misalnya bidang teks)

tr &#39;\t&#39; &#39;,&#39; < file.tsv > file.csv

OUTFILE solusi yang diberikan oleh Paulus Tomblin menyebabkan file yang akan ditulis pada MySQL server itu sendiri, sehingga ini akan bekerja hanya jika anda memiliki FILE akses, serta akses login atau cara lain untuk mengambil file dari kotak itu.

Jika anda don't memiliki akses tersebut, dan tab-delimited output adalah pengganti yang wajar untuk CSV (misalnya, jika tujuan akhir anda adalah untuk impor ke Excel), maka Serbaut's solusi (menggunakan mysql --batch dan opsional --mentah) adalah cara untuk pergi.

MySQL Workbench dapat mengekspor recordsets ke CSV, dan tampaknya untuk menangani koma di bidang-bidang yang sangat baik. CSV membuka di OpenOffice baik-baik saja.

Bagaimana tentang:

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

Semua solusi di sini untuk saat ini, kecuali MySQL workbench satu, adalah tidak benar dan sangat mungkin tidak aman (yaitu masalah keamanan) untuk setidaknya beberapa konten mungkin di db mysql.

MYSQL Workbench (dan demikian pula PHPMyAdmin) memberikan secara resmi solusi yang tepat, tetapi dirancang untuk men-download keluaran untuk pengguna's lokasi. Mereka're tidak begitu berguna untuk hal-hal seperti mengotomatisasi data ekspor.

Hal ini tidak mungkin untuk menghasilkan andal benar csv dari output mysql -B -e 'PILIH ...' karena itu tidak dapat menyandikan kereta kembali dan putih ruang di bidang. The ' s' bendera ke mysql tidak melakukan backslash melarikan diri, dan mungkin menyebabkan solusi yang tepat. Namun, dengan menggunakan bahasa scripting (satu dengan layak struktur data internal yang tidak bash), dan perpustakaan di mana masalah pengkodean telah hati-hati bekerja keluar jauh lebih aman.

Saya berpikir tentang menulis script untuk ini, tetapi segera setelah saya berpikir tentang apa yang saya'd sebut itu, terpikir olehku untuk mencari karya yang sudah ada dengan nama yang sama. Sementara aku belum't pergi lebih dari itu secara menyeluruh, solusi di https://github.com/robmiller/mysql2csv tampak menjanjikan. Tergantung pada aplikasi anda, yaml pendekatan untuk menentukan perintah-perintah SQL yang mungkin atau mungkin tidak menarik meskipun. I'm juga tidak senang dengan persyaratan untuk versi yang lebih baru dari ruby dari datang sebagai standar dengan saya Ubuntu 12.04 laptop atau server Debian Squeeze. Ya aku tahu aku bisa menggunakan RVM, tapi aku'd agak tidak mempertahankan bahwa untuk suatu tujuan sederhana.

Mudah-mudahan seseorang akan menunjukkan sebuah alat yang cocok, yang's memiliki sedikit pengujian. Kalau tidak, aku'll mungkin update ini ketika aku menemukan atau menulis satu.

Dari baris perintah anda, anda dapat melakukan ini:

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*

Kredit: https://stackoverflow.com/questions/9536224/exporting-table-from-amazon-rds-into-a-csv-file

Banyak jawaban pada halaman ini adalah lemah karena mereka don't menangani kasus umum dari apa yang dapat terjadi dalam format CSV. misalnya koma dan kutipan tertanam di ladang dan kondisi lain yang selalu datang pada akhirnya. Kita perlu solusi umum yang bekerja untuk semua berlaku CSV input data.

Berikut ini's sederhana dan kuat solusi dalam 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)

Nama file tab2csv, meletakkannya di jalan anda, memberikan izin mengeksekusi, kemudian menggunakannya seperti ini:

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

Python CSV-fungsi penanganan menutupi kasus sudut untuk CSV format input(s).

Ini bisa ditingkatkan untuk menangani file yang sangat besar melalui streaming pendekatan.

1. logika :

MEMBUAT TABEL () (PILIH data DARI other_table ) ENGINE=CSV ;

Ketika anda membuat tabel CSV, server menciptakan format tabel dalam file direktori database. File yang dimulai dengan nama tabel dan memiliki .frm ekstensi. Mesin penyimpanan juga menciptakan sebuah file data. Namanya dimulai dengan nama tabel dan memiliki .Ekstensi CSV. Data file file teks biasa. Ketika anda menyimpan data ke dalam tabel, penyimpanan mesin menyimpannya ke dalam data file comma-separated values format.

Jawaban ini menggunakan Python dan tempat perpustakaan pihak ketiga, PyMySQL. I'm menambahkan ini karena Python's csv perpustakaan adalah cukup kuat untuk benar menangani banyak rasa yang berbeda dari .csv dan tidak ada jawaban lain yang menggunakan kode Python untuk berinteraksi dengan database.

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)

Ini adalah sederhana, dan bekerja pada apa pun tanpa perlu modus batch atau file output:

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

from your_table where etc;

Penjelasan:

1. Ganti " dengan "," di bidang masing-masing --> menggantikan(field1, ',"', '"",')
2. Mengelilingi setiap hasil yang di kutip --> concat('",', result1, '",')
3. Tempat koma di antara setiap dikutip hasilnya --> concat_ws(',', quoted1, quoted2, ...)

Yang's itu!

Atau untuk jawaban di atas, anda dapat memiliki sebuah tabel MySQL yang menggunakan CSV mesin.

Maka anda akan memiliki sebuah file pada hard disk yang akan selalu berada dalam format CSV yang anda hanya bisa copy tanpa pengolahan.

Untuk memperluas pada jawaban sebelumnya, berikut satu-kapal ekspor satu tabel yang dipisahkan tab file. It's cocok untuk otomatisasi, mengekspor database setiap hari atau lebih.

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

Mudah, kita dapat menggunakan teknik yang sama untuk daftar keluar MySQL's tabel, dan untuk menggambarkan kolom pada satu tabel:

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    

Juga, jika anda'kembali melakukan query pada Bash command line, saya percaya tr perintah ini dapat digunakan untuk pengganti default tab untuk sewenang-wenang pembatas.

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

Bangunan di user7610, berikut adalah cara terbaik untuk melakukannya. Dengan mysql outfile ada 60 menit dari kepemilikan file dan timpa masalah.

It's tidak keren, tapi itu bekerja dalam 5 menit.

php csvdump.php localhost password root database tablename > apa pun yang anda suka.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);

?>

Tidak persis seperti format CSV, tapi tee command dari MySQL klien dapat digunakan untuk menyimpan output ke a lokal file:

tee foobar.txt
SELECT foo FROM bar;

Anda dapat menonaktifkannya dengan menggunakan notee.

Masalah dengan PILIH ... KE OUTFILE ...; adalah bahwa hal itu membutuhkan izin untuk menulis file-file di server.