我需要以CSV格式导出MySQL中的所有表格。
是否有使用mysqldump
命令只输出每个表格的每一行并以CSV格式显示的方法?
首先,我可以给你一个表的答案:
所有这些 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”、“mkobylecki@squaredesign.com” “4”、“miedziak”、“xxxxxxxxxx”、“miedziak”、“miedziak@mail.com” “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”、“_”、“WOBC-14.squaredesign.atlassian.net@yoMama.com”
在这个一行命令的末尾添加 > 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
替换您的表名。
| sed "s/\"/\"\"/;s/'/\'/;s/\t/\",\"/g;s/^/\"/;s/$/\"/;s/\n//g"
(在CSV中正确转义双引号) - OlivierLarue这个命令将在/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=','
SHOW VARIABLES LIKE "secure_file_priv";
命令来查看指定的输出文件夹。如果无法重启MySQL服务器,就使用该文件夹路径作为 mysqldump
命令的输出路径。 - sjas如果您正在使用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;
现在,您可以使用其他技术来重复此命令以处理多个表格。详情请参见:
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
--fields-enclosed-by
中添加了双引号。 - RolandoMySQLDBAmysqldump: 您必须使用 --fields-... 选项与 --tab 选项一起使用...
- Anthony Hatzopoulos这个对我很有帮助:
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选项,因此无法执行此语句
看起来其他人也遇到了这个问题,现在有一个简单的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
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)
mysql.exe
程序来完成,尝试执行 SELECT * FROM table INTO OUTFILE 'file_name'。但是你需要手动指定每个表。 - Devart