MySQL常用的文本文件导出导入方式总结

最新推荐文章于 2026-05-03 16:24:11 发布

原创最新推荐文章于 2026-05-03 16:24:11 发布 · 4.3k 阅读

27 ·

本内容遵循CC 4.0 BY-SA版权协议

标签

#mysql #数据库 #database

本文详细介绍了MySQL数据库的三种数据导出方法，包括mysql命令、SELECT INTO OUTFILE和mysqldump，以及两种导入方法，mysqlimport和LOAD DATA INFILE。在导出时，详细讲解了各种参数的用法，如分隔符、字段括号和行结束符等。在导入时，提到了如何处理数据冲突和文件位置的选择。此外，还强调了secure_file_priv参数在导入导出中的作用。

一、导出

1.1 mysql命令导出文本文件

1.2 select into outfile导出文本文件

1.3 mysqldump导出文本文件

二、导入

2.1 mysqlimport导入文本文件

2.2 LOAD DATA INFILE导入文本文件

一、导出

1.1 mysql命令导出文本文件

语法格式：

mysql [options] -e "SELECT 语句" dbname>C:\name.txt

常用参数：

-e, --execute: 执行命令并退出
-N, --skip-column-names: 不输出字段名
-H, --html: 输出数据html格式
-X, --xml: 输出数据为xml格式

举例：

## 导出SQL查询的结果到文本文件，字段默认以\t分隔
mysql -u -p -Ne "select id,name from db_test.tb_test01 where id>2 and id<10;" > /data/mysql_3306/backup/tb_test01.txt

## 拼SQL，导出表字段以逗号分隔
mysql -u -p -Ne "select concat(id,',',name) from db_test.tb_test01 where id>2 and id<10;" > /data/mysql_3306/backup/tb_test01.txt

1.2 select into outfile导出文本文件

语法格式：

SELECT [列名] FROM table [WHERE 语句] INTO OUTFILE '目标文件' [OPTION];

OPTION为可选参数选项，常用参数：

FIELDS TERMINATED BY：设置分隔符，可以为单个或多个字符。默认值是\t
FIELDS ENCLOSED BY：设置字符来括住字段的值，只能为单个字符。默认不使用任何符号
FIELDS OPTIONALLY ENCLOSED BY：设置字符来括住CHAR、VARCHAR和TEXT等字符型字段。默认情况下不使用任何符号
FIELDS ESCAPED BY：设置转义字符，只能为单个字符。默认值为“\”
LINES STARTING BY：设置每行开头的字符，可以为单个或多个字符。默认不使用任何字符
LINES TERMINATED BY：设置每行数据结尾的字符，可为单个或多个字符。默认值是\n

FIELDS和LINES两个子句都是自选的，但是如果同时指定，FIELDS必须位于LINES的前面。

备注：

必须在服务器上备份，与secure_file_priv参数结合

secure_file_prive=null -- 限制mysqld 不允许导入导出
secure_file_priv=/tmp/ -- 限制mysqld的导入导出只能发生在/tmp/目录下
secure_file_priv=' ' -- 不对mysqld 的导入导出做限制

## 示例，导出查询结果，以逗号为分隔符，字符型用双引号括住，每行使用>开头，换行符为\r\n
SELECT * FROM db_test.tb_test03 where id>2 and id<10 
INTO OUTFILE "/data/mysql_3306/backup/tb_test03.txt"
    FIELDS
        TERMINATED BY ','
        OPTIONALLY ENCLOSED BY '\"'
    LINES
        STARTING BY '\>'
        TERMINATED BY '\r\n';

1.3 mysqldump导出文本文件

语法格式：

mysqldump -u root -pPassword -T 目标目录 dbname [tables] [option];

其中：

-T, --tab=name：导出.sql（建表语句）和.txt（数据）文件。
目标目录：是指导出的文本文件的路径。
dbname参数表示数据库的名称。
tables参数表示要导出的表的名称。如果不指定，则导出数据库dbname中所有的表，导出多张表，则表之间使用空格分开。

option为可选参数选项，这些选项需要结合-T选项使用，常见的参数如下：

