数据迁移(MYSQL--ORACLE)中碰到的乱码问题

MYSQL字符集UTF8,ORACLE字符集GBK
LINUX 5.3
MYSQL 5.1
ORACLE 9208
数据行数1800W

[@more@]

基于这个数据量,我们首先想到的是将MYSQL数据DUMP到文本文件,再用SQLLOAD泵进ORACLE.

但我们在第一步(MYSQL数据DUMP到文本文件,)就出现了问题. 中文显示为乱码.

所以我们把首要问题先解决: 如何让MYSQL正确地DUMP到文本文件?

其实这个归根还是字符集的问题. 但在这个场景,我们还要考虑到MYSQL对不同的输出方式也有不同的字符集转换模式.

首先来我们来看一下,DUMP数据有多种方法:
1. set names gbk; select ... into outfile '/tmp/a1.txt' from test.t1;
2. mysql -uroot -h127.0.0.1 --default-character-set=gbk -e " select ... from test.t1" >>/tmp/a1.txt
3. mysqldump -uroot -h127.0.0.1 --tab "/tmp" --fields-terminated-by='&&&' --lines-terminated-by='$$$$$' --default-character-set=utf8 test t1

创建测试表:
set names gbk;
CREATE TABLE `t1` ( `col0` varchar(100) , `col1` varchar(100) ) ENGINE=MyISAM DEFAULT CHARSET=utf8 ;
insert into t1 values ('中国','1aaaaaa');
select * from t1;

下面我们分别来看一下,上面说的三种方法能不能正确DUMP中文到文本文件

1. set names gbk; select * into outfile '/tmp/a1.txt' from test.t1;
========================================================================
root@127.0.0.1 : (none) 15:35:26> use test;
Database changed
root@127.0.0.1 : test 15:35:27> set names gbk;
Query OK, 0 rows affected (0.00 sec)

root@127.0.0.1 : test 15:35:30> select * from t1;
+------+---------+
| col0 | col1 |
+------+---------+
| 中国 | 1aaaaaa |
+------+---------+
1 row in set (0.00 sec)

root@127.0.0.1 : test 15:35:33> select * into outfile '/tmp/a1.txt' from test.t1;
Query OK, 1 row affected (0.00 sec)

root@127.0.0.1 : test 15:35:53> system cat /tmp/a1.txt
涓?浗 1aaaaaa

root@127.0.0.1 : test 15:35:59> system hexdump /tmp/a1.txt
0000000 b8e4 e5ad bd9b 3109 6161 6161 6161 000a
000000f
========================================================================
注解:用MYSQL CLIENT GBK看能正常显示中文,但OUTFILE里存的却是UTF8的编码数据.
在这里猜测是:
当数据返回给MYSQL客户端的时候, 数据经过character_set_results=gbk的转换.
当数据返回给OUTFILE的时候,是直接DUMP数据.(经过测试,character_set_results不管设成什么,都不影响OUTFILE生成的结果)

2. mysql -e "select .." >> /tmp/a2.txt
========================================================================
[root@PerfTestDB1 tmp]# mysql -uroot -h127.0.0.1 -N -s --default-character-set=gbk -e " select * from test.t1" >/tmp/a2.txt
[root@PerfTestDB1 tmp]# more /tmp/a2.txt
中国 1aaaaaa
[root@PerfTestDB1 tmp]# hexdump /tmp/a2.txt
0000000 d0d6 fab9 3109 6161 6161 6161 000a
000000d
========================================================================
注解:在这里能正常DUMP出来,是因为返回给MYSQL CLIENT的时候,已经经过character_set_results=gbk的转换.
这个相当于第一种方法里的前半部分,直接查看(select * from t1;)

3. mysqldump (这种方式将会产生文件: .sql--建表语句 .txt--数据)
========================================================================
[root@PerfTestDB1 tmp]# mysqldump -uroot -h127.0.0.1 --tab "/tmp" --fields-terminated-by='&&&' --lines-terminated-by='$$$$$' --default-character-set=gbk test t1
[root@PerfTestDB1 tmp]# more t1.txt
涓?浗&&&1aaaaaa$$$$$
[root@PerfTestDB1 tmp]# hexdump t1.txt
0000000 b8e4 e5ad bd9b 2626 3126 6161 6161 6161
0000010 2424 2424 0024
0000015
========================================================================
注解: 不管上面的--default-character-set设成GBK/UTF8/LATIN1,导出的结果都是一致的.
其实这也说明这种方式也是直接将表的真实数据编码直接DUMP出来,而没有经过转换.
为了进一步证明上述的说法.我STRACE了一下第2,第3两种方法.
strace mysql -uroot -h127.0.0.1 -N -s --default-character-set=gbk -e " select * from test.t1" > /tmp/mysql.log
strace mysqldump -uroot -h127.0.0.1 --tab "/tmp" --fields-terminated-by='&&&' --lines-terminated-by='$$$$$' --default-character-set=gbk test t1 >>/tmp/mysqldump.log

通过查看日志文件:/tmp/mysql.log /tmp/mysqldump.log ,发现在mysql.log中.有这么一段:
-----------------------------------------------------
munmap(0xb7f4e000, 4096) = 0
stat64("/usr/share/mysql/charsets/Index.xml", {st_mode=S_IFREG|0755, st_size=18173, ...}) = 0
open("/usr/share/mysql/charsets/Index.xml", O_RDONLY|O_LARGEFILE) = 3
read(3, "<?xml version='1.0' encoding="ut"..., 18173) = 18173
close(3)
-----------------------------------------------------
我想这正是MYSQL在做字符集转换. 而在mysqldump.log中没有找到.

再进一步跟踪另一种MYSQLDUMP备份方法(导成SQL语句).
strace mysqldump -uroot -h127.0.0.1 --default-character-set=gbk test t1 >> mysqldump1.log
你是不是发现在这里又出现了上面那一段? (可以搜索:/usr/share/mysql/charsets/Index.xml)

4.小结.

经过以上的测试. 那么现在我们可以知道,想要中文正确的显示在文本文件里.有两种方法:
1) mysql -uroot -h127.0.0.1 -N -s --default-character-set=gbk -e " select * from test.t1" >/tmp/a2.txt
2) select convert(name USING gbk) into outfile '/tmp/a21.txt' from test.t1 ;

来自 “ ITPUB博客 ” ，链接：http://blog.itpub.net/703656/viewspace-1028582/，如需转载，请注明出处，否则将追究法律责任。

转载于:http://blog.itpub.net/703656/viewspace-1028582/