mysql into 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 '"'

-- 导出表脚本

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 '"'

-- 下面将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提供的接口。