MySQL encoding format. mysql modifies the encoding format.

MySQL encoding format. mysql modifies the encoding format.

I. Problem Introduction

When mysql is installed, the encoding format is configured as UTF-8. Therefore, mysql character_set_client, character_set_connection, character_set_database, character_set_results, character_set_server, and character_set_system are encoded in UTF-8.

The command to view the encoding format is 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 Server 5.5 \ share
\ Charsets \ |

Then the problem arises. After the data records with Chinese characters are inserted into the database in UTF-8 format in the code, query the data through the cmd command line and find that the Chinese fields are garbled, why? The database encoding format is utf8, and tables and fields are also utf8. The stored format is UTF-8.

Code for inserting 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"); ps = 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 (); ps. addBatch (); // ps. addBatch (SQL); // This method is inherited from the Statement interface by the PreparedStatement interface .} Ps.exe cuteBatch (); conn. commit (); long end = System. currentTimeMillis (); System. out. println ("20000 records of batch insertion time:" + (end-start) + "millisecond. ");} catch (ClassNotFoundException e) {e. printStackTrace ();} catch (SQLException e) {e. printStackTrace ();} finally {try {if (ps! = Null &&! Ps. isClosed () {ps. close () ;}} catch (SQLException e) {e. printStackTrace () ;}try {if (conn! = Null &&! Conn. isClosed () {conn. close () ;}} catch (SQLException e) {e. printStackTrace ();}}}

Query results:

Mysql> select * from t_user;
+ ---- + ---------- + --------------------- +
| Id | name | birth |
+ ---- + ---------- + --------------------- +
| 1 | invalid bandwidth limit 0 | 00:00:00 |
| 2 | invalid bandwidth limit 1 | 00:00:00 |
| 3 | invalid bandwidth limit 2 | 00:00:00 |
| 4 | invalid bandwidth limit 3 | 00:00:00 |
| 5 | invalid bandwidth limit 4 | 00:00:00 |
| 6 | invalid bandwidth limit 5 | 00:00:00 |
| 7 | invalid bandwidth limit 6 | 00:00:00 |
| 8 | invalid bandwidth limit 7 | 00:00:00 |
| 9 | invalid bandwidth limit 8 | 00:00:00 |
| 10 | 00:00:00 |
| 11 | too many 10 | 09:18:01 |
+ ---- + ---------- + --------------------- +
11 rows in set (0.00 sec)

The above garbled error occurs because the mysql command line window cannot return data in UTF-8 format.

Ii. Solution

After logging on, you can use the command line to temporarily set the encoding format of the mysql client and result set to gbk or gb2312, but note that gb18030 is not allowed.

Mysql> set names gbk;

Then, run the command to view the current encoding format of mysql.

Mysql> show variables like 'character % ';

The result is 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 Server 5.5 \ share
\ Charsets \ |

The encoding formats of character_set_client, character_set_connection, and character_set_results are now gbk.

The command line query result is as follows:

+ ---- + -------- + --------------------- +
| Id | name | birth |
+ ---- + -------- + --------------------- +
| 1 | Zhang San 0 | 00:00:00 |
| 2 | Zhang San 1 | 00:00:00 |
| 3 | Zhang San 2 | 00:00:00 |
| 4 | Zhang San 3 | 00:00:00 |
| 5 | Zhang San 4 | 00:00:00 |
| 6 | Zhang San 5 | 00:00:00 |
| 7 | Zhang San 6 | 00:00:00 |
| 8 | Zhang San 7 | 00:00:00 |
| 9 | Zhang San 8 | 00:00:00 |
| 10 | Zhang San 9 | 00:00:00 |
| 11 | James 10 | 09:18:01 |
+ ---- + -------- + --------------------- +
11 rows in set (0.02 sec)

The garbled problem has been solved.

Iii. Knowledge Point supplement

(1) The data encoding format in MySQL has been segmented into the unit "column ". When creating a database, you can specify the encoding format of the database. The encoding format of the created tables and columns is the default format. If you want to change the encoding format of the database later, you have to change the encoding format of the previous tables and columns one by one. Therefore, we do not need to worry about the encoding format of the database, as long as the MYSQL installation directory X: \ Program File \ MySQL5 find a my. ini file. Open it in notepad and find default-character-set = the encoding format you want to set. modify the format. Do not set other parameters after creating a database, creating a table, or creating a field unless you have special requirements.
(2) When using MySQL, note that the encoding formats corresponding to databases, tables, and columns do not have a ing relationship. You can set the encoding format when creating databases, 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 (50) character set gbk;

(3) view the encoding formats of databases, tables, and fields

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 formats of tables and fields

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

|
+ -------- + ----------------------------------------------------
--------------------------------------------------------------
----------------------------------------------------------- +
| T_user | create table 't_ user '(
'Id' int (11) not null AUTO_INCREMENT,
'Name' varchar (40) character set utf8 default null,
'Birth' datetime default null,
Primary key ('id ')
) ENGINE = InnoDB default charset = gbk |
+ -------- + ----------------------------------------------------
--------------------------------------------------------------
----------------------------------------------------------- +
1 row in set (0.00 sec)