--fields-terminated-by：设置分隔符，可以为单个或多个字符。默认值为制表符\t。
--fields-enclosed-by：设置字符来括住字段的值。
--fields-optionally-enclosed-by：设置字符括住CHAR、VARCHAR和TEXT等字符型字段，只能为单个字符。
--fields-escaped-by：设置转义字符，只能为单个字符。默认值为“\”。
--lines-terminated-by：设置换行符，可为单个或多个字符。默认值为\n。

其实是调用 SELECT ...INTO OUTFILE语句，所以必须在MySQL Server本地执行，并且执行备份的MySQL账户还必须具有FILE权限，即要设置secure_file_priv参数

二、导入

2.1 mysqlimport导入文本文件

mysqlimport是MySQL提供的一个命令行程序，可用于数据导入。从本质上来说，是LOAD DATA INFILE的命令接口，而且大多数的选项都和LOAD DATA INFILE语法相同。

其语法格式如下：
mysqlimport [options] db_name textfilel [textfile2 …]

和LOAD DATA INFILE不同的是，mysqlimport命令可以用来导入多张表。并且通过–use-threads参数并发地导入不同的文件。这里的并发是指并发导入多个文件，而不是指mysqlimport可以并发地导入一个文件。

常用参数：

--ignore-lines=n：表示可以忽略前n行。
-l, --lock-tables：写入时锁定所有表
-c, --columns：往表里导入指定字段，如：--columns='Name,Age,Gender'
-C, --compress：在客户端和服务器之间启用压缩传递所有信息
--fields-terminated-by：设置字段间的分隔符，可为单个或多个字符，默认为制表符“\t”。
--fields-enclosed-by：设置字符来括住字段的值
--fields-optionally-enclosed-by: 设置字符括住CHAR、VARCHAR和TEXT等字符型字段，只能为单个字符。
--fields-escaped-by: 设置转义字符，只能为单个字符。默认值为“\”。
--lines-terminated-by: 设置换行符，可以为单个或多个字符。默认值为\n。
-d, --delete: 先删除表数据，再导入
-r, --replace: 如果唯一键冲突，新值替换旧值
-i, --ignore：如果唯一键冲突，保留旧值，忽略新值
--use-threads=n: 设置并行导入文件线程数

#########################单表导入#########################
## 导入db_test库tb_test100表（文件名对应表名）
## --fields-terminated-by=, ：字段分隔符为逗号
## --fields-optionally-enclosed-by=\" ：字符型字段使用双引号括住
## --lines-terminated-by=\\r\\n ：换行符为\r\n
## --ignore-lines=2 ：忽略前2行
## --columns='id,name' ：文本中数据对应导入表的id和name列
mysqlimport --fields-terminated-by=, --fields-optionally-enclosed-by=\" --lines-terminated-by=\\r\\n --ignore-lines=2 --columns='id,name' db_test /backup/tb_test100.txt

#########################并行导入#########################
## --use-threads=2 ：开启两个线程分别导入tb_test01和tb_test02
mysqlimport --fields-terminated-by=, --fields-optionally-enclosed-by=\" --lines-terminated-by=\\r\\n --ignore-lines=1 --use-threads=2 db_test /backup/tb_test01.txt /backup/tb_test02.txt

2.2 LOAD DATA INFILE导入文本文件

语法如下：

LOAD DATA
[LOW_PRIORITY | CONCURRENT] [LOCAL]
INFILE 'file_name'
[REPLACE | IGNORE]
INTO TABLE tbl_name
[PARTITION (partition_name [, partition_name] ...)]
[CHARACTER SET charset_name]
[{FIELDS | COLUMNS}
[TERMINATED BY 'string']
[[OPTIONALLY] ENCLOSED BY 'char']
[ESCAPED BY 'char']
]
[LINES
[STARTING BY 'string']
[TERMINATED BY 'string']
]
[IGNORE number {LINES | ROWS}]
[(col_name_or_user_var
[, col_name_or_user_var] ...)]
[SET col_name={expr | DEFAULT}
[, col_name={expr | DEFAULT}] ...]

