How to encode and convert mysql characters

Source: xiaoych. javaeye. comblog148704 is also a loyal user of mysql. mysql was used since mysql 3, and mysql 5.1 has been used since now. Seeing mysql 1.1 Changes, I feel that mysql is becoming more and more powerful, this is a good news that we cannot afford oracle users! If you remember correctly,

Source: http://xiaoych.javaeye.com/blog/148704 I am also a loyal user of mysql, from mysql 3 began to use mysql, until now began to use version 5.1, see mysql 1.1 point of change, I feel that mysql is becoming more and more powerful, which is why we cannot afford to use oracle users! If you remember correctly,

Source: http://xiaoych.javaeye.com/blog/148704

I am also a loyal user of mysql. I started using mysql from mysql 3, and now I am using mysql 5.1. Seeing mysql 1.1 Changes, I feel that mysql is becoming more and more powerful, this is a good news that we cannot afford oracle users!

If you remember correctly, after mysql4.0 is upgraded to 4.1, the character set concept is added. If you migrate data from mysql4.0 or earlier to mysql4.1 or later, Chinese characters may be garbled. Since mysql was previously encoded as latin1 SW (as if it was a Swedish Character Set-mysql was developed by Swedes, it is estimated that the internationalization problem was not considered at the time ), the encoding we use is generally gbk or utf8. So how can we migrate Chinese characters from latin1 encoded tables to gbk or utf8 encoded tables? Directly changing the table encoding does not have any effect. For more information, mysql does not transcode the existing data in the table when changing the table encoding.

There are a lot of materials and programs for transcoding on the Internet, which is not very convenient. To compare BT, we use PHP to read a row from the mysql source table, use PHP for transcoding, and then find the target table. Think about how it works. But the performance is terrible ...... Small tables can also do this. If a table with millions of rows of records is not equivalent to a few hours, it is a waste of time.

Well, it's all nonsense. Let's make a contribution to my research:

First, go to mysql/bin and use mysqldump to execute the following command:

Mysql code
Mysqldump -- u = root-p -- default-character-set = latin1 -- set-charset = utf8 -- skip-opt -- result-file = c:/mytable. SQL mydb mytable

Where: root is the database login name, latin1 is the encoding of the source table (that is, the table to be transcoded), utf8 is the encoding to be converted to, c:/mytable. SQL is the file for storing exported data (for temporary use). mydb is the database (schema) to which the source table belongs, and mytable is the name of the source table.

Run this command and you will be prompted to enter the password. after entering the correct password, you will start exporting data. After all the data is exported, you can use ue and other tools to open it. Then you can see that the data encoding has changed.

Then you need to make some changes to this file. There is a table creation statement at the beginning of the file. Similar:
Mysql code
Create table 'mytable '(
'Tableid' bigint (20) unsigned not null,
'C1 'int (10) unsigned not null default '0 ',
'C2 'int (10) unsigned not null default '0 ',
Primary key ('tableid ')
);

Note that there is nothing missing from the last semicolon: engine = myisam default charset = utf8 engine and charset make sense. Everyone knows how to add this section. The result may be as follows:

Mysql code
Create table 'mytable '(
'Tableid' bigint (20) unsigned not null,
'C1 'int (10) unsigned not null default '0 ',
'C2 'int (10) unsigned not null default '0 ',
Primary key ('tableid ')
) Engine = myisam default charset = utf8;

Here, the engine and charset are changed to the desired items, such as innodb gbk...

Save the file. (If you use a tool such as UE, it won't wait too long even if the file is large. If you open it with Notepad ...... Congratulations !)

In this way, half of the operation is successful, and the rest of the work only needs to import the data with the converted code.

Rename the original table. One is for backup, and the other is to prevent the table from being imported.

Then enter mysql/bin and run:

Mysql code
Mysql-u root-p mydb <c:/mytable. SQL

After the password is entered, the program starts to work. After a while, the new table will come out...

Mission complete!