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

9  

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

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.

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

7  

You can also use the mysql command line utility with the -e option and pipe the output to a file. This will output data in tab-delimited format.

您还可以使用mysql命令行工具和-e选项，并将输出传输到一个文件。这将以表分隔的格式输出数据。

2  

You can execute a SELECT ... INTO OUTFILE query on your local computer, and have it save the file locally. If you've got the mysql* command-line utility installed locally, simply add the -h flag to connect to a specific host. mysql can also read from standard input, so you can set up a .sql file that you'll use to import.

您可以执行SELECT…进入本地计算机上的外文件查询，并让它在本地保存文件。如果在本地安装了mysql*命令行实用程序，只需添加-h 标志，以连接到特定的主机。mysql还可以从标准输入读取数据，因此您可以设置一个用于导入的.sql文件。

SQL file (outfile.sql):

SQL文件(outfile.sql):

SELECT * FROM myTable WHERE date>CURDATE()
INTO OUTFILE '/home/me/outfile.csv' -- Specify output file (if Windows, make sure
                                    -- to use backslashes and escape them 
                                    -- (C:\\Users\\\\...)
FIELDS
    OPTIONALLY ENCLOSED BY '"'
    ESCAPED BY '"' -- Results in double quotation marks, which I have found Excel
                   -- requires (instead of using a backslash to escape)
    TERMINATED BY '\\n' -- or your line ending of choice
                        -- (i believe escaping is required)

Then you would issue the following command:

然后您将发出以下命令:

mysql -u  -p -h