在做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、准备好数据库、数据表、测试数据
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
|
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、执行导出数据:
1
2
3
4
5
|
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
1
2
3
4
5
6
|
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打开:
1
|
vim
/
tmp
/
dump_table_from
.
txt
|
输入\t搜索,发现只匹配到了隐藏的真正的TAB;
输入\\t搜索,才搜到了直接显示出来的\t;
搜索\n和\\n,效果相同;
这就说明,导出的文件中,字段中如果包含了\t和\n,会自动变成\\t和\\n的;
2、使用程序处理
使用Python写一个测试程序,看能不能正常按行读取和按tab分割:
打开Python命令行:
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
|
>>>
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、直接将文件导入到一个新表
执行导入:
1
2
3
4
5
6
7
8
9
|
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://www.crazyant.net/1901.html,转载请注明来源。