MySQL导入导出数据时遇到Tab符号和换行符号怎么办?

在做ETL(下载、转换、导入)开发的时候,经常会遇到从MySQL中导出数据,经过计算后再导入到MySQL的场景。

那么有一个很难绕过的问题,如果源MySQL的字段中,包含了\t、\n特殊字符,该怎么办?

因为导出文件时,默认是按照\t分割字段、\n分割行,现在字段中出现了\t和\n,这不是乱了吗?

同时导入文件时,load data可以指定fields和lines的分隔符,默认情况都是\t和\n,可是现在字段中有\t和\n,这不乱了吗?

最直观想到的办法,是导出的时候,在select语句中,使用mysql的replace函数,将\t和\n替换成普通字符串,使用程序处理,load到库里之后,在使用replace替换。但是如果数据量很大的话,替换\t和\n很不现实。

其实,这个问题真的是个问题吗?

答案是:根本不用考虑\t和\n

1)MySQL的select导出时,会自动把\t和\n转义成\t和\n;

2)在shell、Python的代码中,按行读取,程序遇到\n,会自动略过的;

3)在shell、Python的代码中,按\t分割,程序遇到\t,会自动略过的;

4)load data导入数据的时候,遇到了\t和\n,也当成普通字符处理;

做一个测试就知道了:

1、准备好数据库、数据表、测试数据

CREATE DATABASE test /*!40100 DEFAULT CHARACTER SET utf8 */

use test;
set names utf8;

CREATE TABLE table_from (
  id INT(11) NOT NULL AUTO_INCREMENT COMMENT 'ID',
  name VARCHAR(500) DEFAULT NULL COMMENT '名称',
  remark VARCHAR(500) DEFAULT NULL COMMENT '备注',
  KEY id (id)
) ENGINE=INNODB DEFAULT CHARSET=utf8 COMMENT '导入测试的来源表';

CREATE TABLE table_to (
  id INT(11) NOT NULL AUTO_INCREMENT COMMENT 'ID',
  name VARCHAR(500) DEFAULT NULL COMMENT '名称',
  remark VARCHAR(500) DEFAULT NULL COMMENT '备注',
  KEY id (id)
) ENGINE=INNODB DEFAULT CHARSET=utf8 COMMENT '导入测试的目的表';

truncate table table_from;
truncate table table_to;
insert into table_from values(1,'name1','我们都是屌丝程序员');
insert into table_from values(2,'name2','我们都是屌丝程序员\n');
insert into table_from values(3,'name3','我们都是\t屌丝程序员');
insert into table_from values(4,'name4','我们都是\t屌丝程序员\n');

2、执行导出数据:

mysql -h127.0.0.1 -uroot -p123456 -P3306 -e "
    use test; 
    set names utf8; 
    select * from table_from;
" > /tmp/dump_table_from.txt

查看一下/tmp/dump_table_from.txt

more /tmp/dump_table_from.txt 
id      name    remark
1       name1   我们都是屌丝程序员
2       name2   我们都是屌丝程序员\n
3       name3   我们都是\t屌丝程序员
4       name4   我们都是\t屌丝程序员\n

发现导出的数据中,出现了\t和\n,但是这俩字符是直接显示出来了,而不是变成了真正的TAB和换行。

我们用vim打开:

vim /tmp/dump_table_from.txt

输入\t搜索,发现只匹配到了隐藏的真正的TAB;

输入\t搜索,才搜到了直接显示出来的\t;

搜索\n和\n,效果相同;

这就说明,导出的文件中,字段中如果包含了\t和\n,会自动变成\t和\n的;

2、使用程序处理

使用Python写一个测试程序,看能不能正常按行读取和按tab分割:

打开Python命令行:

>>> for row in open("/tmp/dump_table_from.txt"):
...     print "#"+row+"#"                       
...     print "#".join(str(row).split("\t"))      
... 
#id     name    remark
#
id#name#remark

#1      name1   我们都是屌丝程序员
#
1#name1#我们都是屌丝程序员

#2      name2   我们都是屌丝程序员\n
#
2#name2#我们都是屌丝程序员\n

#3      name3   我们都是\t屌丝程序员
#
3#name3#我们都是\t屌丝程序员

#4      name4   我们都是\t屌丝程序员\n
#
4#name4#我们都是\t屌丝程序员\n

>>>

可以看到,程序会正常的按\n分割行,按\t分割字段,并没有按照字段内部出现的\t和\n分割,因为他们都是\t和\n。

3、直接将文件导入到一个新表

执行导入:

mysql -h127.0.0.1 -uroot -p123456 -P3306 -e "
    use test; 
    set names utf8; 
    load data local 
        infile '/tmp/dump_table_from.txt' 
        into table table_to 
        fields terminated by'\t' 
        lines terminated by'\n' ignore 0 lines (id,name,remark)";

执行命令没有异常,打开目标表,看下结果,发现跟源表一模一样;

最终结论:

1、使用select导出MySQL数据的时候,字段里的\t和\n,会自动的替换成\t和\n;

2、在使用shell、Python读取文件的时候,如果遇到了\n,不会作为行分隔符;使用split('\t')函数分割的时候,如果遇到了\t,会略过;

3、使用load data向MySQL导入数据的时候,里面的\t和\n,不会作为字段分隔符和行分隔符;

 

本文地址:http://crazyant.net/1901.html,转载请注明来源

相关推荐

2 thoughts on “MySQL导入导出数据时遇到Tab符号和换行符号怎么办?”

    • 但是mysql导出的\r并不会变成\\r,当导出数据被linux shell读取时,包含\r的行不会被分隔成两行,但是当被python 读取时候,\r会被分隔成两行,因为python默认的行分隔符包含了\r,幸运的是python open函数有一个newline参数可以指定行与行之间的分隔符,这时候,可以只指定”\t”。

      回复

Leave a Comment