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

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

-- 导出表脚本
SELECT * FROM test.`testbackup` INTO OUTFILE '/tmp/testbackup1.txt' FIELDS TERMINATED BY ',' OPTIONALLY ENCLOSED BY '"'

        mysql 开发进阶篇系列 49 表的数据导出(into outfile,mysqldump)

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

      mysql 开发进阶篇系列 49 表的数据导出(into outfile,mysqldump)
    上面的导出结果,因为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 '"'

      mysql 开发进阶篇系列 49 表的数据导出(into outfile,mysqldump)
  上面的分隔符" , "并没有转义,因为字符中两边带有引用符 " " "(双引号), 所以没有将它作为字段分隔符。只是作为一个普通的数据字符来对待。下面继续将输出文件的字段引用符去掉,这时数据中的" , "将成为转义字符而加上了" \ "。

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

      mysql 开发进阶篇系列 49 表的数据导出(into outfile,mysqldump)

    总结: 当导出命令中包含字段引用符时(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的表数据。
        mysql 开发进阶篇系列 49 表的数据导出(into outfile,mysqldump)

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

上一篇:Binary Tree HDU - 5573 (思维)


下一篇:tf.nn.embedding_lookup函数