是否有一种简单的方法可以从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
当有很多列需要用引号括起来时,或者如果结果中有需要转义的引号,就会变得混乱。
是否有一种简单的方法可以从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
当有很多列需要用引号括起来时,或者如果结果中有需要转义的引号,就会变得混乱。
当前回答
试试下面的代码:
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';
欲了解更多信息:http://dev.mysql.com/doc/refman/5.1/en/select-into.html
其他回答
到目前为止,除了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。
Use:
mysql your_database -p < my_requests.sql | awk '{print $1","$2}' > out.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和文本字段没有任何问题。这些字段被正确地引用,其中的换行符将被CSV阅读器解释为文本的一部分,而不是记录分隔符。这在sed之后是很难纠正的。
Paul Tomblin给出的OUTFILE解决方案导致一个文件被写入MySQL服务器本身,因此只有当您拥有file访问权限,以及登录访问权限或从该框检索文件的其他方法时,这才会起作用。
如果您没有这样的访问权限,并且以制表符分隔的输出是CSV的合理替代品(例如,如果您的最终目标是导入到Excel),那么serbaut的解决方案(使用mysql -batch和可选的-raw)就是要走的路。
为了扩展前面的答案,下面的一行代码将单个表导出为制表符分隔的文件。它适用于自动化,每天导出数据库。
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