Shulou(Shulou.com)05/31 Report--

This article mainly explains "MYSQL data migration to ORACLE encountered in the garbled code problem solution," interested friends may wish to take a look. The method introduced in this paper is simple, fast and practical. Let's take you to learn "MYSQL data migration to ORACLE encountered in the garbled problem solution"!

MYSQL character set UTF8,ORACLE character set GBK

LINUX 5.3

MYSQL 5.1

ORACLE 9208

Number of rows of data 1800W

Based on this data volume, our first thought is to dump MYSQL data into a text file and pump it into ORACLE with SQLLOAD.

But we had a problem with the first step (MYSQL data dump to text file,). Chinese is displayed as garbled code.

So let's get to the first question: How do I get MYSQL to DUMP correctly to a text file?

In fact, this is ultimately a matter of character sets. But in this scenario, we also consider that MYSQL has different character set conversion modes for different output modes.

First let's look at how DUMP data can be used:

1. set names gbk; select ... into outfile '/tmp/a1.txt' from test.t1;

2. mysql -uroot -h227.0.0.1 --default-character-set=gbk -e " select ... from test.t1" >>/tmp/a1.txt

3. mysqldump -uroot -h227.0.0.1 --tab "/tmp" --fields-terminated-by='&&&' --lines-terminated-by='$$$$$' --default-character-set=utf8 test t1

To create a test table:

set names gbk;

CREATE TABLE `t1` ( `col0` varchar(100) , `col1` varchar(100) ) ENGINE=MyISAM DEFAULT CHARSET=utf8 ;

insert into t1 values ('China ',' 1aaaaa ');

select * from t1;

Let's take a look at each of the three methods mentioned above. Can the DUMP Chinese to text files be correct?

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 |

+------+---------+

| China| 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

What?浗 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

========================================================================

Note: MYSQL CLIENT GBK can display Chinese normally, but OUTFILE stores encoded data of UTF8.

The guess here is:

When the data is returned to the MYSQL client, it is transformed by character_set_results=gbk.

When data is returned to OUTFILE, it is direct DUMP data. (tested,character_set_results does not affect OUTFILE results regardless of what it is set to)

2. mysql -e "select .. " >> /tmp/a2.txt

========================================================================

[root@PerfTestDB1 tmp]# mysql -uroot -h227.0.0.1 -N -s --default-character-set=gbk -e " select * from test.t1" >/tmp/a2.txt

[root@PerfTestDB1 tmp]# more /tmp/a2.txt

China 1aaaaa

[root@PerfTestDB1 tmp]# hexdump /tmp/a2.txt

0000000 d0d6 fab9 3109 6161 6161 6161 000a

000000d

========================================================================

Note: DUMP can be returned correctly here because character_set_results=gbk has been converted to MYSQL CLIENT.

This corresponds to the first half of the first method, directly viewing (select * from t1;)

3. mysqldump (this will result in the file: .sql--table statement.txt--data)

========================================================================

[root@PerfTestDB1 tmp]# mysqldump -uroot -h227.0.0.1 --tab "/tmp" --fields-terminated-by='&&&' --lines-terminated-by='$$$$$' --default-character-set=gbk test t1

[root@PerfTestDB1 tmp]# more t1.txt

What?浗&&&1aaaaaa$$$$$

[root@PerfTestDB1 tmp]# hexdump t1.txt

0000000 b8e4 e5ad bd9b 2626 3126 6161 6161 6161

0000010 2424 2424 0024

0000015

========================================================================

Note: The derived results are consistent regardless of whether the above--default-character-set is set to GBK/UTF8/LATIN1.

In fact, this also shows that this way is directly the table of the real data encoding directly DUMP out, without conversion.

To further prove the above statement. I STRACE the second and third methods.

strace mysql -uroot -h227.0.0.1 -N -s --default-character-set=gbk -e " select * from test.t1" > /tmp/mysql.log

strace mysqldump -uroot -h227.0.0.1 --tab "/tmp" --fields-terminated-by='&&&' --lines-terminated-by='$$$$$' --default-character-set=gbk test t1 >>/tmp/mysqldump.log

By looking at the log file:/tmp/mysql.log/tmp/mysqldump. log, you can find it in mysql.log. There is this passage:

-----------------------------------------------------

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, "> mysqldump1.log

Did you notice that the above paragraph appeared again here? The requested URL/usr/share/mysql/charsets/index.xml was not found on this server.

4. Summary.

After the above tests. So now we know that we want Chinese displayed correctly in text files. There are two ways:

1) mysql -uroot -h227.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 ;

At this point, I believe we have a deeper understanding of "MYSQL data migration to ORACLE encountered in the garbled code problem solution," may wish to actually operate some bar! Here is the website, more related content can enter the relevant channels for inquiry, pay attention to us, continue to learn!

Welcome to subscribe "Shulou Technology Information " to get latest news, interesting things and hot topics in the IT industry, and controls the hottest and latest Internet news, technology news and IT industry trends.