将mysql查询结果转换为CSV(带有复制/粘贴)

I often work in command line mysql. A common need is to take a query's results and import them into a Numbers document (similar to an Excel document).

我经常在命令行mysql中工作。一个常见的需求是获取查询的结果并将其导入数字文档(类似于Excel文档)。

What is the fastest method for doing this?

最快的方法是什么?

Method 1: Select into outfile

You can select into an outfile directly from MySQL, but this takes several steps.

您可以直接从MySQL选择输出文件，但这需要几个步骤。

export your query with all the necessary arguments to make it a CSV format, like FIELDS OPTIONALY ENCLOSED BY and DELIMITED BY.
导出包含所有必要参数的查询，使其成为CSV格式，如由和分隔的字段OPTIONALY。
sftp into the server and grab the file
sftp进入服务器并抓取文件
delete the file from the server
从服务器删除文件

Method 2: Copy/paste

I tend to do this method. For me it seems a little faster but that's mostly because I don't remember how to construct the SELECT INTO OUTFILE query from above and have to look it up.

我倾向于用这种方法。对我来说，它似乎有点快，但这主要是因为我不记得如何从上面构造SELECT INTO OUTFILE查询，并且必须查找它。

Copy/paste to a local text file
复制/粘贴到本地文本文件
Open in a text editor and replace | with ,
在文本编辑器中打开并将|替换为，
Save as a CSV and open in Numbers.
保存为CSV并打开数字。

6 个解决方案

#1

How about this?:

这个怎么样?:

mysql -B -e "$MY_QUERY" > my_data.csv

mysql -B -e“$MY_QUERY”> my_data.csv

The output format is actually tab-separated rather than comma-separated but at least Excel and OpenOffice Calc automatically adapt to this.

输出格式实际上是表分隔的，而不是逗号分隔的，但至少Excel和OpenOffice Calc会自动适应这种格式。

BTW, for convenience and to enable non-interactive execution of mysql commands, I strongly recommend setting up a secure ~/.my.cnf file
(readable only by you) with entries like this:

顺便说一句，为了方便和支持非交互式地执行mysql命令，我强烈建议建立一个安全的~/.my.cnf文件(仅供您阅读)，包含如下条目:

[client]
user=YOUR_MYSQL_USER_NAME
password=YOUR_MYSQL_PASSWORD
host=YOUR_MYSQL_SERVER
port=YOUR_MYSQL_SERVER_PORT
WHATEVER_OTHER_OPTIONS_YOU_LIKE

References:

引用:

http://dev.mysql.com/doc/refman/5.1/en/mysql-command-options.html

--batch, -B

批,- b

Print results using tab as the column separator, with each row on a new line. With this option, mysql does not use the history file.

使用选项卡作为列分隔符打印结果，每一行都在新行上。使用此选项，mysql不使用历史文件。

Batch mode results in nontabular output format and escaping of special characters. Escaping may be disabled by using raw mode; see the description for the --raw option.

批处理模式导致非表格输出格式和特殊字符的转义。可以使用原始模式禁用转义;参见“原始选项”的描述。

Method 1: Select into outfile

Method 2: Copy/paste

6 个解决方案

#1

更多相关文章

随机推荐