有没有一种简单的方法可以从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
当有很多列需要用引号括起来,或者结果中有引号需要转义时,它会变得混乱.
来自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 --password=foo < my_requests.sql > out.csv
哪个是制表符分隔.像这样管道以获得真正的CSV(感谢@therefromhere):
... .sql | sed 's/\t/,/g' > out.csv
mysql --batch,-B
使用制表符作为列分隔符打印结果,每行都在新行上.使用此选项,mysql不使用历史记录文件.批处理模式导致非表格输出格式和特殊字符的转义.可以使用原始模式禁用转义; 请参阅--raw选项的说明.
这将为您提供一个制表符分隔文件.由于逗号(或包含逗号的字符串)未进行转义,因此将分隔符更改为逗号并不简单.
这是一种相当粗糙的方式.发现它在某个地方,不能拿任何功劳
mysql --user=wibble --password wobble -B -e "select * from vehicle_categories;" | sed "s/'/\'/;s/\t/\",\"/g;s/^/\"/;s/$/\"/;s/\n//g" > vehicle_categories.csv
效果很好.再一次,虽然正则表达式证明只写.
正则表达式说明:
s ///表示替换第一个//与第二个//之间的内容//
最后的"g"是一个修饰符,意思是"所有实例,而不仅仅是第一个"
^(在此上下文中)表示行的开头
$(在此上下文中)表示行尾
所以,把它们放在一起:
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,通过'tr'管道:
mysql-e " " | tr '\t' ',' > data.csv
注意:它既不处理嵌入式逗号,也不处理嵌入式选项卡.
这节省了我几次.快,它的工作原理!
- 批量
- 生的
例:
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
Paul Tomblin给出的OUTFILE解决方案会导致在MySQL服务器本身上写入文件,因此只有在您具有FILE访问权限以及登录访问权限或其他从该框中检索文件的方法时才能使用该文件.
如果您没有这样的访问权限,并且制表符分隔的输出是CSV的合理替代(例如,如果您的最终目标是导入到Excel),那么Serbaut的解决方案(使用mysql --batch和可选--raw)是可行的方法.
MySQL Workbench可以将记录集导出为CSV,它似乎可以很好地处理字段中的逗号.CSV在OpenOffice中打开很好.
怎么样:
mysql your_database -p < my_requests.sql | awk '{print $1","$2}' > out.csv
迄今为止,除Mysql工作台之外的所有解决方案都是错误的,并且对于MySQL DB中的至少一些可能的内容来说可能是不安全的(即安全问题).
Mysql Workbench(以及类似的PHPMyAdmin)提供了一个正式正确的解决方案,但设计用于将输出下载到用户的位置.它们对于自动化数据导出等问题并不那么有用.
无法从输出中生成可靠的正确csv,mysql -B -e 'SELECT ...'因为它无法对字段中的回车符和空格进行编码.MySQL的'-s'标志确实会执行反斜杠转义,并可能导致正确的解决方案.但是,使用脚本语言(一个具有良好的内部数据结构,而不是bash),以及编码问题已经仔细制定的库,更加安全.
我想为此写一个脚本,但是当我想到我称之为什么时,我就想到了用同一个名字搜索已有的作品.虽然我没有彻底解决它,但解决方案看起来很有希望.根据您的应用程序,指定SQL命令的yaml方法可能会或可能不会吸引人.我对于更新版本的ruby的要求并不像我的Ubuntu 12.04笔记本电脑或Debian Squeeze服务器那样标准.是的我知道我可以使用RVM,但我不想为了这么简单的目的而维护它.
希望有人会指出一个合适的工具,这需要进行一些测试.否则,当我找到或写一个时,我可能会更新它.
从命令行,您可以执行以下操作:
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文件
此页面上的许多答案都很薄弱,因为它们无法处理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输入格式的极端情况.
这可以通过流方法来改进以处理非常大的文件.
逻辑:
CREATE TABLE () (SELECT data FROM other_table ) ENGINE=CSV ;
创建CSV表时,服务器会在数据库目录中创建表格式文件.该文件以表名开头,扩展名为.frm.存储引擎还会创建一个数据文件.它的名称以表名开头,并具有.CSV扩展名.数据文件是纯文本文件.将数据存储到表中时,存储引擎会以逗号分隔值格式将其保存到数据文件中.
这个答案使用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 is not much better, but this is an example
# /sf/ask/17360801/
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)
除上述答案外,您还可以拥有一个使用CSV引擎的MySQL表.
然后,您的硬盘上将有一个文件,该文件将始终采用CSV格式,您可以在不进行处理的情况下进行复制.
这很简单,它适用于任何不需要批处理模式或输出文件的东西:
select concat_ws(',',
concat('"', replace(field1, '"', '""'), '"'),
concat('"', replace(field2, '"', '""'), '"'),
concat('"', replace(field3, '"', '""'), '"'))
from your_table where etc;
说明:
在每个字段中替换"with"" - > replace(field1,'"','""')
用引号括起每个结果 - > concat('"',result1,'"')
在每个引用的结果之间放置一个逗号 - > concat_ws(',',quoted1,quoted2,...)
而已!
要扩展以前的答案,以下单行将单个表导出为制表符分隔文件.它适用于自动化,每天导出数据库.
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
此外,如果您在Bash命令行上执行查询,我相信该tr命令可用于将默认选项卡替换为任意分隔符.
$ echo "SELECT * FROM Table123" | mysql Database456 | tr "\t" ,
在user7610的基础上,这是最好的方法.随着mysql outfile有文件的所有权和覆盖问题的60分钟.
它并不酷,但它可以在5分钟内完成.
php csvdump.php localhost root password database tablename > whatever-you-like.csv
name; } fputcsv($output, $fields); // loop over the rows, outputting them while ($row = mysql_fetch_assoc($rows)) fputcsv($output, $row); ?>
这是我做的:
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方面做得很好.
不完全是CSV格式,但MySQL 客户端的tee命令可用于将输出保存到本地文件中:
tee foobar.txt SELECT foo FROM bar;
你可以使用它来禁用它notee.
问题SELECT … INTO OUTFILE …;是它需要在服务器上写文件的权限.
京公网安备 11010802040832号 | 京ICP备19059560号-6 