Mysql database Character set conversion and version upgrade/downgrade tutorial

Recently Discuz released a new version, free, with more people, used to use other forum programs and DISCUZ2.5/ 3.0 of the conversion or upgrade to discuz4.0, can be seen discuz as the Chinese Development of PHP forum program, is indeed very excellent, when we were ecstatic, also encountered some problems

See a lot of users reflect the conversion is garbled after the situation, the main reason for this phenomenon is that such users are mysql4.1 above version. Here is a description, hope that the problem of friends can patiently read this document!!!

At the beginning of MySQL 4.1, support for multiple languages has changed a lot (which has led to problems). Although most of the places (including personal use and host providers), MySQL 3, 4.0 still dominate; but MySQL 4.1 or even 5.0 is the MySQL official recommendation database, already has the host provider to begin to provide and will be more and more; because latin1 in many places (below will be detailed description What is the specific place as the default character set, successfully blinded many of the PHP program developers and users, masking the language environment in the Chinese and other problems will occur.

MySQL 4.1 began to the multilingual character set points more detailed, so that the database migration, or DZ Forum upgrade to 4.0 after (dz4.0 began using GBK or UTF-8 encoding) garbled problem.

MySQL 4.1 's character set support (Character set Support) has two aspects: Character set (Character set) and Sort method (collation). The support for character sets is refined to four tiers: Servers (server), databases (database), Data tables (table), and connections (connection).

The settings for viewing the system's character set and sorting can be set by using the following two commands:

QUOTE:

mysql> show VARIABLES like ' character_set_% ';
+--------------------------+----------------------------+
| variable_name | Value |
+--------------------------+----------------------------+
| character_set_client | latin1 |
| character_ set_connection | Latin1 |
| character_set_database | latin1 |
| character_set_results | latin1 |
| character_set_server | latin1 |
| character_set_system | utf8 |
| character_sets_dir |/usr/share/mysql/charsets/|
+--------------------------+----------------------------+
7 rows in Set (0.00 sec)
Mysql> show VARIABLES Like ' collation_% ';
+----------------------+-------------------+
| variable_name | Value |
+----------------------+-------------------+
| collation_connection | latin1_swedish_ci |
| collation_ Database | Latin1_swedish_ci |
| collation_server | latin1_swedish_ci |
+----------------------+-------------------+
3 rows in Set (0.00 sec)

MySQL 4.1 for the designation of character sets can be refined to a machine installed on MySQL, where a database of which a table, which a column, should use what character set. However, traditional WEB programs do not use a complex configuration when creating databases and datasheets, and they use the default configuration, so where does the default configuration come from?

When compiling MySQL, a default character set is specified, which is latin1;

When installing MySQL, you can specify a default character set in the configuration file (My.ini), and if not specified, the value inherits from the compile-time designation;

When starting mysqld, you can specify a default character set in the command-line arguments, and if not specified, the value inherits from the configuration file;

At this point the character_set_server is set to the default character set;

When a new database is created, the character set of the database is set to Character_set_server by default unless explicitly specified;

When a database is selected, the Character_set_database is set to the default character set for the database;

When a table is created in this database, the default character set of the table is set to Character_set_database, which is the default character set of the database;

When you set a column in a table, the default character set for this column is the default character set for the table unless explicitly specified;

This character set is the character set of the actual data stored in the database, and the content of the mysqldump is this character set;

When we access the MySQL database through PHP in the way we used to, even if we set the default character set of the table to UTF8 and send the query via UTF-8 encoding, you will find that the database is still garbled. The problem is on the connection connection layer.

The most convenient way to get the "right" storage and the "right" results is to do it before all query starts:

SET NAMES ' GBK ';

Where GBK is the database character set.

It corresponds to the following three-sentence instruction:

SET character_set_client = gbk;
SET character_set_results = gbk;
SET character_set_connection = gbk;