将mysql查询结果转换为CSV（使用复制/粘贴） [英] Convert mysql query results to CSV (with copy/paste)

问题描述

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

这是最快的方法是什么？

方法1：选择输入outfile

您可以直接从MySQL中选择一个outfile，但这需要几个步骤。

1. 查询所有必要的参数，使其成为CSV格式，例如 FIELDS OPTIONALY ENCLOSED BY 和 DELIMITED BY 。


2. sftp插入服务器并抓取文件


3. 从服务器删除文件

方法2：复制/粘贴

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

1. 复制/粘贴到本地文本文件


2. 在文本编辑器中打开，

解决方案 div>

这是怎么回事？：

mysql -b -e$ MY_QUERY> my_data.csv

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

BTW，为了方便起见并启用非交互执行
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 
  

---

参考文献：

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

- batch，-B

使用制表符作为列分隔符打印结果，线。使用此选项，mysql不使用历史记录文件。

批处理模式导致非字母输出格式和特殊字符转义。可以通过使用原始模式禁用转义;请参阅--raw选项的说明。

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).

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.

1. export your query with all the necessary arguments to make it a CSV format, like FIELDS OPTIONALY ENCLOSED BY and DELIMITED BY.
2. sftp into the server and grab the file
3. 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.

1. Copy/paste to a local text file
2. Open in a text editor and replace | with ,
3. Save as a CSV and open in Numbers.

解决方案

How about this?:

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.

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:

[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

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.

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.

这篇关于将mysql查询结果转换为CSV（使用复制/粘贴）的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！