mysql 开发进阶篇系列 49 表的数据导出(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提供的接口。
