使用'mysqldump'以CSV格式转储所有表

Question

我需要以CSV格式导出MySQL中的所有表格。

是否有使用mysqldump命令只输出每个表格的每一行并以CSV格式显示的方法？

- Ken

---

你可以使用 mysql.exe 程序来完成，尝试执行 SELECT * FROM table INTO OUTFILE 'file_name'。但是你需要手动指定每个表。 - Devart

Answer 1

首先，我可以给你一个表的答案：

所有这些 INTO OUTFILE 或者 --tab=tmpfile（还有 -T/path/to/directory）的方法都要求在与 MySQL 服务器相同的服务器上运行 mysqldump 并具有访问权限。

我的解决方案是简单地使用带有 -B 参数的 mysql（而不是 mysqldump），内联 SELECT 语句并使用 -e，然后使用 sed 处理 ASCII 输出，最终得到包括标题字段行的 CSV：

例如：

 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”、“[e-mail]” “4”、“miedziak”、“xxxxxxxxxx”、“miedziak”、“[e-mail]” “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”、“_”、“[e-mail]”

在这个一行命令的末尾添加 > outfile.csv，就可以得到该表格的 CSV 文件。

接下来，使用以下命令获取您所有表格的列表：

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

接下来，只需要再进行一步就可以创建一个循环，例如在Bash shell中迭代这些表：

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

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

- Marcos

---

如果查询结果超出了您要转储到的计算机的内存限制，那么此方法将失败。有什么解决办法吗？ - T. Brian Jones

这似乎没有考虑到STRING列类型中有双引号的情况--它们应该被转义，否则就会出错。你有什么想法怎么做？ - timetofly

@Blossoming_Flower：这是更新后的正则表达式，用于转义双引号： | sed "s/\"/\"\"/;s/'/\'/;s/\t/\",\"/g;s/^/\"/;s/$/\"/;s/\n//g"（在CSV中正确转义双引号） - OlivierLarue

这是去掉引号后的sed命令：sed "s/'//;s/\t/,/g;s/\n//g"。其功能为：替换文本中的单引号为空，将制表符替换为逗号，并删除所有换行符。 - elegant dice

有没有办法使用mysqldump进行这样的操作？由于MySQL中大表转储需要大量的RAM，而mysqldump具有快速模式以逐行检索。 - Omer Anisfeld

Answer 2

这个命令将在/path/to/directory目录下创建两个文件，分别是table_name.sql和table_name.txt。

SQL文件中包含表的创建架构，txt文件中包含mytable表的记录，其中字段由逗号分隔。

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

- Armance

---

请记住使用-T/路径指向一个mysql进程可写入的目录。 - Mayank Jain

仅当您从与数据库服务器相同的计算机上运行mysqldump时才起作用。 - Jake

如果你遇到了MySQL的安全文件权限问题，可以执行 SHOW VARIABLES LIKE "secure_file_priv"; 命令来查看指定的输出文件夹。如果无法重启MySQL服务器，就使用该文件夹路径作为 mysqldump 命令的输出路径。 - sjas

Answer 3

如果您正在使用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;

现在，您可以使用其他技术来重复此命令以处理多个表格。详情请参见：

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

- Anis

---

这似乎是最合适的答案，因为它既灵活又高效。 - Thomas Kimber

Answer 4

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

name应包含以下之一：

`--fields-terminated-by`

\t or "\""

`--fields-enclosed-by=name`
   Fields in the output file are enclosed by the given

并且

--lines-terminated-by

• \r
• \n
• \r\n

自然地，您应该逐个表格地使用mysqldump。

我建议您将所有表格名称收集到一个文本文件中。然后，遍历所有表格运行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 $1}'`
    TB=`echo "${DBTB}" | sed 's/\./ /g' | awk '{print $2}'`
    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

- RolandoMySQLDBA

---

那不是CSV，那是制表符分隔的。CSV需要逗号转义、引用等处理。 - Ken Williams

@KenWilliams 谢谢。 我在 --fields-enclosed-by 中添加了双引号。 - RolandoMySQLDBA

这会产生错误。mysqldump: 您必须使用 --fields-... 选项与 --tab 选项一起使用... - Anthony Hatzopoulos

我找不到你说你添加的“--tab”选项。 - DrLightman

Cloud SQL文档描述了如何创建mysqldump。https://cloud.google.com/sql/docs/mysql/import-export/ 其中一些选项也可能与为bigquery创建CSV转储相关，例如mysqldump ---hex-blob --default-character-set=utf8。 - intotecho

Answer 5

这个对我很有帮助：

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

或者，如果您只想转储特定的表：

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

为了避免这个错误，我正在将导出数据到 /var/lib/mysql-files/：

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

- the

Answer 6

看起来其他人也遇到了这个问题，现在有一个简单的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

- Vajk Hermecz

---

注意，mysqldump-to-csv 在其简单代码中存在一些错误。因此，请准备好修复它，或者选择一个速度较慢但稳定的解决方案... - Vajk Hermecz

Answer 7

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

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

- Devart

Answer 8

这是一个Python解决方案：

import os
import subprocess

import pymysql.cursors


def get_table_names(cursor: pymysql.cursors.Cursor, database_name) -> list[str]:
    """Returns a list of all the table names in the database"""
    with cursor:
        cursor.execute(f"SHOW TABLES FROM {database_name};")
        tables = cursor.fetchall()
    tables = [table[0] for table in tables]
    return tables


def save_clean_data(traget_directory: str, mysql_user: str, my_sql_password: str, database_name: str, host: str, cursor: pymysql.cursors.Cursor) -> None:
    """Saves the each table in the database to a csv file"""
    os.makedirs(traget_directory, exist_ok=True)
    expresion = r"s/\"/\"\"/g;s/'/\'/;s/\t/\",\"/g;s/^/\"/;s/$/\"/;s/\n//g"
    for table_name in get_table_names(cursor, database_name):
        file_path = os.path.join(traget_directory, f'{table_name}.csv')
        if not os.path.exists(file_path):
            dump_command = (
                f'mysql -B -u {mysql_user} -p{my_sql_password} {database_name} -h {host}'
                f' -e "SELECT * FROM {table_name};" | sed "{expresion}" > {file_path}'
            )
            subprocess.call(dump_command, shell=True)

- Yoni Kremer