How can I modify the Mysql character set? The following describes how to modify the Mysql Character Set of used data tables in detail. We hope you can learn more about the Mysql character set.
Environment: the character set is not correctly set at the beginning of the application. After running for a period of time, it is found that the data cannot meet the requirements and needs to be adjusted, you need to modify the character set. You cannot directly modify the character set by running the "alter database character set ***" or "alter table tablename character set ***" command. Neither of these two commands updates the character set of an existing record, it only takes effect for newly created tables or records.
How do I adjust the character set of existing records?
The following simulation is the process of modifying the database of the latin1 character set to the database of the GBK character set:
(1) Export the table structure
Mysqldump-uroot-p -- default-character-set = gbk-d databasesename> createdb. SQL
Here, -- default-character-set = gbk indicates the character set used to set the connection.-d indicates that only the table structure is exported and no data is exported.
(2) manually modify the character set in the table structure definition in createdb. SQL as the New Character Set
(3) ensure that records are no longer updated and all records are exported
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 the rows in the table from the server one row at a time, instead of retrieving all rows. It is cached in the memory before the input.
-- Extended-insert: Use the multiline Insert syntax that includes several values lists to make the dump file smaller and the reload file faster.
-- No-create-info: the create table statement for each dump table is not required to be re-created.
-- Default-character-set = latin1: export all data according to the original character set. In this way, all Chinese characters in the exported file are visible and will not be saved as garbled characters.
(4) Open data. SQL and change SET NAMES latin1 to SET NAMES gbk.
(5) use a new character set to create a new database
Create database newdatabasename default charset gbk;
(6) create a table and execute createdb. SQL
Mysql-uroot-p newdatabasesname <createdb. SQL
(7) import data and execute data. SQL
Mysql-uroot-p newdatabasename <data. SQL
How to set the current time as the default value for MySQL
Simple MySQL large table backup method
Combination of multiple MySQL table result sets
Implementation of MySQL table sharding
How to copy data from a MySQL table to a new table