有没有办法将整个MySQL数据库转换成有效的CSV文件?

I am working with a large database 1.5 gig in size and hundreds of tables / fields. I need to convert all tables into CSV files. PhpMyAdmin does not do this easily / times out.

我正在处理一个大型数据库1.5 gig的大小和数百个表/字段。我需要将所有表转换为CSV文件。PhpMyAdmin不容易做到这一点。

I would rather use a shell / mysql command or a script to get the data out and into CSV.

我宁愿使用shell / mysql命令或脚本将数据取出并放入CSV。

Note:

注意:

I am looking to export ALL tables of the database - in 1 shot. I can not produce an export command for every single table individually.

我正在寻找导出数据库的所有表-在一个镜头。我不能为每个表单独生成导出命令。

3 个解决方案

#1

You can use mysqldump:

您可以使用mysqldump:

The mysqldump command can also generate output in CSV, other delimited text, or XML format.

mysqldump命令还可以生成CSV、其他分隔文本或XML格式的输出。

In particular, look at the following arguments:

特别是，看看下面的论点:

--tab=path
——选项卡=路径
--fields-[optionally-]enclosed-by
——字段(可选)包围
--fields-escaped-by
——fields-escaped-by
--fields-terminated-by
——fields-terminated-by
--lines-terminated-by
——lines-terminated-by
--no-create-info
——no-create-info

#2

You will need to do this table by table, see below.

您将需要逐个表地执行此表，请参见下面。

SELECT *
INTO OUTFILE '/tmp/products.csv'
FIELDS TERMINATED BY ','
ENCLOSED BY '"'
ESCAPED BY '\\'
LINES TERMINATED BY '\n'
FROM products

Note that the directory must be writable by the MySQL database server. If it's not, you'll get an error message like this:

注意，该目录必须由MySQL数据库服务器写入。如果不是，你会得到这样的错误信息:

#1 - Can't create/write to file '/tmp/products.csv' (Errcode: 13)

Also note that it will not overwrite the file if it already exists, instead showing this error message:

还要注意，如果文件已经存在，它不会覆盖该文件，而是显示此错误消息:

#1086 - File '/tmp/products.csv' already exists

Source: http://www.electrictoolbox.com/mysql-export-data-csv/

来源:http://www.electrictoolbox.com/mysql-export-data-csv/

#3

Information about the software : sql2csv

关于软件的信息:sql2csv

Download link exe : http://www.convert-in.com/demos/sql2csv.exe

下载链接exe: http://www.convert-in.com/demos/sql2csv.exe

This is best option I found around for windows. With the software we can connect to local and remote DB server and select schema. In one shot we can extract all tables data into Valid CSV files.

这是我为windows找到的最好的选择。通过这个软件，我们可以连接到本地和远程DB服务器并选择模式。我们可以一次将所有表数据提取到有效的CSV文件中。

Features :

特点:

#1