MySQL 转 csv,将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).

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.

export your query with all the necessary arguments to make it a CSV format, like FIELDS OPTIONALY ENCLOSED BY and DELIMITED BY.

sftp into the server and grab the file

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.

Copy/paste to a local text file

Open in a text editor and replace | with ,

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:

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