Summary of MySQL character set garbled solution method

Character-set-server/default-character-set: Server character set, by default.
Character-set-database: Database character set.
Character-set-table: Database table character Set.
Priority increases in order. Therefore, in general, you only need to set the character-set-server, while creating the database and tables do not specifically specify the character set, so that the unified use of the Character-set-server character set.
Character-set-client: The character set of the client. The client default character set. When a client sends a request to the server, the request is encoded with the character set.
Character-set-results: Result character Set. When the server returns results or information to the client, the result is encoded in that character set.
At the client, if no character-set-results is defined, the character-set-client character set is used as the default character set. So you just need to set up the character-set-client character set.

To handle Chinese, you can set both Character-set-server and Character-set-client to GB2312, and if you want to handle multiple languages at the same time, set to UTF8.

Questions about MySQL in Chinese

To solve the garbled method, the following three system parameters are set to the same character set as the server character set character-set-server before executing the SQL statement.
Character_set_client: The character set of the client.
Character_set_results: Result character Set.
Character_set_connection: Connection character Set.
Set these three system parameters by sending a statement to MySQL: Set names gb2312

About GBK, GB2312, UTF8

Utf-8:unicode transformation Format-8bit is allowed with a BOM, but usually does not contain a BOM. is a multi-byte encoding used to solve international characters, which uses 8 bits in English (that is, one byte), and the Chinese uses 24 for (three bytes) to encode. UTF-8 contains the characters that all countries in the world need to use, is international code, strong universality. UTF-8 encoded text can be displayed on browsers that support UTF8 character sets in countries. For example, if the UTF8 code, the foreigner in English IE can also display Chinese, they do not need to download IE Chinese Language support package.

GBK is the standard of GB2312 compatible GB2312 on the basis of national standard. GBK's text encoding is expressed in two-byte notation, that is, both Chinese and English characters are expressed in double-byte notation, and the highest bits are set to 1 in order to differentiate Chinese. GBK contains all the Chinese characters, is the country code, the universality is worse than the UTF8, but UTF8 occupies the database to be bigger than the GBD.

GBK, GB2312, and UTF8 must all be converted to each other through Unicode encoding:
GBK, Gb2312--unicode--utf8
UTF8--UNICODE--GBK, GB2312

For a website, forum, if English characters are more, it is recommended to use UTF-8 to save space. But now many forum plug-ins generally only support GBK.

GB2312 is a subset of GBK, GBK is a subset of GB18030.
GBK is a large set of characters including Chinese, Japanese and Korean
If it is a Chinese website recommend GB2312 GBK Sometimes there is a problem
In order to avoid all garbled problems, should adopt UTF-8, in the future to support internationalization is also very convenient
UTF-8 can be thought of as a large character set, which contains the encoding of most of the text.
One advantage of using UTF-8 is that users in other areas (such as Hong Kong, Taiwan) will be able to view your text without any garbled characters without installing Simplified Chinese support.

GB2312 is a code in Simplified Chinese
GBK supports Simplified Chinese and Traditional Chinese
Big5 supports Traditional Chinese
Utf-8 supports almost any character

First, analyze the situation of garbled
1. Write as garbled when writing to the database
2. Query results with garbled return
What is the situation when garbled characters occur?
Let's first type in the MySQL command line
Show variables like '%char% ';
To view MySQL character set settings:

Mysql> Show variables like '%char% ';
+--------------------------+----------------------------------------+
| variable_name | Value |
+--------------------------+----------------------------------------+
| character_set_client | GBK |
| character_set_connection | GBK |
| Character_set_database | GBK |
| Character_set_filesystem | binary |
| Character_set_results | GBK |
| Character_set_server | GBK |
| Character_set_system | UTF8 |
| Character_sets_dir | /usr/local/mysql/share/mysql/charsets/|
+--------------------------+----------------------------------------+

In the query results can be seen in the MySQL database system client, database connection, database, file system, query
Results, server, system set of character sets
In this case, the file system character set is fixed, the system, the server's character set at the time of installation, and the garbled problem is irrelevant
The problem of garbled characters is related to the setting of the client, database connection, database, query result
* Note: The client is to see the way to access the MySQL database, access through the command line, the command line window is the client, pass
JDBC Connection access, the program is the client
We write the Chinese data to MySQL, the client, database connection, write to the database, respectively, to encode the
Change
When the query is executed, the return result, the database connection, and the client encode the conversion
Now we should be aware that garbled characters occur in databases, clients, query results, and database connections, one or more of these
A link
And then we're going to solve this problem.
When we log in to the database, we connect using the MySQL--default-character-set= character set-U root-p, when we
Then use show variables like '%char% '; command to view character set settings, you can find clients, database connections,
The character set of the query result has been set to the character set that was selected at logon
If you are already logged in, you can use the set names character set to achieve this effect, equivalent to the following command:
Set character_set_client = Character Set
Set character_set_connection = Character Set
Set character_set_results = Character Set
If you are connecting to a database through JDBC, you can write a URL like this:
url=jdbc:mysql://localhost:3306/abs?useunicode=true&characterencoding= Character Set
JSP page and other terminals to set the corresponding character set
The database's character set can modify the MySQL startup configuration to specify the character set, or you can add it to the CREATE database
Default character set character set to enforce the character set of the database
Through this setting, the entire data write read out process in the unified character set, there will be no garbled
Why write Chinese directly from the command line without setting or garbled?
It is clear from the command line, the client, database connection, query results of the character set has not changed
The input of Chinese after a series of transcoding and back to the original character set, we look at what we see is not garbled
But that does not mean that Chinese are stored correctly in a database as a medium character.
For example, now there is a UTF8 encoded database, the client connection uses GBK encoding, connection uses the default
Iso8859-1 (that is, the latin1 in MySQL), we send the "Chinese" string on the client, the client
Sends a string of GBK format binaries to the connection layer, the connection layer in ISO8859-1 format
Binary code sent to the database, the database will be stored in the UTF8 format, we will this field to UTF8
Format read out, it must be garbled, that is, Chinese data in writing to the database is in garbled form of storage,
When querying with the same client, a set of actions opposite to writing is done and the wrong UTF8 format binary
The code is then converted to the correct GBK code and displayed correctly.

