MySQL character set caused by garbled problem!

Garbled problem caused by MySQL character set

MySQL Character set support (Character set supports) has two aspects:

1. Character set (Character set)

2, sorting Method (Collation).

Support for character sets is refined to four levels:

1. Servers (server)

2. Databases (database)

3. Data Sheet (table)

4. Connection (Connection)

Note: MySQL Specifies the character set that can be refined to a database, a table, a column, and what character sets should be used.

~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~

First question: Can the MySQL default character set be modified? Must drip!

1. Modify the configuration file:

[Mysqld]

Default-character-set = UTF8

Default-collation=utf8_general_ci

#character_set_server = UTF8

#[client]

#default-character-set=utf8

After the modification, restart the MySQL services, service MySQL restart

2, command modification (not recommended, unless there are special circumstances)

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;

Sometimes the default character set for the table is UTF8 and the query is sent through UTF-8 encoding, and the database is still garbled. There may be a problem on the connection connection layer. The workaround is to execute the following sentence before sending the query: SET NAMES ' UTF8 '; it corresponds to the following three-sentence instruction:

SET character_set_client = UTF8;

SET character_set_results = UTF8;

SET character_set_connection = UTF8;

Note: The specific parameters and MySQL installation method and version are closely related, the more mature version of the default character set is Utf-8, of course, how to define, can refer to the official documents.

~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~

second question: View MySQL default character set (by default, MySQL's character set is Latin1)

In general, the settings for viewing the character set and ordering of the system can be set through the following two commands:

SHOW VARIABLES like ' character% ';

SHOW VARIABLES like ' collation_% ';

650) this.width=650; "Src=" Http://s3.51cto.com/wyfs02/M01/83/D9/wKiom1d9_ObiE0HzAACCrMHX-0I370.png-wh_500x0-wm_3 -wmp_4-s_3666084395.png "title=" 1.png "alt=" Wkiom1d9_obie0hzaaccrmhx-0i370.png-wh_50 "/>

~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~

Third question: Traditional programs do not use a complex configuration when creating databases and datasheets, they use the default configuration, so where does the default configuration come from?

(1) When compiling MySQL, specify a MySQL default character set, this character set is latin1;

(2) When installing MySQL, you can specify a default character set in the configuration file (my.cnf, or My.ini), and if not specified, this value is inherited from the compile-time specified;

(3) When starting mysqld, you can specify a default character set in the command line arguments, if not specified, this value inherits from the configuration in the configuration file, at this time Character_set_server is set to this default character set;

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

(5) When a database is selected, Character_set_database is set to the default character set of this database;

(6) When creating a table 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;

(7) When a column is set in the table, the default character set of the column is the set of defaults for the table unless explicitly specified;

Simply summarize, if there is no change anywhere, then all the tables of all the columns of the database are stored with latin1, but if we install MySQL, we will generally choose multi-language support, that is, the installation program will automatically in the configuration file Default_character _set is set to UTF-8, which guarantees that all the columns of all the tables in the database are stored UTF-8 by default.

~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~

Question four: What else will be garbled due to MySQL's cause?

This may be caused by the use of highly available software or plug-ins, such as Mysql-proxy, etc.

Example:mysql proxy garbled problem

Sometimes after connecting the database through a proxy, the found string is always garbled, even if the manual execution of set names ' utf8′ has no effect.

Workaround, MySQL server must be set

Modify the My.cnf file

[Mysqld]

Skip-character-set-client-handshake

init-connect= ' SET NAMES UTF8 '

Default-character-set=utf8

~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~

Question five: What causes the garbled problem?

1. Browser

2. Agent

3. Web

4. Improper configuration

5, Code problems

6. Database

。。。。。。。

In short, there are many reasons can cause the website garbled, according to their actual situation to exclude, here is just discuss MySQL problem, other reasons, not to delve into.

Write a blog, share to everyone, where there is a mistake, please correct me!

This article from "Ningzhiyuan @it share" blog, declined reprint!

MySQL character set caused by garbled problem!