是否有一种简单的方法可以从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

当有很多列需要用引号括起来时,或者如果结果中有需要转义的引号,就会变得混乱。


当前回答

从Save MySQL查询结果到一个文本或CSV文件:

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

注意:该语法可能需要重新排序

SELECT order_id,product_name,qty
INTO OUTFILE '/var/lib/mysql-files/orders.csv'
FIELDS TERMINATED BY ','
ENCLOSED BY '"'
LINES TERMINATED BY '\n'
FROM orders
WHERE foo = 'bar';

在MySQL的最新版本。

使用此命令,将不会导出列名。

还要注意/var/lib/ MySQL -files/orders.csv将在运行MySQL的服务器上。MySQL进程运行所在的用户必须具有写入所选目录的权限,否则该命令将失败。

如果您想从远程服务器(特别是托管或虚拟机,如Heroku或Amazon RDS)向本地机器写入输出,则此解决方案不适合。

其他回答

下面是在user7610基础上实现这一点的最佳方法。使用mysql outfile有60分钟的文件所有权和覆盖问题。

这并不酷,但它在5分钟内就起作用了。

PHP csvdump.php本地主机根密码数据库表名>

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

?>

到目前为止,除了MySQL Workbench之外,所有的解决方案都是不正确的,而且很可能对MySQL数据库中的某些内容不安全(即安全问题)。

MySQL Workbench(以及类似的phpMyAdmin)提供了一个正式的正确解决方案,但它们是为将输出下载到用户的位置而设计的。它们对于自动化数据导出之类的事情不是很有用。

这是不可能产生可靠的正确的CSV内容从mysql -B -e 'SELECT…'因为它不能编码回车符和字段中的空白。mysql的'-s'标志确实做反斜杠转义,并可能导致一个正确的解决方案。但是,使用脚本语言(具有良好的内部数据结构的语言,而不是Bash)和已经仔细解决编码问题的库要安全得多。

我想过为此写一个脚本,但当我想到我该怎么称呼它时,我突然想到要搜索同名的已有作品。虽然我还没有彻底讨论它,但mysql2csv看起来很有前途。不过,根据应用程序的不同,使用YAML方法指定SQL命令可能有吸引力,也可能没有吸引力。我对Ruby的最新版本的要求也不感兴趣,因为我的Ubuntu 12.04 (Precise穿山甲)笔记本电脑或Debian 6.0 (Squeeze)服务器都是标准版本。是的,我知道我可以使用RVM,但是我宁愿不为了这样一个简单的目的而维护RVM。

我是这么做的:

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。

从Save MySQL查询结果到一个文本或CSV文件:

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

注意:该语法可能需要重新排序

SELECT order_id,product_name,qty
INTO OUTFILE '/var/lib/mysql-files/orders.csv'
FIELDS TERMINATED BY ','
ENCLOSED BY '"'
LINES TERMINATED BY '\n'
FROM orders
WHERE foo = 'bar';

在MySQL的最新版本。

使用此命令,将不会导出列名。

还要注意/var/lib/ MySQL -files/orders.csv将在运行MySQL的服务器上。MySQL进程运行所在的用户必须具有写入所选目录的权限,否则该命令将失败。

如果您想从远程服务器(特别是托管或虚拟机,如Heroku或Amazon RDS)向本地机器写入输出,则此解决方案不适合。

为了扩展前面的答案,下面的一行代码将单个表导出为制表符分隔的文件。它适用于自动化,每天导出数据库。

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