Summary of SQL bit 40-mysql garbled problem

Text: SQL drip 40-mysql garbled problem Summary

This article will explain how to handle the Java connection process of MySQL Chinese garbled problem. General MySQL Chinese garbled problem is related to the character set, the author's experience here is roughly the same.

MySQL default encoding is Latin1
1. Mysql> Show variables like ' character% ';
2. +--------------------------+--------------------------+
3. | variable_name | Value |
4. +--------------------------+--------------------------+
5. | character_set_client | Latin1 |
6. | character_set_connection | Latin1 |
7. | Character_set_database | Latin1 |
8. | Character_set_filesystem | binary |
9. | Character_set_results | Latin1 |
10. | Character_set_server | Latin1 |
11. | Character_set_system | UTF8 |
12. | Character_sets_dir | D:\MySQL\share\charsets\ |
+--------------------------+--------------------------+
14. Create a data table and insert data
mysql> use test;
Mysql> CREATE TABLE messages (
. ID int (4) unsigned auto_increment primary key,
Message varchar (+) NOT NULL
Engine=myisam default Charset=utf8;
Mysql> INSERT into messages (message) VALUES ("Test MySQL Chinese display");
Mysql> SELECT * from messages;
+----+-------------------+
23. | ID | message |
+----+-------------------+
25. | 1 | Test MySQL Chinese display |
+----+-------------------+
27. Writing the program (Java)
. import Java.sql.Connection;
. import Java.sql.DriverManager;
. import Java.sql.ResultSet;
. import Java.sql.Statement;
public class Jdbctest {
The public static void main (string[] args) {
String Driver = "Com.mysql.jdbc.Driver";
A. String url = "Jdbc:mysql://localhost:3306/test";
String user = "root";
PNs. String Password = "root";
. try {
Class.forName (driver);
Connection conn = drivermanager.getconnection (URL, user, password);
Statement stmt = Conn.createstatement ();
Stmt.executeupdate ("INSERT into messages (message) values (' Test MySQL code ')");
ResultSet rs = stmt.executequery ("SELECT * from messages");
The. while (Rs.next ()) {
the. int id = rs.getint ("id");
The. String message = rs.getstring ("message");
System.out.println (id + "" + message);
48.}
Rs.close ();
Stmt.close ();
Wuyi Conn.close ();
A.} catch (Exception e) {
E.printstacktrace ();
54.}
55.}
56.}
57. Program Output
1???? Mysql????????
59.2?? Mysql??

We see that although we can normally add and display Chinese while using the database, we will not be able to display Chinese correctly when using the program to connect to the database, so we need to modify the default MySQL encoding and edit the My.ini (MySQL config file) file to modify the encoding.

Setting the default character set for MySQL to UTF8, locate the client configuration [clients] added below.

Default-character-set=utf8

Locate the server configuration [mysqld] below to add

Default-character-set=utf8

Set MySQL database to run in UTF8 encoding using UTF8 encoding when connecting to MySQL database

Stop and restart MySQL

net stop MySQL

net start MySQL

In practice, the latest MySQL above two sentences have expired, in the command line first exit and then re-login can achieve this purpose

Reconnect the database, view the encoding, the contents of the data table

1. Mysql> Show variables like ' character% ';
2. +--------------------------+--------------------------+
3. | variable_name | Value |
4. +--------------------------+--------------------------+
5. | character_set_client | UTF8 |
6. | character_set_connection | UTF8 |
7. | Character_set_database | UTF8 |
8. | Character_set_filesystem | binary |
9. | Character_set_results | UTF8 |
10. | Character_set_server | UTF8 |
11. | Character_set_system |    UTF8 | The line has not changed, it is recommended to use the replacement, so that can be changed comprehensively
12. | Character_sets_dir | D:\MySQL\share\charsets\ |
+--------------------------+--------------------------+
mysql> use test;
Mysql> SELECT * from messages;
+----+-------------------------------+
17. | ID | message |
+----+-------------------------------+
19. | 1 | Krupp kinase row MySQL distinction row contacto shin contacto row kinase Timestamp |
20. | 2 |??                     Mysql?? |
+----+-------------------------------+
22. The display is still garbled mainly because of the different encoding used before, rerun the program written before: Java jdbctest
1???? Mysql????????
24.2?? Mysql??
25.3 Test MySQL Code
26. From the third record we can see now the program connected to the database can be normal to add and display Chinese//If from MySQL command line still cannot query data, display data set is empty, continue to complete the third step to make the system code and MySQL code consistent can be queried out ......&
Mysql> SELECT * from messages;
+----+-------------------------------+
29. | ID | message |
+----+-------------------------------+
31. | 1 | Krupp kinase row MySQL distinction row contacto shin contacto row kinase Timestamp |
32. | 2 |??                     Mysql?? |
33. | 3 | Jung Cuppacino examination mysql ke policy home |
+----+-------------------------------+

Look back to the database display, we will be very strange to find out why the display is garbled, which is mainly related to Windows under the command line encoding, on the command line (right-click on the upper-left corner of the command Prompt property) View Properties----options for the current code page: 936 (ansi/oem-Simplified Chinese) GBK) (This is shown on my machine)
That is, the command line is using the GBK encoding, and we are in the program connection with the use of UTF8 to add, so there will be garbled, now we will change the client's code to gb2312 or GBK to try

