Mysql导出数据

在某些情况下，为了一些特定的目的，经常需要将表里的数据导出为某些符号分割的纯数据文本，而不是 SQL 语句，因为LOAD DATA 的加载速度比普通的 SQL 加载要快 20 倍以上。

方法 1：使用 SELECT ...INTO OUTFILE ...命令来导出数据，具体语法如下。

mysql> SELECT * FROM tablename INTO OUTFILE 'target_file' [option];

其中 option 参数可以是以下选项：

例子1，将 emp 表中数据导出为数据文本，其中，字段分隔符为“,”，每个字段用双引号引用起来，记录结束符为回车符(默认如此，可以不写)

mysql> select * from emp into outfile '/tmp/emp.txt' fields terminated by "," enclosed by '"';

输出结果如下：

mysql> system more /tmp/emp.txt
"1","z1","aa"
"2","z1","aa"
"3","z1","aa"

例子2，发现例1中第一列是数值型，如果不希望字段两边用引号，则语句改为如下

mysql> select * from emp into outfile '/tmp/emp.txt' fields terminated by ","  optionally enclosed by '"' ;

 结果输出如下：

mysql> system more /tmp/emp.txt
1,"z1","aa"
2,"z1","aa"
3,"z1","aa"

下面测试一下转义字符，大概包括三类，转义字符本身，字段分隔符（导出的文本中用什么符号分隔），记录分隔符（每条记录之间用什么分隔，默认是回车）

例子3，更改上面例子中id=1的name为\"##!aa

update employee  set name ='\\"##!aa' where id=1;   #更新操作中转义字符本身也需要用转义字符来转义，所以这里有2个\\
# 然后做导出操作
select * from employee into outfile '/tmp/employee' fields terminated by "," optionally enclosed by ‘”’；

导出结果如下
mysql> system more /tmp/emp.txt
1,"\\\"##!aa","aa"
2,"z1","aa"
3,"z1","aa"

说明：name 中含有转义字符本身“\”，域引用符“”“，因此，在输出的数据中我们发现这两种字符前面都加上了转义字符“\”

例子4，将id=1的name更新为含有字段分隔符”，“的字符串

update employee set name='\\"#,#,!aa' where id=1;

然后导出
mysql> system rm /tmp/emp.txt  #需要删掉重名文件

mysql> select * from emp into outfile '/tmp/emp.txt' fields terminated by "," optionally enclosed by '"' ;

输出结果如下
mysql> system more /tmp/emp.txt
1,"\\\"#,#,!aa","aa"
2,"z1","aa"
3,"z1","aa"

说明：发现数据中的字符","没有被转义，原因是它和后面真正的字段分隔符之间没有冲突，因为name字段包含在双引号之间。

例子5：继续做测试，将输出文件的字段引用符去掉，这个时候，我们的预期是数据中的“,”将成为转义字符而需要加上“\”

mysql> system rm /tmp/emp.txt
mysql> select * from emp into outfile '/tmp/emp.txt' fields terminated by "," ;

输出结果如下

mysql> system more /tmp/emp.txt
1,\\"#\,#\,!aa,aa
2,z1,aa
3,z1,aa

说明：果然，现在的“,”前面加上了转义字符“\”。而刚才的引用符“””却没有被转义，因为它已经没有什么歧义，不需要被转义

注意：SELECT…INTO OUTFILE...产生的输出文件如果在目标目录下有重名文件，将不会创建成功，源文件不能被自动覆盖

 

方法 2：用 mysqldump 导出数据为文本。

mysqldump –u username –T target_dir dbname tablename [option]

其中 option 参数可以是以下选项：

1) --fields-terminated-by=name（字段分隔符）；
2) --fields-enclosed-by=name  字段引用符，比如每个字段用双引号括起来；
3) --fields-optionally-enclosed-by=name（字段引用符，只用在 char、varchar 和 text 等字符型字段上
4) --fields-escaped-by=name（转义字符）；
5) --lines-terminated-by=name（记录结束符）。

常用参数

 --compact ：使得输出结果简洁，不包括默认选项中的各种注释，例如例中对 test 数据库中的表 emp 进行简洁导出 mysqldump -uroot -p  --compact test emp >a

 -F --flush-logs（备份前刷新日志）：加上此选项后，备份前将关闭旧日志，生成新日志。使得进行恢复的时候直接从新日志开始进行重做，大大方便了恢复过程

 -l --lock-tables（给所有表加读锁）：可以在备份期间使用，使得数据无法被更新，从而使备份的数据保持一致性，可以配合-F选项一起使用。

注意：

MyISAM 存储引擎在备份的时候需要加上-l 参数，表示将所有表加上读锁，在备份期间，所有表将只能读而不能进行数据更新。

但是对于事务存储引擎（InnoDB 和 BDB）来说，可以采用更好的选项--single-transaction，此选项将使得 InnoDB 存储引擎得到一个快照（Snapshot），使得备份的数据能够保证一致性。

例子，采用 mysqldump 生成指定分隔符分隔的文本：

mysqldump -uroot -T /tmp test emp --fields-terminated-by ',' --fields-optionally-enclosed-by '"'

输出结果如下

more /tmp/emp.txt

1,"\\\"#,#,!aa","aa"
2,"z1","aa"
3,"z1","aa"

 

注意：

1）ubuntu测试中会出错，解决方法如下。将输出目录改为/var/lib/mysql-files/. 例子中test是库名，emp是表名，如果不写则备份全部表

mysql> mysql> select * from person into outfile '/tmp/x.txt' fields terminated by ",";
ERROR 1290 (HY000): The MySQL server is running with the --secure-file-priv option so it cannot execute this statement
mysql> show global variables like "%secure%";
+--------------------------+-----------------------+
| Variable_name            | Value                 |
+--------------------------+-----------------------+
| require_secure_transport | OFF                   |
| secure_auth              | ON                    |
| secure_file_priv         | /var/lib/mysql-files/ |
+--------------------------+-----------------------+

2）如果非要用/tmp，则需要做如下操作 (测试了下又不行了...)

在/etc/apparmor.d/usr.sbin.mysqld文件中加入一行代码如下
/data/export/** rw, 保存退出后
/etc/init.d/apparmor reload  #重新载入配置 
再次导出即可。