MySQL encoding format

First, the problem leads

When installing MySQL is to configure its encoding format to Utf-8, so at this point MySQL character_set_client, character_set_connection, Character_set_database, Character_set_results, Character_set_server, Character_set_system encoding formats are utf-8.

The commands to view the encoded format are as follows:

Mysql> Show variables like ' character% ';

Current MySQL encoding format:

| 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:\Program Files (x86) \mysql\mysql Server 5.5\share
\charsets\ |

So the problem, in the code in the format of Utf-8 with Chinese characters in the data record inserted into the database, and then through the CMD command line query data, found that the text field display garbled, this is why? The database encoding format is UTF8, and the tables and fields are also UTF8, and the format in which they are stored is utf-8.

Code to insert Data

@Testpublic void TestBatch02 () {Connection conn = null; PreparedStatement PS = null;try {class.forname ("Com.mysql.jdbc.Driver"); conn = Drivermanager.getconnection ("JDBC: Mysql://localhost:3306/testjdbc "," root "," root ");p s = conn.preparestatement (" INSERT into T_user (name, birth) values (? , ?)"); Conn.setautocommit (false); long start = System.currenttimemillis (); for (int i=0; i<10; i++) {ps.setstring (1, "Zhang San" + i); Ps.setdate (2, New Java.sql.Date (New Date (). GetTime ()));p S.addbatch ();//ps.addbatch (SQL); This method is inherited from the statement interface by the PreparedStatement interface. }ps.executebatch (); Conn.commit (); Long end = System.currenttimemillis (); System.out.println ("20,000 Records BULK Insert time:" + (End-start) + "milliseconds."); catch (ClassNotFoundException e) {e.printstacktrace ()} catch (SQLException e) {e.printstacktrace ();} finally {try {if (p s! = null &&!ps.isclosed ()) {ps.close ();}} catch (SQLException e) {e.printstacktrace ();} try {if (conn! = null &&!conn.isclosed ()) {conn.close ();}} catch (SQLException e) {e.printsTacktrace ();}}} 

Query Result:

Mysql> select * from T_user;
+----+----------+---------------------+
| id | name     | birth                |
+----+----------+---------------------+
|  1 | liao incrementally 笁 0    | 2015-05-03 00:00:00 |
|   2 | Liao Incrementally 笁 1    | 2015-05-03 00:00:00 |
|  3 | Liao incrementally 笁 2    | 2015-05-03 00:00:00 |
|  4 | Liao incrementally 笁 3    | 2015-05-03 00:00:00 |
|  5 | liao incrementally 笁 4    | 2015-05-03 00:00:00 |
|  6 | Liao incrementally 笁 5    | 2015-05-03 00:00:00 |
|  7 | Liao incrementally 笁 6    | 2015-05-03 00:00:00 |
|  8 | Liao incrementally 笁 7    | 2015-05-03 00:00:00 |
|  9 | Liao incrementally 笁 8    | 2015-05-03 00:00:00 |
| 10 | liao incrementally 笁 9    | 2015-05-03 00:00:00 |
| 11 | Liao incrementally 笁 10   | 2015-05-03 09:18:01 |
+----+----------+---------------------+
One rows in Set (0.00 sec)

The problem with the above garbled characters is that the MySQL command-line window simply cannot return data in utf-8 format.

Second, the solution

After logging in, the encoding format of the MySQL client and result set is temporarily set to GBK or gb2312 through the command line, but note that GB18030 is not possible.

mysql> set names GBK;

After setting, view MySQL current encoding format by command

Mysql> Show variables like ' character% ';

The results are as follows:

| character_set_client     | GBK
            |
| character_set_connection | GBK
           |
| character_set_database   | UTF8
           |
| character_set_filesystem | binary
           |
| character_set_results    | GBK
            |
| character_set_server     | UTF8
            |
| character_set_system     | UTF8
            |
| character_sets_dir       | D:\Program Files (x86) \mysql\mysql Server 5.5\share
\charsets\ |

At this point you can see Character_set_client, character_set_connection, character_set_results encoding format is now GBK.

In the command line query, the results are as follows:

+----+--------+---------------------+
| ID | name | Birth |
+----+--------+---------------------+
| 1 | Sheet 30 | 2015-05-03 00:00:00 |
| 2 | Sheet 31 | 2015-05-03 00:00:00 |
| 3 | Sheet 32 | 2015-05-03 00:00:00 |
| 4 | Sheet 33 | 2015-05-03 00:00:00 |
| 5 | Sheet 34 | 2015-05-03 00:00:00 |
| 6 | Sheet 35 | 2015-05-03 00:00:00 |
| 7 | Sheet 36 | 2015-05-03 00:00:00 |
| 8 | Sheet 37 | 2015-05-03 00:00:00 |
| 9 | Sheet 38 | 2015-05-03 00:00:00 |
| 10 | Sheet 39 | 2015-05-03 00:00:00 |
| 11 | Sheet 310 | 2015-05-03 09:18:01 |
+----+--------+---------------------+
Rows in Set (0.02 sec)

The problem of garbled characters has been solved.

Third, the Knowledge point supplement

(1) The data encoding format in MySQL has been hashed to the unit "column". The database encoding format can be specified when the database is being built, and the formatting of the tables and columns will be formatted as the default format. If you want to change the encoding format of the database later, you want to change the format of the previous table and the column encoding, you will have to change it all over. So, we want to make no worry about the database encoding format, as long as the MySQL installation directory below X:\Program file\mysql5 find a My.ini file, open with Notepad to find default-character-set= you want to set the encoding format, You can modify its format. Build a library, build a table, build a field in the back, and don't make any other settings, unless you want to ask for it.
(2) in MySQL to note that the database, table, column corresponding encoding format does not contain the relationship. You can set the encoding format when you are building libraries, tables, and columns.

mysql> ALTER DATABASE TESTJDBC character set GBK;

mysql> ALTER TABLE T_user character set GBK;

Mysql> ALTER TABLE T_user modify name varchar () character set GBK;

(3) View the encoding format of the database, table, field

1) View the database encoding format

Mysql> Show CREATE Database testjdbc;
+----------+------------------------------------------------------------------+
| Database | Create Database |
+----------+------------------------------------------------------------------+
| Testjdbc | CREATE DATABASE ' testjdbc '/*!40100 DEFAULT CHARACTER SET gbk */| |
+----------+------------------------------------------------------------------+
1 row in Set (0.00 sec)

2) View the encoding format for tables and fields

Mysql> Show CREATE TABLE T_user;
+--------+----------------------------------------------------
--------------------------------------------------------------
-----------------------------------------------------------+
| Table | Create Table

|
+--------+----------------------------------------------------
--------------------------------------------------------------
-----------------------------------------------------------+
| T_user | CREATE TABLE ' T_user ' (
' id ' int (one) not NULL auto_increment,
' Name ' varchar (40)CHARACTER SET UTF8DEFAULT NULL,
' Birth ' datetime DEFAULT NULL,
PRIMARY KEY (' id ')
) Engine=innodbDEFAULT CHARSET=GBK|
+--------+----------------------------------------------------
--------------------------------------------------------------
-----------------------------------------------------------+
1 row in Set (0.00 sec)

MySQL encoding format