MySQL CSV 格式的 mysqldump

mysqldump in CSV format

提问by Ken

I need to dump alltables in MySQL in CSV format.

我需要以CSV 格式转储MySQL 中的所有表。

Is there a command using mysqldumpto justoutput every row for every table in CSV format?

是否有使用命令mysqldump来只输出每一行的CSV格式的每个表？

回答by Marcos

First, I can give you the answer for ONE table:

首先，我可以给你一张桌子的答案：

The trouble with all these INTO OUTFILEor --tab=tmpfile(and -T/path/to/directory) answers is that it requires running mysqldumpon the same serveras the MySQL server, and having those access rights.

所有这些INTO OUTFILE或--tab=tmpfile（和-T/path/to/directory）答案的问题在于，它需要在与MySQL 服务器相同的服务器上运行mysqldump，并具有这些访问权限。

My solution was simply to use mysql(NOT mysqldump) with the -Bparameter, inline the SELECT statement with -e, then massage the ASCII output with sed, and wind up with CSV including a header field row:

我的解决方案是简单地将mysql(NOT mysqldump) 与-B参数一起使用，将 SELECT 语句与 内联-e，然后用 按摩 ASCII 输出sed，并以包含标题字段行的 CSV 结束：

Example:

例子：

 mysql -B -u username -p password database -h dbhost -e "SELECT * FROM accounts;" \
 | sed "s/\"/\"\"/g;s/'/\'/;s/\t/\",\"/g;s/^/\"/;s/$/\"/;s/\n//g"

"id","login","password","folder","email" "8","mariana","xxxxxxxxxx","mariana","" "3","squaredesign","xxxxxxxxxxxxxxxxx","squaredesign","[email protected]" "4","miedziak","xxxxxxxxxx","miedziak","[email protected]" "5","Sarko","xxxxxxxxx","Sarko","" "6","Logitrans Poland","xxxxxxxxxxxxxx","LogitransPoland","" "7","Amos","xxxxxxxxxxxxxxxxxxxx","Amos","" "9","Annabelle","xxxxxxxxxxxxxxxx","Annabelle","" "11","Brandfathers and Sons","xxxxxxxxxxxxxxxxx","BrandfathersAndSons","" "12","Imagine Group","xxxxxxxxxxxxxxxx","ImagineGroup","" "13","EduSquare.pl","xxxxxxxxxxxxxxxxx","EduSquare.pl","" "101","tmp","xxxxxxxxxxxxxxxxxxxxx","_","[email protected]"

"id","login","password","folder","email" "8","mariana","xxxxxxxxxx","mariana","""3","squaredesign","xxxxxxxxxxxxxxxxx"," squaredesign","[email protected]" "4","miedziak","xxxxxxxxxx","miedziak","[email protected]" "5","Sarko","xxxxxxxxx","Sarko"," ""6","Logitrans 波兰","xxxxxxxxxxxxxx","LogitransPoland",""7","Amos","xxxxxxxxxxxxxxxxxxxxx","Amos","""9","Annabelle","xxxxxxxxxxxxxxxx", 《安娜贝尔》、《11》、Brandfathers and Sons","xxxxxxxxxxxxxxxxxx","BrandfathersAndSons",""12","Imagine Group","xxxxxxxxxxxxxxxx","ImagineGroup","""13","EduSquare.pl","xxxxxxxxxxxxxxxxx","EduSquare .pl","""101","tmp","xxxxxxxxxxxxxxxxxxxxx","_","[email protected]"_","[email protected]"_","[email protected]"

Add a > outfile.csvat the end of that one-liner, to get your CSV file for that table.

> outfile.csv在一行的末尾添加一个，以获取该表的 CSV 文件。

Next, get a list of ALL your tables with

接下来，获取所有表的列表

mysql -u username -ppassword dbname -sN -e "SHOW TABLES;"

From there, it's only one more step to make a loop eg. in the bashshell to iterate over those tables:

从那里开始，只需再执行一个循环即可，例如。在bashshell 中迭代这些表：

 for tb in $(mysql -u username -ppassword dbname -sN -e "SHOW TABLES;"); do
     echo .....;
 done

Between the doand ; doneinsert the long command I wrote in Part 1 above, but substitute your tablename with $tbinstead.

在do和之间; done插入我在上面第 1 部分中编写的长命令，但用$tb代替替换您的表名。

回答by Armance

This command will create 2 files in /path/to/directory table_name.sql and table_name.txt.

此命令将在 /path/to/directory table_name.sql 和 table_name.txt 中创建 2 个文件。

The sql file will contain the table creation schema and the txt file will contain the records of the mytable table with fields delimited by a comma.

sql 文件将包含表创建模式，txt 文件将包含 mytable 表的记录，字段以逗号分隔。

mysqldump -u username -p -t  -T/path/to/directory dbname table_name --fields-terminated-by=',' 

回答by RolandoMySQLDBA

mysqldump has options for CSV formatting

mysqldump 有 CSV 格式选项

--fields-terminated-by=name
                  Fields in the output file are terminated by the given
--lines-terminated-by=name
                  Lines in the output file are terminated by the given

the nameshould contain one of the following

对name应包含以下内容之一

`--fields-terminated-by`

\tor "\""

\t或者 "\""

`--fields-enclosed-by=name`

? ? ? Fields in the output file are enclosed by the given

? ? ? 输出文件中的字段由给定的

and

和

--lines-terminated-by

--lines-terminated-by