/****************java, JSP to set the encoding ***************************/
First of all, in Java where the code can be set

The following two coding format methods apply to JSP pages (*.jsp)

The code is as follows

Copy Code

<%@ page language= "java" import= "java.util.*" pageencoding= "UTF-8"%> <%@ page contenttype= "text/html; Charset=utf-8 "%> The following methods are suitable for JSP, servlet, action (*.java) Request.setcharacterencoding ("UTF-8"); Response.setcharacterencoding ("UTF-8"); The following are suitable for HTML pages (*.htm;*.html) <meta http-equiv= "Content-type" content= "text/html; Charset=utf-8 "> Tomcate set Encoding (server.xml) <connector other omitted port= "uriencoding=" "UTF-8" >

MySQL Set encoding command

The code is as follows

Copy Code

SET character_set_client = UTF8; SET character_set_connection = UTF8; SET character_set_database = UTF8; SET Character_set_results = utf8;/* here to pay attention to very useful * * SET character_set_server = UTF8; SET collation_connection = Utf8_bin; SET collation_database = Utf8_bin; SET collation_server = Utf8_bin; Configure default encoding in My.ini Default-character-set=utf8 Connection Database Settings encoding Jdbc:mysql://192.168.0.5:3306/test?characterencoding=utf8 /*****************************************java and mysq codes correspond to ****************************************/ The common coding UTF-8 in Java; GBK; gb2312;iso-8859-1; corresponding to the code utf8;gbk;gb2312;latin1 in MySQL database /******************************************** Filter using *********************************************/ Filter set encoding filter (Setcharacterencodingfilter.java) Package COM.SORC; Import java.io.*; Import javax.servlet.*; Import javax.servlet.http.*; public class Setcharacterencodingfilter extends HttpServlet implements filter{ Private Filterconfig Filterconfig; Private String Encoding=null; Handle the passed-in filterconfig public void init (Filterconfig filterconfig) { This.filterconfig=filterconfig; Encoding=filterconfig.getinitparameter ("encoding"); } Process the Request/response pair public void Dofilter (ServletRequest request,servletresponse response,filterchain filterchain) { try{ request.setcharacterencoding (encoding); Filterchain.dofilter (Request,response); catch (Servletexception sx) { Filterconfig.getservletcontext (). log (Sx.getmessage ()); catch (IOException Iox) { Filterconfig.getservletcontext (). log (Iox.getmessage ()); } } Clean up resources public void Destroy () { } } Web.xml Configuration Filter Method (WEB.XMD) <filter> <filter-name>setcharacterencodingfilter</filter-name> <filter-class>com.sorc.SetCharacterEncodingFilter</filter-class> <init-param> <param-name>encoding</param-name> <param-value>utf8</param-value> </init-param> </filter> <filter-mapping> <filter-name>setcharacterencodingfilter</filter-name> <url-pattern>/*</url-pattern> </filter-mapping>

/*************** has the above Foundation to interview a perfect solution *****************************************/
1. Solution using GBK Coding
The easiest place to get a setup code is to use the GBK database GBK and then use a filter to filter the code for GBK all done.
effect for adding data no garbled read without garbled database management tools No garbled everywhere SQL structure and data no garbled

2. Using UTF-8 Coding Solution
All encodings are set to UTF-8
Database Encoding UTF8
Set Filter Encoding UTF8
Database connection? Characterencoding=utf8
Then run SET character_set_results = GBK on the Database administration tool or the MySQL command line;
effect for adding data no garbled read without garbled database management tools No garbled everywhere SQL structure and data exist garbled

3. The page uses the UTF8 database to use the Latin1 solution
Jap Java Tomcat set to UTF-8
Filter UTF8
Database connection? characterencoding=latin1
Database Other Latin1
Then run SET character_set_results = GBK on the Database administration tool or the MySQL command line;
effect for adding data no garbled read without garbled database management tools No garbled everywhere SQL structure and data exist garbled

PHP MYSQL Chinese Final Solution,

In MySQL, if the inserted character set is inconsistent with the current environment and field, the MySQL display is garbled

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

See for yourself.

PHP.ini My.ini

Database encoding

Table encoding

Column encoding

Link encoding
Here's something we'll often talk about in the MySQL tutorial.

If it is page and MySQL output page garbled we can

is to add a sentence after the mysql_connect set NAMES UTF8, you can make the UTF8 database to eliminate garbled, for the GBK database uses set NAMES GBK, the code is as follows:

The code is as follows	Copy Code
$mysql _mylink = mysql_connect ($mysql _host, $mysql _user, $mysql _pass); mysql_query ("SET NAMES ' GBK '");