mysql outfile 迁移_mysql表数据导出的两种方法实例讲解( into outfile、mysqldump)

一.概述

在数据库的日常维护中，表的导入和导出是很频繁的操作，本篇讲解如何使用导入功能，并以案例为演示。某些情况下，需要将表里的数据导出为某些符号分割的纯数据文本，而不是sql语句，比如：(1)用来作为excel显示。 (2)节省备份空间。(3)为了快速的加载数据,load data的加载速度比普通的sql加载要快20倍以上。为了满足这些应用，可以使用以下两种办法来实现。

1. 方法1  into outfile

--格式如下:

select ... into tablename into outfile 'target_file' [option];

option参数

option参数说明

Fields terminated by 'string'

字符分隔符，默认为制表符 t

Fields [optionally] enclosed by 'char'

字段引用符，如果加optionally选项则只用在char,varchar和test等字符型字段上，默认不使用引用符

Fields escaped by 'char'

转义字符，默认为

Lines  starting by 'string'

每行前都加此字符串, 默认' '

Lines terminated by 'string'

行结束符 默认为 n

例1：下面是导出testbackup表数据到数据文本，文件在服务器目录/tmp目录下。使用字符分隔符(terminated by )为逗号(',') 。使用字段引用符(enclosed by)为双引号( ' " ')。

--导出表脚本

SELECT * FROM test.`testbackup` INTO OUTFILE '/tmp/testbackup.txt' FIELDS TERMINATED BY ',' ENCLOSED BY '"'

例2：发现第一列是数值型，如果不希望字段两边用引号，则语句改为如下脚本。导出到testbackup表数据 到服务器目录/tmp目录下(使用OPTIONALLY 选项引用符只用在char, varchar,test等字段上)

--导出表脚本

SELECT * FROM test.`testbackup` INTO OUTFILE '/tmp/testbackup1.txt' FIELDS TERMINATED BY ',' OPTIONALLY ENCLOSED BY '"'

1.1. 转义字符

into outfile方法导出来测试转义字符， 转义字符是由于含义模糊而需要特殊进行转换的字符。不同情况下，需要转义的字符不一样。在mysql 导出的数据中需要转义的字符主要包括以下3类: 转义字符本身； 字段分隔符； 记录分隔符。

--下面将testbackup表中的name更新为含()的转义字符以及字段分隔符(")

UPDATE testbackup SET `name`='\"##!aa' WHERE id=1

--导出数据

SELECT * FROM test.`testbackup` INTO OUTFILE '/tmp/testbackup2.txt' FIELDS TERMINATED BY ',' OPTIONALLY ENCLOSED BY '"'

上面的导出结果，因为name中含有转义字符本身 , 字段引用符(enclosed by)" " ", 因此，输出的数据中前面加上了转义字符，变成了\# 。

--下面将testbackup表中的name更新为含字段分隔符" , "的字符串

UPDATE testbackup SET `name`='\"#,#,!aa' WHERE id=1

--导出

SELECT * FROM test.`testbackup` INTO OUTFILE '/tmp/testbackup3.txt' FIELDS TERMINATED BY ',' OPTIONALLY ENCLOSED BY '"'

上面的分隔符" , "并没有转义，因为字符中两边带有引用符 " " "(双引号), 所以没有将它作为字段分隔符。只是作为一个普通的数据字符来对待。下面继续将输出文件的字段引用符去掉，这时数据中的" , "将成为转义字符而加上了" "。

--导出

SELECT * FROM test.`testbackup` INTO OUTFILE '/tmp/testbackup4.txt' FIELDS TERMINATED BY ','

总结： 当导出命令中包含字段引用符时(OPTIONALLY ENCLOSED BY ' " '), 数据中含有转义字符本身( )和字段引用符的字符需要转义。

当导出命令中不包含字段引用符时(OPTIONALLY ENCLOSED BY ' " '), 数据中含有转义字符本身( )和字段分隔符(TERMINATED BY ' , ')的字符需要转义。

2. 方法二  mysqldump

用 mysqldump 导出数据为纯文本。在第35篇讲到了mysqldump数据导出工具，讲到了数据表导出表结构和数据的可执行T-SQL脚本，可以用来创建表和表数据。

--之前导出的脚本文件最基本的语法是:

mysqldump -uroot -p dbname >dbname.txt--这里继续讲用mysqldump工具导出纯数据的文本。语法如下：mysqldump -uroot -p target_dir dbname tablename [option]

Option参数

说明

--fields-terminated-by=name

字段分隔符

--fields-enclosed-by=name

字段引用符

--fields-optionally-enclosed-by=name

字段引用符(只能用在char,varchar,text等字符型上)

--fields-escaped-by=name

转义字符

--lines- terminated-by=name

记录结束符

--导出 这里使用了字段分隔符，和字段引用符

[root@hsr tmp]# mysqldump -uroot -p -T /tmp/ test testbackup --fields-terminated-by ',' --fields-optionally-enclosed-by '"'

导出后产生了二个文件，(1)是testbackup.sql 的创建表结构脚本，(2)是testbackup.txt的表数据。

总结：使用mysqldump工具除了创建脚本文件外(table.sql)，和select .. into outfile的选项和语法相似，mysqldump实际调用的就是into outfile提供的接口。