MySQL changes the character set of an existing data table, preserving the original data content

MySQL changes the character set of an existing data table, preserving the original data content

Original URL: http://blog.csdn.net/learn_2/article/details/6460370

Environment: In the beginning of the application does not have the correct character set, after a period of time to find that there is no need to meet the needs of the adjustment, and do not want to discard the time of the data, then the character set needs to be modified. The modification of the character set cannot be done directly through the ALTER DATABASE character set or the ALTER TABLE tablename Character Set * * * command, both of which do not update the existing recorded character set, but only for the new The created table or record takes effect.

So what do I need to do to adjust the set of recorded characters?
The following simulates the process of modifying a database of the latin1 character set to a database of the GBK character set:
(1) Export table structure
Mysqldump-uroot-p--default-character-set=gbk-d Databasesename > Createdb.sql
where--DEFAULT-CHARACTER-SET=GBK indicates which character set to connect to,-D means that only the table structure is exported, no data is exported
(2) Manually modify the character set in the table structure definition in createdb.sql to the new character set
(3) to ensure that records are no longer updated, export all records
Mysqldump-uroot-p--quick--no-create-info-- Extended-insert--default-character-set=latin1 databasename > Data.sql
--quick: This option is used to dump large tables. It forces mysqldump to retrieve rows from the server one row at a time, rather than retrieving all the rows, before the output caches it in memory
--extended-insert: Use a multiline insert syntax that includes several values lists, which makes the dump file smaller, Reload files faster
--no-create-info: Disdain to recreate the CREATE TABLE statement for each dump table
--default-character-set=latin1: Export all data according to the original character set, so that the exported file , all Chinese is visible and will not be stored as garbled
(4) Open Data.sql, modify set NAMES latin1 to set NAMES GBK
(5) Create a new database with the new character set
Newdatabasename default CharSet GBK;
(6) CREATE TABLE, execute Createdb.sql
mysql-uroot-p newdatabasesname < Createdb.sql
(7) Import data, execute Data.sql
MySQL- Uroot-p Newdatabasename < Data.sql

MySQL changes the character set of an existing data table, preserving the original data contents