目录
一、导出
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：设置分隔符，可以为单个或多个字符。默认值是\tFIELDS 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，则表示文件位于客户端，如果没有，则表示文件在Server端。同时，这个关键字的使用还会影响到load data命令对于错误数据的处理方式
A) 如果是指定了local，则数据从客户端读取，文档中的说法是会在服务端的临时目录下创建一份文件的copy，但我在测试的时候并没有发现，如果file_name中是绝对路径就不用解释，如果是相对路径，则文件的位置应该是在客户端程序启动的位置，所以为了保险，一般使用绝对路径。由于涉及到数据传输，所以这种方式会相对来说慢一些。
B) 如果未指定local，则文件应该是直接在服务端，这种情况下如果文件名使用的是相对路径，则又分两种情况，一种是文件名前没有相对目录，则直接是在默认数据库的data目录下查找，如果是指定了相对目录，则从server的data目录下寻找。
C) 如果指定了local，则当某条数据处理有误时，系统把这个错误记录为一个warning，不会影响下一条数据的处理，因为涉及到数据传输。而如果没有指定local，则默认情况下，遇到错误后不会继续执行。
所以，如果我们是在客户端执行load data命令，一定记得加上local参数。
如果指定了,那么,当前的数据跟表中的数据有惟一性冲突的时候，采用什么样的方式，是替换已有还是忽略当前。特别需要说明的是，当这两种方式都未指定时，如果数据来自于客户端，则重复的数据会忽略，如果来源于服务端，则命令将终止执行。
指定具体的分区
指定编码集，如果文件的编码跟数据库的编码不一致，可能会出现乱码的问题。所以要注意的是，这里指定的是文件的编码集，而不是数据库的编码集
- [{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’.
忽略文件中的前 number 行，通常情况下，我们生成的文件可能有列名，那么要忽略的放在，这儿的值设置为１即可。需要注意的是这里是行的数量，而不是行号。
- [(col_name_or_user_var,…)]
有的时候我们不需要给所有的字段都填充值，这个时候就可以指定列名，以()将列名括起来，注意这里也可以是用户自定义的用户表达式。
如果在前一步中指定了用户表达式，那么相应就可以使用列名等于用户表达式的方式来指定
## 对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';