• \r
• \n
• \r\n

• \r
• \n
• \r\n

Naturally you should mysqldump each table individually

自然地，您应该单独 mysqldump 每个表

I suggest you gather all table names in a text file. Then, iterate through all tables running mysqldump. Here is a script that will dump and gzip 10 tables at a time:

我建议您将所有表名收集在一个文本文件中。然后，遍历所有运行 mysqldump 的表。这是一个将一次转储和 gzip 10 个表的脚本：

MYSQL_USER=root
MYSQL_PASS=rootpassword
MYSQL_CONN="-u${MYSQL_USER} -p${MYSQL_PASS}"
SQLSTMT="SELECT CONCAT(table_schema,'.',table_name)"
SQLSTMT="${SQLSTMT} FROM information_schema.tables WHERE table_schema NOT IN "
SQLSTMT="${SQLSTMT} ('information_schema','performance_schema','mysql')"
mysql ${MYSQL_CONN} -ANe"${SQLSTMT}" > /tmp/DBTB.txt
COMMIT_COUNT=0
COMMIT_LIMIT=10
TARGET_FOLDER=/path/to/csv/files
for DBTB in `cat /tmp/DBTB.txt`
do
    DB=`echo "${DBTB}" | sed 's/\./ /g' | awk '{print }'`
    TB=`echo "${DBTB}" | sed 's/\./ /g' | awk '{print }'`
    DUMPFILE=${DB}-${TB}.csv.gz
    mysqldump ${MYSQL_CONN} -T ${TARGET_FOLDER} --fields-terminated-by="," --fields-enclosed-by="\"" --lines-terminated-by="\r\n" ${DB} ${TB} | gzip > ${DUMPFILE}
    (( COMMIT_COUNT++ ))
    if [ ${COMMIT_COUNT} -eq ${COMMIT_LIMIT} ]
    then
        COMMIT_COUNT=0
        wait
    fi
done
if [ ${COMMIT_COUNT} -gt 0 ]
then
    wait
fi

Give it a Try !!!

试一试 ！！！

回答by Anis

If you are using MySQL or MariaDB, the easiest and performant way dump CSV for single table is -

如果您使用的是 MySQL 或 MariaDB，则为单个表转储 CSV 的最简单且高效的方法是 -

SELECT customer_id, firstname, surname INTO OUTFILE '/exportdata/customers.txt'
  FIELDS TERMINATED BY ',' OPTIONALLY ENCLOSED BY '"'
  LINES TERMINATED BY '\n'
  FROM customers;

Now you can use other techniques to repeat this command for multiple tables. See more details here:

现在您可以使用其他技术对多个表重复此命令。在此处查看更多详细信息：

• https://mariadb.com/kb/en/the-mariadb-library/select-into-outfile/
• https://dev.mysql.com/doc/refman/5.7/en/select-into.html

• https://mariadb.com/kb/en/the-mariadb-library/select-into-outfile/
• https://dev.mysql.com/doc/refman/5.7/en/select-into.html

回答by kqw

This worked well for me:

这对我来说效果很好：

 mysqldump DBNAME --fields-terminated-by ',' \
 --fields-enclosed-by '"' --fields-escaped-by '\' \
 --no-create-info --tab /var/lib/mysql-files/

Or if you want to only dump TABLENAME:

或者，如果您只想转储 TABLENAME：

 mysqldump DBNAME TABLENAME --fields-terminated-by ',' \
 --fields-enclosed-by '"' --fields-escaped-by '\' \
 --no-create-info --tab /var/lib/mysql-files/

I'm dumping to /var/lib/mysql-files/to avoid this error:

我正在转储以/var/lib/mysql-files/避免此错误：

mysqldump: Got error: 1290: The MySQL server is running with the --secure-file-priv option so it cannot execute this statement when executing 'SELECT INTO OUTFILE'

mysqldump: Got error: 1290: MySQL 服务器正在使用 --secure-file-priv 选项运行，因此在执行“SELECT INTO OUTFILE”时无法执行此语句

回答by Vajk Hermecz

Looks like others had this problem also, there is a simple python scriptnow, for converting output of mysqldump into csv files.

看起来其他人也有这个问题，现在有一个简单的 python 脚本，用于将 mysqldump 的输出转换为 csv 文件。

wget https://raw.githubusercontent.com/jamesmishra/mysqldump-to-csv/master/mysqldump_to_csv.py
mysqldump -u username -p --host=rdshostname database table | python mysqldump_to_csv.py > table.csv

回答by Devart

You also can do it using Data Export tool in dbForge Studio for MySQL.

您也可以使用dbForge Studio for MySQL 中的数据导出工具来完成。

It will allow you to select some or all tables and export them into CSV format.

它将允许您选择部分或所有表并将它们导出为 CSV 格式。

回答by user11297642

You could using the easy more method, csvdump tool to print remote csv file from mysql: https://github.com/bobby96333/csvdump

您可以使用更简单的方法 csvdump 工具从 mysql 打印远程 csv 文件：https: //github.com/bobby96333/csvdump

回答by minimallinux

Thats a lot of messing around just to export as csv, why dont you just install Dbeaver from enter link description here

只是为了导出为 csv 就搞砸了很多，为什么不直接从这里输入链接描述来安装 Dbeaver

It covers most of the popular databases except nosql,and can export in a variety of formats including csv and sql.

它涵盖了除nosql之外的大多数流行数据库，并且可以导出包括csv和sql在内的多种格式。