参数解释：

LOW_PRIORITY | CONCURRENT

如果你指定关键词LOW_PRIORITY,这个只对那些只采用了表级别锁（如MYISAM)的引擎有影响，比如InnoDB使用的是行锁，不受这个影响，具体的来说，使用了LOW_PRIORITY，则本操作会在其它线程完成之后再操作。
CONCURRENT会和其它线程同时进行，这个对性能是有一些影响。

LOCAL

这是个非常重要的关键字，指明了文件的位置，简单的说，如果指定了local，则表示文件位于客户端，如果没有，则表示文件在Server端。同时，这个关键字的使用还会影响到load data命令对于错误数据的处理方式
A) 如果是指定了local，则数据从客户端读取，文档中的说法是会在服务端的临时目录下创建一份文件的copy，但我在测试的时候并没有发现，如果file_name中是绝对路径就不用解释，如果是相对路径，则文件的位置应该是在客户端程序启动的位置，所以为了保险，一般使用绝对路径。由于涉及到数据传输，所以这种方式会相对来说慢一些。
B) 如果未指定local，则文件应该是直接在服务端，这种情况下如果文件名使用的是相对路径，则又分两种情况，一种是文件名前没有相对目录，则直接是在默认数据库的data目录下查找，如果是指定了相对目录，则从server的data目录下寻找。
C) 如果指定了local，则当某条数据处理有误时，系统把这个错误记录为一个warning，不会影响下一条数据的处理，因为涉及到数据传输。而如果没有指定local，则默认情况下，遇到错误后不会继续执行。
所以，如果我们是在客户端执行load data命令，一定记得加上local参数。

REPLACE | IGNORE

如果指定了,那么,当前的数据跟表中的数据有惟一性冲突的时候，采用什么样的方式，是替换已有还是忽略当前。特别需要说明的是，当这两种方式都未指定时，如果数据来自于客户端，则重复的数据会忽略，如果来源于服务端，则命令将终止执行。

PARTITION

指定具体的分区

CHARACTER SET

指定编码集，如果文件的编码跟数据库的编码不一致，可能会出现乱码的问题。所以要注意的是，这里指定的是文件的编码集，而不是数据库的编码集

[{FIELDS | COLUMNS} [TERMINATED BY ‘string’][[OPTIONALLY] ENCLOSED BY ‘char’][ESCAPED BY ‘char’]]

指定对于字段的处理方式
FIELDS和COLUMNS指定其中一个即可。
TERMINATED表示字段间的分隔符，
ENCLOSED BY指定字段值由什么符号包围，如果加上OPTIONALLY，则只对字符型的加符号包围，数字型的不加
ESCAPED表示指定转义字符。
在不指定这个参数的情况下，默认的字段分隔符是\t, 字段值无任何值包围，转义字符为\

[LINES [STARTING BY ‘string’] [TERMINATED BY ‘string’]]

指定每一行的起始符与终止符，默认情况下，起始符为空，终止符为’\n’，对于windows产生的文本文件来说，需要指定换行符为’\r\n’.

IGNORE number LINES

忽略文件中的前 number 行，通常情况下，我们生成的文件可能有列名，那么要忽略的放在，这儿的值设置为１即可。需要注意的是这里是行的数量，而不是行号。

[(col_name_or_user_var,…)]

有的时候我们不需要给所有的字段都填充值，这个时候就可以指定列名，以()将列名括起来，注意这里也可以是用户自定义的用户表达式。

[SET col_name = expr,…]

如果在前一步中指定了用户表达式，那么相应就可以使用列名等于用户表达式的方式来指定

## 对db_test.tb_test03表导入文本文件
## 以逗号为分隔符，字符型用双引号括住，每行使用>开头，换行符为\r\n
LOAD DATA INFILE '/data/mysql_3306/backup/tb_test03.txt' 
INTO TABLE db_test.tb_test03
      FIELDS
          TERMINATED BY '\、'
          OPTIONALLY ENCLOSED BY '\"'
      LINES
          STARTING BY '\>'
          TERMINATED BY '\r\n';