outfile - mysql匯出csv語法




如何以CSV格式輸出MySQL查詢結果? (17)

mysql --batch,-B

使用選項卡作為列分隔符打印結果,每行放在一個新行中。 使用這個選項,mysql不會使用歷史文件。 批處理模式導致非表格輸出格式和轉義特殊字符。 通過使用原始模式可能會禁用轉義; 請參閱--raw選項的說明。

這會給你一個製表符分隔的文件。 由於逗號(或包含逗號的字符串)不會被轉義,因此將分隔符更改為逗號並不簡單。

有沒有簡單的方法來從Linux命令行運行MySQL查詢並以CSV格式輸出結果?

這就是我現在正在做的事情:

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

當需要用引號包圍很多列時,或者結果中有需要轉義的引號時,它會變得雜亂無章。


  1. 邏輯:

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

當您創建CSV表時,服務器會在數據庫目錄中創建一個表格格式文件。 該文件以表名開頭並具有.frm擴展名。 存儲引擎還會創建一個數據文件。 它的名字以表名開頭,擴展名為.CSV。 數據文件是純文本文件。 將數據存儲到表中時,存儲引擎會以逗號分隔值格式將其保存到數據文件中。


不完全是CSV格式,但可以使用來自MySQL 客戶端的 tee命令將輸出保存到本地文件中:

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' [email protected]

perl腳本(從其他地方狙擊)可以很好地將製表符間隔字段轉換為CSV。


只有Unix / Cygwin ,通過'tr'管道:

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

注意:這不處理嵌入的逗號,也不嵌入標籤。


在你的命令行中,你可以這樣做:

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文件


建立在用戶7610上,這是做到這一點的最佳方式。 用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);

?>

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 -p < my_requests.sql | awk '{print $1","$2}' > out.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輸入格式的角落案例。

這可以通過流式處理來改善以處理非常大的文件。


為了擴大以前的答案,以下單行程序將單個表格導出為製表符分隔文件。 它適用於自動化,每天導出數據庫等等。

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    

由Paul Tomblin給出的OUTFILE解決方案導致文件被寫入MySQL服務器本身,所以只有當您擁有FILE訪問FILE ,以及登錄訪問權限或其他方式從該文件夾中檢索文件時,該文件才會起作用。

如果您沒有這種訪問權限,並且製表符分隔的輸出是CSV的合理替代(例如,如果您的最終目標是導入到Excel中),那麼Serbaut的解決方案(使用mysql --batch和可選的--raw )是要走的路。


這個答案使用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 isn't much better, but this is an example
# http://.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://.com/a/17725590/2958070 about lineterminator
    csv_writer = csv.writer(csvfile, lineterminator='\n')
    csv_writer.writerows(results)

這很簡單,它適用於任何不需要批處理模式或輸出文件的任何內容:

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

from your_table where etc;

說明:

  1. 在每個字段中替換“”替換“(field1,''',''''')
  2. 將每個結果用引號括起來 - > concat(''',result1,''')
  3. 在每個引用結果之間放置一個逗號 - > concat_ws(',',quoted1,quoted2,...)

而已!


這節省了我幾次。 快速,它的作品!

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

除上述答案之外,還可以使用使用CSV引擎的MySQL表。

然後,您將在硬盤上擁有一個始終採用CSV格式的文件,您可以在不處理該文件的情況下進行複制。


Try this code:
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';

for more: http://dev.mysql.com/doc/refman/5.1/en/select-into.html




quotes