MySQL 5.1 Chinese Character Set

1: MySQL character set support
MySQL character set support has two aspects: Character Set and collation ).
The support for character sets is refined to four levels: Server, database, table, and connection ).
2: Default Character Set of MySQL
MySQL can refine the character set designation to a database, a table, and a column.
However, traditional programs do not use such complex configurations when creating databases and data tables. They use default configurations.
Where does the default configuration come from?
(1) When compiling MySQL, a default character set is specified, which is Latin1;
(2) When installing MySQL, you can specify a default character set in the configuration file (My. INI). If this character set is not specified, this value inherits from the one specified during compilation;
(3) When mysqld is started, you can specify a default character set in the command line parameters. If not specified, this value inherits from the configuration.
In the file, character_set_server is set to this default Character Set;
(4) When creating a new database, unless explicitly specified, the character set of this database is set to character_set_server by default;
(5) When a database is selected, character_set_database is set to the default Character Set of the database;
(6) When creating a table in this database, the default Character Set of the table is set to character_set_database, that is, this
Default Character Set of the database;
　
(7) When setting a column in a table, unless explicitly specified, the default character set in this column is the default Character Set of the table. To sum up, if no modification is made, all tables in all databases
All the columns are stored in Latin1, but if we install
MySQL usually supports multiple languages. That is to say, the installer automatically sets default_character_set
UTF-8, which ensures that all columns of all tables in all databases are stored in UTF-8 by default.
3: view the character set of MySQL (by default, the character set of MySQL is Latin1 (iso_8859_1)
Generally, you can run the following two commands to view the character set and sorting method of the system:
Command 1: mysql> show variables like 'character % ';
+ -------------------------- + ---------------------- ----------- +
| Variable_name | value |
+ -------------------------- + ---------------------- ----------- +
| Character_set_client | Latin1 |
| Character_set_connection | Latin1 |
| Character_set_database | Latin1 |
| Character_set_filesystem | binary |
| Character_set_results | Latin1 |
| Character_set_server | Latin1 |
| Character_set_system | utf8 |
| Character_sets_dir | D: "mysql-5.0.37" share "charsets" |
+ -------------------------- + ---------------------- ----------- +
Command 2: mysql> show variables like 'collation _ % ';
+ ---------------------- + ----------------- +
| Variable_name | value |
+ ---------------------- + ----------------- +
| Collation_connection | utf8_general_ci |
| Collation_database | utf8_general_ci |
| Collation_server | utf8_general_ci |
+ ---------------------- + ----------------- +
4: Modify the default Character Set
(1) The simplest modification method is to modify the character set key value in MySQL's my. ini file, as shown in
Default-character-set = utf8
Character_set_server = utf8
After the modification, restart the MySQL service and use mysql> show variables like 'character % '. Check that the database encoding has been changed to utf8:
+ -------------------------- + ---------------------- ----------- +
| Variable_name | value |
+ -------------------------- + ---------------------- ----------- +
| Character_set_client | utf8 |
| Character_set_connection | utf8 |
| Character_set_database | utf8 |
| Character_set_filesystem | binary |
| Character_set_results | utf8 |
| Character_set_server | utf8 |
| Character_set_system | utf8 |
| Character_sets_dir | D: "mysql-5.0.37" share "charsets" |
+ -------------------------- + ---------------------- ----------- +
(2) another way to modify the character set is to use MySQL commands.
Mysql> set character_set_client = utf8;
Mysql> set character_set_connection = utf8;
Mysql> set character_set_database = utf8;
Mysql> set character_set_results = utf8;
Mysql> set character_set_server = utf8;
Mysql> set collation_connection = utf8;
Mysql> set collation_database = utf8;
Mysql> set collation_server = utf8;
Generally, even if the default Character Set of the table is set to utf8 and the query is sent through the UTF-8 encoding, you will find that the database is still garbled
. The problem lies in the connection layer. The solution is to execute the following sentence before sending the query: Set names 'utf8 ';
It is equivalent to the following three commands:
Set character_set_client = utf8;
Set character_set_results = utf8;
Set character_set_connection = utf8;
5. Summary
(1) set the correct database encoding: MySQL and earlier versions of the character set is always the default ISO8859-1, mysql4.1 will
Let you choose. If you are going to use UTF-8, you need to specify the UTF-8 when creating the database (but MySQL 5 cannot select utf8 encoding during installation, I just tried today, the result cannot be inserted into Chinese !)
　
(2) ensure that the character_set_client and character_set_connection variables and
Character_set_database is consistent, and character_set_results is consistent with the results returned by select and the encoding of the program,
This is important! For the setting method, see "4: modifying the default Character Set" above ".
(3) Before inserting data, you 'd better Add the following sentence: Set names 'utf8', which is equivalent:
Set character_set_client = 'utf8'
Set character_set_connection = 'utf8'
Set character_set_results = 'utf8'
Of course, if the character set for the entire environment is GBK, set names 'gbk' as well '.
(4) Another summary of the key points:
Ensure that the data stored in the database is consistent with the database encoding, that is, the data encoding is consistent with character_set_database;
Ensure that the character sets for communications are consistent with those for databases, that is, character_set_client and character_set_connection are consistent with character_set_database;
Make sure that the return value of select is the same as the code of the program, that is, character_set_results is consistent with the program code;
Ensure that the program encoding is consistent with the browser encoding, that is, the program encoding is consistent with <meta http-equiv = "Content-Type" content = "text/html; charset =? "/> Consistent.