CENTOS6 of MySQL database character set to solve Chinese garbled problem

Today in the deployment of a system, the page appears garbled. So all kinds of Baidu (have been used in the same way before, but did not affect the use),
Let's do an experiment.
This server system is Centos6.3,lamp environment all with yum installation. No configuration has been optimized
See the MySQL default character set configuration below

Mysql

The code is as follows

Copy Code

Mysql> Show variables like "%char%"; +--------------------------+----------------------------+ | 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 | /usr/share/mysql/charsets/| +--------------------------+----------------------------+ 8 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)

Next we build a database

Mysql

code is as follows

copy code

mysql> use Test Database changed mysql> CREATE TABLE ' Test_user ' (   ' id ' int () unsigned not NULL auto_increment,   ' username ' varchar (255) CHARACTER set UTF8 DEFAULT NULL,   ' email ' varchar (255) CHARACTER set UTF8 DEFA ULT NULL COMMENT ' email ',   PRIMARY KEY (' id ') ) engine=myisam DEFAULT charset=utf8 ' test database character set '; & nbsp Mysql> Show tables; +----------------+ | Tables_in_test | +----------------+ | test_user      | +----------------+ 1 rows in Set (0.00 SE c)

Insert two test data

Mysql

The code is as follows	Copy Code
INSERT into ' test ', ' test_user ' (' id ', ' username ', ' email ') VALUES (' 1 ', ' Jobs ', ' jobs@apple.com '); INSERT into ' test ', ' test_user ' (' id ', ' username ', ' email ') VALUES (' 2 ', ' apple ', ' apple@apple.com ');

The query appears garbled, but it does not affect use in our web pages (because the data set in this table is UTF8)

Mysql

The code is as follows

Copy Code

Mysql> select * from Test_user; +----+----------+-----------------+ | ID | Username | email | +----+----------+-----------------+ |      1 |??? | jobs@apple.com | |       2 |?? | apple@apple.com | +----+----------+-----------------+ 2 rows in Set (0.00 sec) We use the Set names UTF8 command when we do the query. Mysql mysql> set names UTF8; Query OK, 0 rows Affected (0.00 sec) The results appear to be normal. Mysql Mysql> select * from Test_user; +----+-----------+-----------------+ | ID | Username | email | +----+-----------+-----------------+ | 1 | Jobs | jobs@apple.com | | 2 | Apple | apple@apple.com | +----+-----------+-----------------+ 2 rows in Set (0.00 sec)

View character set conditions at this time

The code is as follows

Copy Code

Mysql Mysql> Show variables like "%char%"; +--------------------------+----------------------------+ | variable_name | Value | +--------------------------+----------------------------+ | character_set_client | UTF8 | | character_set_connection | UTF8 | | Character_set_database | Latin1 | | Character_set_filesystem | binary | | Character_set_results | UTF8 | | Character_set_server | Latin1 | | Character_set_system | UTF8 | | Character_sets_dir | /usr/share/mysql/charsets/| +--------------------------+----------------------------+ 8 rows in Set (0.00 sec) Mysql> Show variables like ' collation_% '; +----------------------+-------------------+ | variable_name | Value | +----------------------+-------------------+ | collation_connection | Utf8_general_ci | | Collation_database | Latin1_swedish_ci | | Collation_server | Latin1_swedish_ci | +----------------------+-------------------+ 3 Rows in Set (0.00 sec)

Now you can see client,connection,rsults these three character sets are already utf8 (at this time we can use the normal), but in order to prevent some abnormal insertion of data, we recommend that the database is all set to UTF8, without some troubleshooting errors.

The code is as follows

Copy Code

[Root@test ~]# vim/etc/my.cnf Changed to the following Mysql [Client] Default-character-set=utf8 [Mysqld] Datadir=/var/lib/mysql socket=/var/lib/mysql/mysql.sock                                                                                                                                                                                                                       User=mysql # Disabling Symbolic-links is recommended to prevent assorted security risks Symbolic-links=0 Default_character_set=utf8 [MySQL] Default-character-set=utf8 [Mysqld_safe] Log-error=/var/log/mysqld.log Pid-file=/var/run/mysqld/mysqld.pid Mainly add the following data Mysql [Client] Default_character_set=utf8 [MySQL] Default_character_set=utf8 [Mysqld] Default_character_set=utf8 Restart the MySQL service [Root@test ~]#/etc/init.d/mysqld Restart Stop mysqld: [OK] Starting mysqld: [OK]

View the character set at this time

The code is as follows

Copy Code

Mysql> Show variables like "%char%"; +--------------------------+----------------------------+ | 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 | /usr/share/mysql/charsets/| +--------------------------+----------------------------+ 8 rows in Set (0.00 sec)

View the proofing character set

The code is as follows	Copy Code
Mysql> Show variables like ' collation_% '; +----------------------+-----------------+ | variable_name | Value | +----------------------+-----------------+ | collation_connection | Utf8_general_ci | | Collation_database | Utf8_general_ci | | Collation_server | Utf8_general_ci | +----------------------+-----------------+ 3 Rows in Set (0.00 sec)

At this point, the database character set is all unified into UTF8

MySQL'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 levels:
Server, database, datasheet (table), and connection (connection).
1.MySQL default Character set
MySQL specifies the character set that can be refined into a database, a table, a column, and what character set should be used.
However, traditional 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?
(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), and if not specified, the value inherits from the compile-time specified;
(3 When starting mysqld, you can specify a default character set in the command-line arguments, and if not specified, this value inherits from the configuration in the configuration file, at which point the character_set_server is set to this default character set;
(4) When a new database is created, Unless explicitly specified, the character set of the database is set by default to Character_set_server
(5) When a database is selected, Character_set_database is set to the default character set for this database;
(6) 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 for the database;
(7) When a column is set in a table, the default character set for this column is the default character set of the table unless explicitly specified;
Simply summarize, if there is no place to modify, then all the tables of all the database all the fields are used
Latin1 storage, but if we install MySQL, we will generally choose multi-language support, that is, the installer will automatically in the configuration file in the
Default_character_set is set to UTF-8, which guarantees that all fields of all tables in all databases are stored UTF-8 by default.