Summary of MySQL garbled problem

The previous time in the cross-version of the migration of MySQL data is always the more garbled problem, now summarize.

thought: Data garbled is actually the data in the write-in and read out using a different character encoding rules, to solve garbled is the uniform character encoding rules, also known as the unified character set. For example, database tables use ASCII encoding to store data, and using gb2312 when inserting or reading data must be garbled.

In MySQL, the character set is multi-layered, with the MySQL default character set--the database character set and the table character set. If the database character set is not specified in the creation data, the newly created library inherits the MySQL default character set, and similarly, if the new table does not specify a character set, it inherits the database character set to which it belongs. When building a new MySQL service, be sure to set the default character set in advance, MySQL default is latin1, in order to be compatible with the multi-lingual recommended use UTF8.

MySQL Default character set specifies the method:

To modify the MY.CNF configuration file:

[Mysqld]

Default-character-set=utf8 #适用于5.1 and Previous versions

Character-set-server=utf8#适用于5.5 version

Check the current character Set settings:

SHOW VARIABLES like ' character% '; #执行此命令可查询当前环境中关于字符集的设置, as follows:

Character_set_client: Client. specified by the Default-character-set in the configuration file, if not specified, the character set of the current shell terminal is read and remains with it.

Character_set_connection: Current connection. When the client and server character sets are not at the same time, this connection will prevail. specified by the Default-character-set in the configuration file, if not specified, the character set of the current shell terminal is read and remains with it.

Character_set_database: Database character set. Specified when a table is specified by a configuration file or built by a database. specified by server-side parameter Character_set_server

Character_set_filesystem: Character Set file system

Character_set_results: Returns the result. specified by the Default-character-set in the configuration file, if not specified, the character set of the current shell terminal is read and remains with it.

Character_set_server: Server character Set, configuration file designation, or Database build table designation. specified by server-side parameter Character_set_server

by default, Character_set_client, Character_set_connection, and character_set_results are consistent with the system's character set (/etc/sysconfig/i18n).

garbled when querying data:

1, in the query data garbled when the first to query the current database and even the current character encoding of the table, the command is as follows:

SHOW CREATE DATABASE DatabaseName; #查询数据库编码规则

SHOW CREATE TABLE Databasename.tablename; #查询表的编码规则

2. Modify the encoded character set of the feedback data when the Select query is modified according to the character set used by the Library or table encoding:

SET NAMES CharacterSet; #设定NAMES值, this command modifies Character_set_client, Character_set_connection, and character_set_results at the same time. This setting takes effect temporarily.

or modify the MY.CNF configuration file:

[Client]

Default-character-set=characterset #配置文件中修改永久生效

garbled after inserting data:

When the data is copied into the same query, the first thing is to ensure that the encoding of the data is the same as the encoding rules of the table. Can be implemented by modifying the names. or specify the encoding when using script import, take gb2312 as an example:

SET NAMES gb2312; Or:

MySQL--default-character-set=gb2312 DatabaseName < databasename.sql

Migrating data:

To migrate data from one MySQL server to another MySQL server, you can export data through mysqldump, copy the data to the new server, and then import it.

1. Be sure to review the encoding rules for the pre-exported database tables before exporting the data:

SHOW CREATE TABLE Databasename.tablename;

2. Specify the character set export according to the rule:

Mysqldump-hhostip-uusername-p--default-character-set=characterset DatabaseName > Databasename.sql

3. After the data is copied to the new server, specify the character set of the new database table when importing:

MySQL--default-character-set=newcharacterset DatabaseName < databasename.sql

Attention:

1) before importing data into the new MySQL server, be sure to check that the storage engine and character set specified in the Databasename.sql script file are consistent with the new server, and that the SED command can be used to modify it.

2) mysqldump The exported script file does not have a new database, you need to manually create the database in advance, and specify the database on import;

This article is from the "Silver Kay Blog" blog, make sure to keep this source http://yinkai.blog.51cto.com/3813923/1735820

Summary of MySQL garbled problem