JSP uses UTF-8 to link MYSQL database (UTF8) garbled and connection failure issues and change mysql Default Encoding

JSP uses UTF-8 link MYSQL database (UTF8) garbled and connection failure issues:

Preface: the databases used by these big companies are not human-friendly... It took me a long time to get it done.

Csdn does not seem to be able to transmit images anymore... I would like to have a few images...

1. In windows mysql database is utf8 encoding, the connection fails (note that mysql is not a UTF-8, but utf8) Status, display? On the web page (UTF-8 encoding set for the web page)

User ID number	User Name	User Password	User address
47	G?	1243	Null
48	?	1243	Null
49	? 123	1243	Null
50	? 23	1243	Null
51	? 3	1243	Null

When the database sets the client language to GBK, it can be displayed. It is assumed that utf8 encoding is used internally. After the setting, it will be converted to GBK encoding:

Mysql> set names gbk
->;
Query OK, 0 rows affected (0.00 sec)


Mysql> select * from user;
+ -------- + ----------- + -------------- + ------------ +
| Userid | username | userpassword | useradress |
+ -------- + ----------- + -------------- + ------------ +
| 1 | Me | NULL |
| 2 |? 2 | 1243 | NULL |
| 3 | ?? 37fg459| 1243 | NULL |

Of course, if you use utf8 encoding, it will be garbled. It is assumed that the default windows encoding is GBK, so utf cannot be displayed.

Mysql> set names utf8;
Query OK, 0 rows affected (0.00 sec)


Mysql> select * from user;
+ -------- + ----------- + -------------- + ------------ +
| Userid | username | userpassword | useradress |
+ -------- + ----------- + -------------- + ------------ +
| 1 | bytes? | NULL |
| 2 |? 2 | 1243 | NULL |
| 3 | ?? 37fg459| 1243 | NULL |
| 4 | response? | 1, 1243 | NULL |

The solution is as follows:

2. Step 1: Check the database encoding method: Open the database command line and enter the command status;

Mysql> status;
--------------
D: \ mysql_6.0 \ bin \ mysql.exe Ver 14.15 Distrib 6.0.11-alpha, for Win64 (unknown)


Connection id: 72
Current database: contacts
Current user: root @ localhost
SSL: Not in use
Using delimiter :;
Server version: 6.0.11-alpha-community MySQL Community Server (GPL)
Protocol version: 10
Connection: localhost via TCP/IP
Server characterset: utf8
Db characterset: utf8
Client characterset: utf8
Conn. characterset: utf8
TCP port: 3306
Uptime: 9 hours 25 min 53 sec


Threads: 7 Questions: 749 Slow queries: 0 Opens: 23 Flush tables: 1 Open ta
Bles: 2 Queries per second avg: 0.22
--------------

You can also view the following command: show variables like "% char %"

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 | D: \ mysql_6.0 \ share \ charsets \ |
+ -------------------------- + -------------------------------- +
8 rows in set (0.00 sec)

Basically, the encoding is based on the master node. You can determine that the default encoding of the database is correct.

3. Check the database encoding and enter the following command:

Show create database mydata. Here mydata refers to the name of a database.

Mysql> show create database mydata;
+ ---------- + --------------------------------------------------------------------
-------------- +
| Database | Create Database
|
+ ---------- + --------------------------------------------------------------------
-------------- +
| Mydata | create database 'mydata '/*! 40100 default character set utf8 COLLAT
E utf8_bin */|
+ ---------- + --------------------------------------------------------------------
-------------- +
1 row in set (0.00 sec)

4. Check the encoding of tables and columns in the database. After selecting a database, enter the following command in the command line:

Here, the user is a table name.

Show create table user;

Mysql> show create table user;
+ ------- + -----------------------------------------------------------------------
-----------------------------------------------------------------------------
----------------- +
| Table | Create Table

|
+ ------- + -----------------------------------------------------------------------
--------------------------------------------------------------------------
----------------- +
| User | create table 'user '(
'Userid' int (11) not null AUTO_INCREMENT,
'Username' varchar (20) COLLATE utf8_bin not null,
'Userpassword' varchar (20) COLLATE utf8_bin not null,
'Address' varchar (100) COLLATE utf8_bin default null,
Primary key ('userid '),
Unique key 'username' ('username '),
Unique key 'username _ 2' ('username ')
) ENGINE = InnoDB AUTO_INCREMENT = 72 default charset = utf8 COLLATE = utf8_bin |
+ ------- + -----------------------------------------------------------------------
-------------------------------------------------------------------------------
----------------- +
1 row in set (0.00 sec)

Then, check whether the encoding behind each column is utf8. Also, check the number of charsets behind the table and the encoding method specified after collate, it refers to the matching encoding rules used for database search.

In the case of 3 and 4, we can basically determine that there is no problem with the encoding in the database and table. Other problems can only be solved in jsp and the connection between jsp and database.

5. check whether all the code in jsp is the way of UTF-8.

Step 1:

For the jsp page itself:

<% @ Page contentType = "text/html; charset = UTF-8" pageEncoding = "UTF-8" %>

Step 2:

For the html page itself:

<Meta http-equiv = "Content-Type" content = "text/html; charset = UTF-8">

Step 3:

Jsp: request. setCharacterEncoding ("UTF-8 ");
Jsp: response. setCharacterEncoding ("UTF-8 ");

To achieve the above, basically the code in jsp is no problem at all, ensure that they are UTF-8.

6. check whether there is a problem in the connection process. The problem occurred here and the result was found for a long time...

When connecting to a database in jsp:

Connection conn = DriverManager. getConnection ("jdbc: mysql: // localhost/mydata? User = root & password = password1 & useUnicode = true & characterEncoding = UTF-8 ");

Note: Here mydata is the database name

Localhost is the local host

Root is the username used to connect to the database.

Password1 is the password used to connect to the database.

UseUnicode = true & characterEncoding = The UTF-8 specifies the encoding method used by the connection

The landlord once thought that in connection useless useUnicode = true & characterEncoding = UTF-8 everything, after the connection with set names utf8 method, but not successful.

The above, the problem is completely solved, just in case, we suggest you talk about IDE encoding settings for UTF-8, What eclipse myeclipse netbeanse you know ....

7. settings in servl:

Request. setCharacterEncoding ("UTF-8 ");
Response. setCharacterEncoding ("UTF-8 ");
Response. setContentType ("text/html; charset = UTF-8 ");

The tables and columns in the MYSQL database are already encoded in the following way if they are not UTF-8 at the beginning.

Note: errors may occur if data exists in the table. 1. Change the database code to UTF-8

Alter DATABASE 'test' default character set utf8 COLLATE utf8_bin

The preceding command sets the encoding of the test database to utf8.

2. Change the table encoding to UTF-8

Alter TABLE 'category 'default character set utf8 COLLATE utf8_bin

The preceding command changes the category encoding of a table to utf8.

3. encode the fields (each column) in the table to UTF-8

Alter TABLE 'test' CHANGE 'dd' dd' VARCHAR (45) character set utf8 COLLATE utf8_bin NOT NULL

The preceding command is to encode the dd field in the test table to utf8.

This command can also specify this field as a special encoding field. For example, if some symbols in the text are not in utf8, you can set a certain field to GBK to store this symbol, but remember to perform transcoding when accessing data. 4. set other (default) encoding:

In the mySQL installation directory, open mysql. ini and change the encoding after all character to utf8. This will change the default encoding when creating a new database and table. MYSQL will not expire after restart.