MySQL database to solve Chinese garbled problem

After the installation of MySQL, if stored in Chinese, and then read out when the garbled problem occurs. There are hundreds of different character sets now, all defined by companies or organizations. We should use a character set that can hold all the characters in all languages of the world, so that no more garbled problems will occur. This is now the universal UTF-8. To understand the encoding you should distinguish between the character set and the encoding format are the two aspects of the same problem.

The reason for garbled is that different character sets are used when storing and reading.

It is recommended to modify the encoding format before building the library:

first, go to MySQL (mysql-uroot-p), view the current database character set (status;)

Second , #vim/etc/mysql/my.cnf

Three

[Client]
Default-character-set=utf8

[Mysqld]
Default-storage-engine=innodb
Character-set-server=utf8
Collation-server=utf8_general_ci

Iv. restarting MySQL (/etc/init.d/mysql stop/etc/init.d/mysql start)

Above is the operation before the building, if the database is in use, and then found that the encoding format is not what should be done?

First, the export table structure: mysqldump-uroot-p--default-character-set=gbk-d databasesname > Createtable.sql

Second, manually modify the table structure in the Createtable.sql defines the character set as the new character set.

Third, 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: When a large table is dumped, the rows in the table are retrieved, not all rows at one time, and the output is stored in the cache.

--no-create-info: do not export the build table statement.

--extended-insert: uses a multiline insert syntax that contains several values lists.

--default-character-set=latin1: Export According to the original character set so that all Chinese is visible in the export file.

Open Data.sql, change set NAMES latin1 to set NAMES GBK

V. Create a new database with the new character set.

Create database DatabaseName Defaule charset GBK;

VI. Create a table and execute Createtable.sql

Mysql-uroot-p DatabaseName < Createtable.sql

VII. Import data

Mysql-uroot-p DatabaseName < Data.sql

Here are some of our custom coding standards:

1) gb2312-80:1980-year release, double-byte coded character set.

2) gb13000:1993 published, use very little, just a formal standard.

3) gbk:1995 was released as a result of being supported by Windows 95.

4) gb18030:2000 year release, not much heard.

The following is a comparison of common character sets:

1) ASCII, fixed-length, single-byte 7-bit encoding, the earliest foundation-breaking code.

2) GBK, fixed length, double-byte encoding, self-sustaining wide.

3) UTF-8, indefinite length, 1-4 byte encoding, Internet and UNIX widely supported Unicode character set.

4) UTF-16, indefinite length, Java and other internal use of the character set.

How to choose a character set:

1) in multi-country use, internationalization of the stylish, using Unicode, that is, UTF-8.

2) If the data in the database is imported, consider the compatibility of the database character set with the existing data.

3) The database only supports Chinese, the data is large, the performance requirements are high, can use double-byte encoded GBK, because compared to UTF-8 3 bytes, space consumption less.

4) If the database to do a large number of character operations, such as comparative sorting, and so on, the fixed length is faster than the indefinite length.

5) The character set format commonly used by clients.

MySQL supports multiple character sets, and different character sets can be developed on the same server, the same database, or even different fields of the same table.

You can use the command show character set only; Insert card to see the supported character sets. Relative character sets also have the problem of proofing rules, you can check the relevant information.

Settings for the MySQL character set:

There are four levels: server-level, database-level, table-level, and field-level.

1) setting of the database character set: Set in My.cnf (Windows My.ini): [Mysqld]character-set-server=utf-8 Set in Startup options: Mysqld--character-set-server =utf-8 can also be made at compile time.

2) database character set and proofing rules can be made when creating a database, or can be modified by the ALTER DATABASE command after creation.

3) The setting of the label is the same as above, to display the table's character set and setting rules can be viewed through the show create TABLE command.

MySQL database to solve Chinese garbled problem