mysql格式化输出 - phpmyadmin导出查询结果




如何以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 …; 是它需要权限才能在服务器上写入文件。


为了扩大以前的答案,以下单行程序将单个表格导出为制表符分隔文件。 它适用于自动化,每天导出数据库等等。

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    

以下是我的工作:

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。


使用Tim发布的解决方案,我创建了这个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的文件


在你的命令行中,你可以这样做:

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


如果您正在使用的计算机上安装了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或之后很难纠正的事情。


微小的bash脚本,可以通过https://.com/a/5395421/2841607进行简单的CSV查询转储查询。

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

怎么样:

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

此外,如果您在Bash命令行上执行查询,我相信tr命令可用于将默认选项卡替换为任意分隔符。

$ echo "SELECT * FROM Employee" | mysql Database | tr '\t' ,


由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