有时经常会需要将MySQL数据库中的数据导出到外部存储文件中，MySQL数据库中的数据库可以导出成sql文本文件、xml文件或者html文件以及txt文本文件，同样这些导出文件也可以导入到MySQL数据库中。
一、导出文本文件
1. 用 SELECT * INTO OUTFILE 导出文本文件
MySQL 数据库导出数据时，允许使用包含导出定义的 SELECT 语句进行数据的导出操作。该文件被创建到服务器的宿主机上，因此必须拥有文件的写入权限，并且此文件不能提前存在，才能使用此语法，限制太多。
语法如下：
|
1 2 3 4 5 6 7 8 9 10 11 12 13 |
SELECT col1.. FROM table_name WHERE condition INTO OUTFILE "file_name" [FIELDS [TERMINATED BY 'string'] [[OPTIONALLY] ENCLOSED BY 'char'] [ESCAPED BY 'char' ] [LINES [STARTING BY 'string'] [TERMINATED BY 'string'] [IGNORE number LINES [(col_name_or_user_var,...)] [SET col_name = expr,...]]]; |
FIELDS
TERMINATED BY ‘string’
设置输出字段之间的分割字符，可以为单个或多个字符，默认为制表符’\t’，等同于TERMINATED BY ‘/t’;
[OPTIONALLY] ENCLOSED BY ‘char’
设置输出字段的包围字符，只能够为单个字符，如果使用[OPTIONALLY]则只有CHAR和VARCHAR等字符数据字段被包括。默认为ENCLOSED BY ”；
ESCAPED BY ‘char’
设置如何写入或读取特殊字符，只能够为单个字符，即设置转义符，默认值为’\’;
LINES
STARTING BY ‘string’
设置每行数据开头的字符，可以为单个或多个字符，默认情况下不适用任何字符;
TERMINATED BY ‘string’
设置每行数据结尾的字符，可以为单个或多个字符，默认值为’\n’；FIELDS和LINES两个子句都是自选的，但是如果两个都被指定了，FIELDS必须位于LINES的前面。
实例
语法 FIELDS 和 LINES 子句是相同的，两个子句都是可选的，但是如果同时指定了两个子句，则 FIELDS 必须在LINES前。如果用户没有指定一个 FIELDS 子句，则使用默认 FIELDS TERMINATED BY ‘/t’ ENCLOSED BY ” ESCAPED BY ‘//’。
|
1 2 3 4 5 6 7 8 |
# 以默认制表符"\t"格式导出数据到/tmp/1.txt文件; mysql> select * from TABLE_NAME into outfile "/tmp/1.txt"; # 以冒号格式导出数据到/tmp/1.txt文件; mysql> select * from TABLE_NAME into outfile "/tmp/1.txt" FIELDS TERMINATED BY ':'; # 以分号格式导出数据到/tmp/1.txt文件,并且对于char或varchar类型使用""括起来,以及所有为字段NULL的都正常显示; mysql> select * from TABLE_NAME into outfile "/tmp/1.txt" FIELDS TERMINATED BY ',' fields enclosed by '\"' fields ESCAPED BY ''; |
PS：可以把导入的文本文件格式化，命令 unix2dos 1.txt 是把 Linux 格式的文本格式化成 Windows 格式的，命令 dos2unix 相反。如果要把文本文件导入到 excel 中，可以创建新表格—–打开文件(所有文件)—–选择txt文件—–选择分割方式（tab/space/,）。
2. 用 mysqldump 命令导出文本文件
mysqldump 工具不仅可以做备份数据的工具可以将数据导出为包含 CREATE、INSERT 的 sql 文件，也可以导出为文本文件。
|
1 |
mysqldump -T path -uroot -predhat db_name [tables] [options] |
[options]
|
1 2 3 4 5 |
--fields-terminated-by=string #设置字段之间的分割字符，可以为单个或多个字符,默认为制表符'\t'; --fields-enclosed-by=char #设置字段的包围字符; --fields-optionally-enclosed-by=char #设置字段的包围字符,只能够为单个字符，如果使用OPTIONALLY则只有CHAR和VARCHAR等字符数据字段被包括; --fields-escaped-by=value #设置如何写入或读取特殊字符,只能够为单个字符,即设置转衣服,默认值为'\'; --lines-terminated-by=value #设置每行数据结尾的字符,可以为单个或多个字符,默认值为'\n'; |
只有指定了-T参数才可以导出纯文本文件，path 表示导出数据的目录，tables 为指定要导出的表名称，如果不指定，将导出数据库 db_name 中所有的表。[options] 为可选参数选项，这些选项需要结合-T选项使用才有效。
|
1 2 |
# 此语句将会在/tmp目录下生成两个文件,一个是1.sql和1.txt; $ mysqldump -T /tmp/1.txt db_name.table_name -uroot -predhat |
|
1 |
$ mysqldump -T /tmp/1.txt db_name.table_name -uroot -predhat --fields-terminated-by=: --fields-optionally-enclosed-by=\" --fields-escaped-by=? --lines-terminated-by=\r\n |
3. 用 mysql 命令导出文本文件
mysql 客户端是一个功能丰富的命令行工具，使用 mysql 可以在命令行模式下执行 SQL 语句，将查询结果导入到文本文件中。相比 mysqldump 来说，mysql 工具导出的结果可读性更强，更灵活。语法如下：
|
1 |
$ mysql -e "SELECT * FROM db_name.table_name;" > filename.csv |
默认重定向到文件中的结果集已经去掉了无用的字符，所谓无用字符就是格式排版字符。当然，你也可以使用-N去掉列名称；-s去掉无用字符。一般组合使用即可，另外，-ss 或-NB效果等同于-s -N。
上面导出文件虽然后缀为 csv，但本质还是一个文本文件，如果需要 csv 文件呢？最简单可以使用 Linux 下的 sed 命令处理一下。
简单说下，我们得到的数据，每个字段之间是用制表符 \t 分隔的。在 Linux 中，cat -A file可以把文件中的所有可见的和不可见的字符都显示出来，在 vim 中，如何将不可见字符也显示出来呢？当然，如果只是想在 vim 中查看的话，可以这样:%!cat -A在Vim中调用 cat 转换显示。这样的做法不便于编辑，其实 vim 本身是可以设置显示不可见字符的。
只需要:set invlist即可以将不可见的字符显示出来，例如，会以^I表示一个 tab 符（\t），$表示一个回车符等。最后，:set nolist可以回到正常的模式。
简单了解了这些，使用下面的 sed 命令对这些特殊字符处理一下就好了。转换为 csv 格式的排版模式即可。
|
1 |
sed 's/\t/","/g;s/^/"/g;s/$/"\r/g' filename.csv |
sed 命令用到了正则表达式，所以第一个表达式 s/\t/","/g 把所有的制表符替换为 ","，然后第二个表达式，将每行的开头加上双引号，第三个表达式将每行的结尾添加一个双引号和一个回车符\r（原始数据每行结尾只有一个换行符\n，sed 命令是处理去掉回车符后的字符串）。
二、导入文本文件
1. 用 LOAD DATA INFILE 方式导入文本文件
MySQL 允许将数据导出到外部文件，也可以从外部文件导入数据。MySQL 提供了一些导入数据的工具，这些工具有 LOAD DATA 语句、source 命令和 mysql 命令。LOAD DATA INFILE 语句将文本文件中的行以非常高的速度读入表中。 LOAD DATA INFILE 是补充 SELECT … INTO OUTFILE。载入的文件名称必须为文字字符串。下面介绍 LOAD DATA 语句的语法。
|
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 |
LOAD DATA [LOW_PRIORITY | CONCURRENT] [LOCAL] INFILE 'file_name' [REPLACE | IGNORE] INTO TABLE tbl_name [PARTITION (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,...)] [SET col_name = expr,...] |
这两个语句的语法 FIELDS 和 LINES 子句是相同的，两个子句都是可选的，但是如果同时指定了两个子句，则 FIELDS 必须在 LINES 前。
FIELDS
TERMINATED BY ‘string’
设置输入字段之间的分割字符，可以为单个或多个字符，默认为制表符’\t’，等同于TERMINATED BY ‘/t’;
[OPTIONALLY] ENCLOSED BY ‘char’
设置输入字段的包围字符，只能够为单个字符，如果使用[OPTIONALLY]则只有CHAR和VARCHAR等字符数据字段被包括。默认为ENCLOSED BY ”；
ESCAPED BY ‘char’
设置如何写入或读取特殊字符，只能够为单个字符，即设置转义符，默认值为’\’;
LINES
STARTING BY ‘string’
设置每行数据开头的字符，可以为单个或多个字符，默认情况下不适用任何字符;
TERMINATED BY ‘string’
设置每行数据结尾的字符，可以为单个或多个字符，默认值为’\n’；FIELDS和LINES两个子句都是自选的，但是如果两个都被指定了，FIELDS必须位于LINES的前面。
[IGNORE number LINES]
选项表示忽略文件开始处的行数，number 表示忽略的行数。执行 LOAD DATA 语句需要 FILE 权限;
实例：将 /tmp/2.txt 文件以 ‘:’ 为分隔符将多个字段数据导入到 user.aa 表中，aa 表必须提前存在，且表字段需设定好。
|
1 |
load data infile '/tmp/2.txt' into table user.aa fields terminated by ':'; |
在导入的过程中可能会报如下错误：ERROR 1290 (HY000): The MySQL server is running with the –secure-file-priv option so it cannot execute this statement.
权限问题，可以查看一下这个变量，如下：
|
1 2 3 4 5 6 7 8 9 |
mysql> show global variables like '%secure%'; +--------------------------+-----------------------+ | Variable_name | Value | +--------------------------+-----------------------+ | require_secure_transport | OFF | | secure_auth | ON | | secure_file_priv | /var/lib/mysql-files/ | +--------------------------+-----------------------+ 3 rows in set (0.01 sec) |
意思是允许这个目录下的文件进行 LOAD 操作，所以把文件拷贝到这个目录下执行就好了。
2. 用 mysqlimport 命令导入文本文件
mysqlimport 是 MySQL 内置的一个工具，使用 mysqlimport 可以导入文本文件，并且不需要登陆 mysql 客户端。mysqlimport 命令提供许多与 LOAD DATA INFILE 语句相同的功能。大多数选项直接对应 LOAD DATA INFILE 子句，语法如下：
|
1 2 3 4 5 6 7 8 |
mysqlimport -uroot -predhat db_name filename.txt [options] [options] --fields-terminated-by=value --field-enclosed-by=value --fields-optionally-enclosed-by=value --fields-escaped-by=value --lines-terminated-by=value --ignore-lines=n |
