mysql 开发进阶篇系列 49 表的数据导出

一.概述

　　在数据库的日常维护中，表的导入和导出是很频繁的操作，本篇讲解如何使用导入功能，并以案例为演示。某些情况下，需要将表里的数据导出为某些符号分割的纯数据文本，而不是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提供的接口。