如何将 MySQL 查询结果存储到本地 CSV 文件中？
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/2385812/
Warning: these are provided under cc-by-sa 4.0 license. You are free to use/share it, But you must attribute it to the original authors (not me):
StackOverFlow
How do I store a MySQL query result into a local CSV file?
提问by joe
How do I store a MySQL query result into a local CSV file? I don't have access to the remote machine.
如何将 MySQL 查询结果存储到本地 CSV 文件中？我无权访问远程机器。
采纳答案by codaddict
You can try doing :
你可以尝试做：
SELECT a,b,c
FROM table_name
INTO OUTFILE '/tmp/file.csv'
FIELDS TERMINATED BY ','
ENCLOSED BY '"'
LINES TERMINATED BY '\n'
We use the OUTFILEclause here to store the output into a CSV file.
We enclose the fields in double-quotes to handle field values that have the comma in them and we separate the fields by comma and separate individual line using newline.
我们在这里使用OUTFILE子句将输出存储到 CSV 文件中。
我们将字段用双引号括起来以处理其中包含逗号的字段值，我们用逗号分隔字段并使用换行符分隔单个行。
回答by tmarthal
You're going to have to use the command line execution '-e' flag.
您将不得不使用命令行执行“-e”标志。
$> /usr/local/mysql/bin/mysql -u user -p -h remote.example.com -e "select t1.a,t1.b from db_schema.table1 t1 limit 10;" > hello.txt
Will generate a local hello.txtfile in your current working directory with the output from the query.
将在您当前的工作目录中生成一个本地hello.txt文件，其中包含查询的输出。
回答by kamal sehrawat
We can use command line execution '-e' flag and a simple python script to generate result in csv format.
我们可以使用命令行执行 '-e' 标志和一个简单的 python 脚本来生成 csv 格式的结果。
create a python file (lets name = tab2csv) and write the following code in it..
#!/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)Run the following command by updating mysql credentials correctly
mysql -u orch -p -h database_ip -e "select * from database_name.table_name limit 10;" | python tab2csv > outfile.csv
创建一个python文件（让名称= tab2csv）并在其中写入以下代码..
#!/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)通过正确更新 mysql 凭据运行以下命令
mysql -u orch -p -h database_ip -e "select * from database_name.table_name limit 10;" | python tab2csv > outfile.csv
Result will be stored in outfile.csv.
结果将存储在 outfile.csv 中。
回答by Amroz K. Siddiqui
Use the concat function of mysql
使用mysql的concat函数
mysql -u <username> -p -h <hostname> -e "select concat(userid,',',surname,',',firstname,',',midname) as user from dbname.tablename;" > user.csv
You can delete the first line which contains the column name "user".
您可以删除包含列名称“user”的第一行。
回答by Raul Luna
I am facing this problem and I've been reading some time for a solution: importing into excel, importing into access, saving as text file...
我正面临这个问题，我一直在阅读一些解决方案：导入excel，导入access，另存为文本文件...
I think the best solution for windowsis the following:
我认为Windows的最佳解决方案如下：
- use the command insert...select to create a "result" table. The ideal scenario whould be to automatically create the fields of this result table, but this is not possible in mysql
- create an ODBC connection to the database
- use access or excel to extract the data and then save or process in the way you want
- 使用命令 insert...select 创建“结果”表。理想的场景应该是自动创建这个结果表的字段，但这在mysql中是不可能的
- 创建到数据库的 ODBC 连接
- 使用 access 或 excel 提取数据，然后以您想要的方式保存或处理
For Unix/LinuxI think that the best solution might be using this -e option tmarthal said before and process the output through a processor like awk to get a proper format (CSV, xml, whatever).
对于 Unix/Linux，我认为最好的解决方案可能是使用 tmarthal 之前所说的 -e 选项，并通过像 awk 这样的处理器处理输出以获得正确的格式（CSV、xml 等）。
回答by Anurag
Run the MySQl query to generate CSV from your App like below
运行 MySQl 查询以从您的应用程序生成 CSV，如下所示
SELECT order_id,product_name,qty FROM orders INTO OUTFILE '/tmp/orders.csv'
FIELDS TERMINATED BY ',' ENCLOSED BY '"' LINES TERMINATED BY '\n'
It will create the csv file in the tmp folder of the application.
它将在应用程序的 tmp 文件夹中创建 csv 文件。
Then you can add logic to send the file through headers.
然后您可以添加逻辑以通过标头发送文件。
Make sure the database user has permissions to write into the remote file-system.
确保数据库用户具有写入远程文件系统的权限。
