MySQL use UTF8 Chinese garbled solution

Recently, I am doing a project deployment, this project is nginx for the front-end agent two Tomcat node, through the Mysql-proxy proxy access to two master-slave MySQL database.

The entire project was deployed, tested and found to be garbled in Chinese when the Web page was opened. OK, I'll make the changes.

Start thinking MySQL database configuration has a problem, view MySQL configuration file my.conf, the content is as follows:

[Mysqld]datadir=/usr/local/mysql/datasocket=/tmp/mysql.sockuser=mysqlskip-character-set-client-handshakeinit_ connect= ' SET NAMES UTF8 ' default-storage-engine=innodb[mysqld_safe]log-error=/var/log/mysqld.logpid-file=/var/run /mysqld/mysqld.piddefault-character-set = Utf8[mysql]default-character-set = Utf8[mysql.server] Default-character-set = Utf8[client]default-character-set = UTF8

I am searching online to tell me to add Character-set-server=utf8 and init_connect= ' Set NAMES UTF8 ' in the config file, I can change the configuration file

[Mysqld]datadir=/usr/local/mysql/datasocket=/tmp/mysql.sockuser=mysqlskip-character-set-client-handshakeinit_ connect= ' SET NAMES UTF8 ' character_set_server=utf8default-storage-engine=innodb[mysqld_safe]log-error=/var/log/ Mysqld.logpid-file=/var/run/mysqld/mysqld.piddefault-character-set = Utf8[mysql]default-character-set = utf8[ Mysql.server]default-character-set = Utf8[client]default-character-set = UTF8

Restart MySQL, log in to the MySQL database for query

mysql> show variables like  ' character_set% '; +--------------------------+--------------- -----------------------------------------------------+| 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/local/mysql-5.0.96-linux-x86_64-glibc23/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    | utf8_general_ci | | collation_server     | utf8_ general_ci | +----------------------+-----------------+3 rows in set  (0.00  SEC) mysql>

See all the character sets are UTF8, thought this time should be no problem, the results open the webpage or garbled,

View the configuration of the JDBC interface,

Jdbc.url=jdbc:mysql://mysqlproxy:3306/canyin_model?useunicode=true&characterencoding=utf-8jdbc.username= Usernamejdbc.password=password

Find JDBC configuration is also UTF8, no problem ah.

I checked the tomcat configuration file, thinking that the Server.xml file forgot to modify the character set, I opened the Server.xml file and found this configuration as follows:

    <connector port= "8080"  protocol= "http/1.1"                 connectiontimeout= "20000"                 redirectport= "8443"                 uriencoding= "UTF-8"                 maxthreads= "    "              minsparethreads= " "                   Acceptcount= "               "   maxconnections= "            "    maxhttpheadersize= "8192 "                  Tcpnodelay= "true"                   compression= "on"                   compressionminsize= "2048"                   enablelookups= "false"  />

Found that I have set the ugf-8 character set Ah, how still wrong, see the result of a face confused, ah, the problem is to solve, I ask my friend, he told me to add usebodyencodingforuri= "true" in the configuration file I do not know what it is to do, in the online search to know.

In Tomcat7, get is handled separately from post, processed using uriencoding for GET requests, and request.setcharacterencoding () for post. The following configuration parameters have been added to the connector element in Server.xml:

Uriencoding: Used to set the encoding used for content passed through a URI, tomcat encodes the content that the client transmits using the encoding specified here. Processing GET requests uses this parameter, which uses ISO-8859-1 encoding by default.

Usebodyencodingforuri: Uses the same encoding as the body to handle the URI.

OK, make changes to the Tomcat configuration file,

<connector port= "8080" protocol= "http/1.1" connectiontimeout= "20000" redirectport= "8443" useBodyEncodingForURI= " True "uriencoding=" UTF-8 "/>

Restart Tomcat, clear the browser's cache, re-access and garbled, after the Web page changes, found that the database data is normal. At the time of the visit, sometimes there will be question marks, not garbled. This garbled is solved.

My friend told me to look at the system's character set, and I found that the system's character set seems to have problems,

[Email protected] ~]# LOCALELANG=ZH_CN. Utf-8lc_ctype= "ZH_CN. UTF-8 "lc_numeric=" ZH_CN. UTF-8 "Lc_time=" ZH_CN. UTF-8 "Lc_collate=" ZH_CN. UTF-8 "lc_monetary=" ZH_CN. UTF-8 "lc_messages=" ZH_CN. UTF-8 "Lc_paper=" ZH_CN. UTF-8 "Lc_name=" ZH_CN. UTF-8 "lc_address=" ZH_CN. UTF-8 "Lc_telephone=" ZH_CN. UTF-8 "Lc_measurement=" ZH_CN. UTF-8 "lc_identification=" ZH_CN. UTF-8 "Lc_all=[[email protected" ~]# echo $LANGzh _cn. Utf-8[[email protected] ~]# cat/etc/sysconfig/i18nlang= "ZH_CN. UTF-8 "[[email protected] ~]#

In Lc_all this is blank, changes are made to the system character set,

echo "Export lang=\" ZH_CN. Utf-8\ "Export lc_all=\" ZH_CN. Utf-8\ "" >>/etc/profile Source/etc/profile

In the view system is UTF8 format, thought now should have no matter. Open the Web page, you can access the normal, but, or the question mark, the odds are relatively large, to find the reason again. I guess the proxy is not a problem, so I connect the node directly to the MySQL database, I found that the visit without a question mark appears. But the configuration of proxy is not about the configuration of the character set Ah, this is my friend asked me to use the database version is how much, I told him the version of MySQL for mysql-5.0.96.

So tell me to view the MySQL profile, he looks at my profile, tells me to add default-character-set = UTF8 under [mysqld] in the config file, and change the contents as follows:

[Mysqld]datadir=/usr/local/mysql/datasocket=/tmp/mysql.sockuser=mysqlskip-character-set-client-handshakeinit_ connect= ' SET NAMES UTF8 ' character_set_server=utf8default-character-set=utf8default-storage-engine=innodb[mysqld_ Safe]log-error=/var/log/mysqld.logpid-file=/var/run/mysqld/mysqld.piddefault-character-set = Utf8[mysql] Default-character-set = Utf8[mysql.server]default-character-set = Utf8[client]default-character-set = UTF8

Plus, once again, visiting the discovery page does not appear in question marks.

To this, I this project garbled problem is solved perfectly.

But why does the Character_set_server=utf8 I configure work, and this DEFAULT-CHARACTER-SET=UTF8 configuration works?

I look in the mother, just understand.

Originally in the 5.1 version, in order to solve the Chinese garbled problem set the default character set is UTF8, in the My.ini in the [MySQL] and [mysqld] entries are written:

Default-character-set=utf8

In the 5.5 version, [MySQL] can write this in the [mysqld] item, but it must be written:

Character-set-server=utf8

And my MySQL database version is 5.0, so only the configuration Default-character-set=utf8 this only works.

Summarize:

1, MySQL garbled problems mainly have several links to decide, one is the front page, the second is the character transmission, the third is to connect MySQL, and four is the storage mode of MySQL.

2, Linux to pay attention to the version of the difference.

This article from "Sun Tao" blog, declined reprint!

MySQL use UTF8 Chinese garbled solution