1. Mysql> Show variables like ' character% ';
2. +--------------------------+--------------------------+
3. | variable_name | Value |
4. +--------------------------+--------------------------+
5. | character_set_client | gb2312 |
6. | character_set_connection | gb2312 |
7. | Character_set_database | UTF8 |
8. | Character_set_filesystem | binary |
9. | Character_set_results | gb2312 |
10. | Character_set_server | UTF8 | Finally, you will find that this is still unchanged, see below
11. | Character_set_system | UTF8 |
12. | Character_sets_dir | D:\MySQL\share\charsets\ |
+--------------------------+--------------------------+
mysql> use test;
Mysql> SELECT * from messages;
+----+-------------------+
17. | ID | message |
+----+-------------------+
19. | 1 |???? Mysql???????? |
20. | 2 |??         Mysql?? |
21. | 3 | Test MySQL Code |
+----+-------------------+

Add: Change Character_set_server, use the MySQLInstanceConfig.exe configuration under the Bin folder of the installation directory, select the second item (using UTF-8) or the third item (self-setting) in one step of many steps (choose encoding) ... Change Complete ...

Now can see the normal display of Chinese (mainly because UTF8 also support Chinese), so when we use the command-line tool to connect to the database, it is best to change the client's code, if the GUI is not necessary, and modify the client's code after the program will still be able to display (the above two points have been tested)

So if you want to display Chinese in the program, we can choose UTF8,GB2312,GBK these three kinds of coding, but if you want to add Chinese data on the command line or to view the words will need to set the client's encoding to gb2312 or GBK, or that sentence, CMD encoding about

Confused,!!!!!!!!!!!!!!!!!!!!!!!!!!!!!.

1. Query encoding

 like ' character% ';

2. Setting the database character set

Alter Database character Set UTF8

3. Setting the character set of a table

Alter Table Convert  to character Set UTF8

4. Do not know

set character_set_results=UTF8

5. Do not know

Set character Set UTF8

6. Do not know

Set ' UTF8 '

Sometimes these do not work, to set into GBK or gb2312, as if there is no regularity, I rub!

PHP Web page garbled is generally used in the establishment of the database Code and PHP Web page encoding different causes,

Database created with phpMyAdmin if you do not specify the encoding he is the default is LATIN1_SWEDISH_CI encoding, both the Swedish language is not case-sensitive, and we do the Chinese web page is not GBK is GB2312 code, so do not appear garbled.

1, specify the encoding when establishing the database.

In this again nagging about the usual code, free novice and confused:
If you are doing a simplified Chinese page, then you use GB2312 encoding when you build the database, Gb2312_chinese_ci.
If you are doing a traditional Chinese web page, then you need to use GIB5 encoding when you build the database, Big5_chinese_ci
If you do the webpage has simplified also has the traditional Chinese, then recommends you to use GBK code, GBK_CHINESE_CI. GBK contains more loadline than GB2312, and of course traditional is in it.
If you are working on a multi-lingual Web page, it is recommended that you use UTF-8 encoding. MySQL has two kinds of UTF8 encoding to choose from: Utf8_unicode_ci and utf8_general_ci The author General Utf8_general_ci, about these two kinds of coding differences, please refer to this site another article: MySQL proofing set utf8_ The difference between Unicode_ci and utf8_general_ci
We use UTF-8 encoding as an example to build a database:

2, when using PHP to connect to the database with Mysq_query set the code
Syntax: mysql_query ("SET NAMES ' UTF8 '");
For example:

Copy the code as follows: $conn =mysql_connect (' 127.0.0.1 ', ' root ', ' 123456 ');
mysql_query ("Set names ' UTF8 '", $conn); Fix garbled
mysql_select_db (' test1 ', $conn);

In general, as long as you do the two steps, your program will not appear garbled
two general situation:  
Second-like situation generally appear not much, more than now do the programming language more, such as sometimes do utf8, sometimes do gb2312, and sometimes do Big5 Web pages, with the IDE built. php file encoding differs from the code that the program will display.
For example, I last made a gb2312 of the Simplified Chinese page, and now the boss has commanded to make a gbi5 Web page, but my IDE is GB2312 encoding
Let's take the IDE Dreamweaver I used to.

IDE New page is gb2312, and I have built a Web page into Big5, so how can not appear garbled, the solution is very simple, re-save as a bit, specify the code is OK,
If not, add a line of HTTP headers to the head of the page
Header ("Content-type:text/html;charset=utf-8");
Note: Headers may not have any output, including spaces, in front of the header when sending header information.
Well write so much, quickly see your program belongs to which situation caused by garbled it.

Summary of SQL bit 40-mysql garbled problem