JSP uses UTF-8 link MySQL database (UTF8) garbled and connection failure problem and change MySQL default encoding

JSP uses UTF-8 link MySQL database (UTF8) garbled and connection failure problem:

Preface, the service of these large companies, do the database are not human ... I've been busy for a long time to say

Csdn can't seem to pass the picture ... I would like to have a few pictures, we will ...

1. When MySQL database is UTF8 encoded in Windows, the connection fails (note that MySQL is not UTF-8, but UTF8) situation, show? On the Web page (UTF-8 encoding for Web page settings)

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

in the database setting the client language is GBK can be displayed, guessing is the internal use of UTF8 encoding, set after the custom conversion into 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 | NULL |
| 2 |? 2 | 1243 | NULL |
| 3 |?? 37fg459 | 1243 | NULL |

of course, with UTF8 encoding, will be garbled, guess is Windows default encoding is GBK, so UTF is not displayed.

mysql> set names UTF8;
Query OK, 0 rows Affected (0.00 sec)


Mysql> select * from user;
+--------+-----------+--------------+------------+
| UserID | Username | UserPassword | useradress |
+--------+-----------+--------------+------------+
| 1 |       Contact? | NULL | NULL |
| 2 |? 2 | 1243 | NULL |
| 3 |?? 37fg459 | 1243 | NULL |
| 4 |       澶? | 1243 | NULL |

Here's how to fix it:

2. The first step is to check how the database is encoded: Open the command line for the database 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: [email protected]
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 min. sec


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

There is also a command to view: 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 code to do the landlord this degree, you can determine the default encoding of the database is no problem.

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

Show CREATE Database MyData, here MyData refers to the name of one of the databases.

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, and enter the following command at the command line after selecting a database:

User here is a table name

Show create table user;

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

|
+-------+-----------------------------------------------------------------------
--------------------------------------------------------------------------
-----------------+
| user | CREATE TABLE ' user ' (
' UserID ' int (one) not NULL auto_increment,
' username ' varchar (COLLATE) Utf8_bin not NULL,
' UserPassword ' varchar (COLLATE) Utf8_bin not NULL,
' Address ' varchar (+) 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)

After the check, each column after the encoding is not utf8, at the same time look at the table behind the charset= how much, collate after the specified encoding method, refers to the database search when using the matching encoding rules.

To achieve the situation in 3 and 4, the basic can determine the database and the coding in the table is no problem. The rest of the problem can only be in the JSP and the JSP and database connection.

5. Check that the code in the JSP is all UTF-8.

The first step:

For the JSP page itself:

<% @page contenttype= "text/html; Charset=utf-8 "pageencoding=" UTF-8 "%>

Step Two:

For the HTML page itself:

<meta http-equiv= "Content-type" content= "text/html; Charset=utf-8 ">

Step Three:

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

To do as above, basically the code in JSP completely no problem, guarantee is UTF-8.

6. Check the connection process whether there is a problem, landlord is here out of the problem, the results for a long time ...

When linking a database in a 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 native host

Root is the user name of the connection database

Password1 is the password to connect to the database

USEUNICODE=TRUE&CHARACTERENCODING=UTF-8 Specifies the encoding used by the connection

Landlord once also thought in connection useless useunicode=true&characterencoding=utf-8 everything, after connecting with set names UTF8 way, but no success.

Above, the problem is completely solved, just in case, we recommend that the IDE encoding set to UTF-8, what Eclipse myeclipse netbeanse you understand ....

The MySQL database already has tables and columns in it if the first encoding is not UTF-8, the way to set up UTF-8 can be as follows.

Note: Errors may occur if there is data in the table. 1. Change the encoding of the database to UTF-8

Alter DATABASE ' test ' DEFAULT CHARACTER SET UTF8 COLLATE utf8_bin

The above command is to set the test database encoding to UTF8.

2. Change the encoding of the table to UTF-8

Alter TABLE ' category ' DEFAULT CHARACTER SET UTF8 COLLATE utf8_bin

The above command is to change the encoding of a table category to UTF8.

3. Change the encoding of the fields (each column) in the table to UTF-8

Alter TABLE ' test ' change ' dd ' "dd ' VARCHAR" CHARACTER SET UTF8 COLLATE utf8_bin not NULL

The above command is to change the field encoding of DD in the Test table to UTF8.

This command can also be specified in fields that require special encoding, such as in some symbols in Chinese, utf8, which can be set to GBK, which can store this symbol, but remember to transcode 4 when accessing the data. Set other (default) encodings:

In the MySQL installation directory, open Mysql.ini, all the character behind the code to change to UTF8, so that the creation of new databases and tables when the default encoding, MySQL restart does not expire.