How to solve Chinese garbled characters written into MySQL using Java
How to solve the problem of garbled characters in MySQL written in Java:
Method 1:
When the page value contains Chinese characters, you can use the filter to compile it. However, it is easy to use System. out. println to output Chinese characters and insert it into Mysql. It is garbled. (Chao, your free space may also be the problem)
What should we do? Do the following:
1. When creating a database:
CREATEDATABASE 'db'
CHARACTERSET 'utf8'
COLLATE 'utf8 _ general_ci ';
2. When creating a table:
CREATETABLE 'tablea '(
'Id' varchar (40) NOTNULLdefault '',
'Userid' varchar (40) notnulldefault '',)
ENGINE = InnoDB default charset = utf8;
3. When setting the URL:
Jdbc: mysql: /// localhost: 3306/database? UseUnicode = true & amp; characterEncoding = UTF-8
Method 2:
The default MySQL encoding is latin1.
- Mysql> show variables like 'character % ';
- + -------------------------- +
- | Variable_name | Value |
- + -------------------------- +
- | Character_set_client | latin1 |
- | Character_set_connection | latin1 |
- | Character_set_database | latin1 |
- | Character_set_filesystem | binary |
- | Character_set_results | latin1 |
- | Character_set_server | latin1 |
- | Character_set_system | utf8 |
- | Character_sets_dir | D: \ MySQL \ share \ charsets \ |
- + -------------------------- +
- Create a data table and insert data
- Mysql> use test;
- Mysql> create table messages (
- -> Id int (4) unsigned auto_increment primary key,
- -> Message varchar (50) not null
- ->) Engine = myisam default charset = utf8;
- Mysql> insert into messages (message) values ("test MySQL Chinese display ");
- Mysql> select * from messages;
- + ---- + ------------------- +
- | Id | message |
- + ---- + ------------------- +
- | 1 | test MySQL Chinese display |
- + ---- + ------------------- +
- Programming (Java)
- Import java. SQL. Connection;
- Import java. SQL. DriverManager;
- Import java. SQL. ResultSet;
- Import java. SQL. Statement;
- Public class JDBCTest {
- Public static void main (String [] args ){
- String driver = "com. mysql. jdbc. Driver ";
- String url = "jdbc: mysql: // localhost: 3306/test ";
- String user = "root ";
- String password = "root ";
- Try {
- Class. forName (driver );
- Connection conn = DriverManager. getConnection (url, user, password );
- Statement stmt = conn. createStatement ();
- Stmt.exe cuteUpdate ("insert into messages (message) values ('test MySQL Code ')");
- ResultSet rs = stmt.exe cuteQuery ("select * from messages ");
- While (rs. next ()){
- Int id = rs. getInt ("id ");
- String message = rs. getString ("message ");
- System. out. println (id + "" + message );
- }
- Rs. close ();
- Stmt. close ();
- Conn. close ();
- } Catch (Exception e ){
- E. printStackTrace ();
- }
- }
- }
- Program output
- 1 ???? MySQL ????????
- 2 ?? MySQL ??
We can see that although we can add and display Chinese characters normally when using the database, Chinese characters cannot be displayed normally when using the program to connect to the database. Therefore, we need to modify the default MySQL encoding, edit my. the ini (MySQL configuration file) file modifies the encoding.
Set the default Character Set of MySQL to utf8. Find the client configuration [client] and add it below.
Default-character-set = utf8
Find the server configuration [mysqld] and add it below
Default-character-set = utf8
Set the MySQL database to run in utf8 encoding and use utf8 encoding when connecting to the MySQL database.
Stop and restart MySQL
Net stop mysql
Net start mysql
Reconnect to the database and view the encoding and data table content.
- Mysql> show variables like 'character % ';
- + -------------------------- +
- | 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 \ share \ charsets \ |
- + -------------------------- +
- Mysql> use test;
- Mysql> select * from messages;
- + ---- + --------------------------------- +
- | Id | message |
- + ---- + --------------------------------- +
- | 1 | why does one need to modify MySQL statements?
- | 2 | ?? MySQL ?? |
- + ---- + --------------------------------- +
- It is still garbled here mainly because of the different encoding used previously. re-run the previously written Program: java JDBCTest
- 1 ???? MySQL ????????
- 2 ?? MySQL ??
- 3. Test MySQL Encoding
- From the third record, we can see that Chinese characters can be properly added and displayed when the program connects to the database.
- Mysql> select * from messages;
- + ---- + --------------------------------- +
- | Id | message |
- + ---- + --------------------------------- +
- | 1 | why does one need to modify MySQL statements?
- | 2 | ?? MySQL ?? |
- | 3 | zookeeper MySQL zookeeper |
- + ---- + --------------------------------- +
Looking back at the display of the database, we will be very surprised to find out why the display is garbled, which is mainly related to the command line encoding in windows, view the current code page of the attribute> option on the command line: 936 (ANSI/OEM-Simplified Chinese GBK)
That is to say, the command line uses GBK encoding, while we use utf8 to add it during program connection, so garbled characters may appear, now let's change the client encoding to gb2312 or gbk.
- Mysql> show variables like 'character % ';
- + -------------------------- +
- | Variable_name | Value |
- + -------------------------- +
- | Character_set_client | gb2312 |
- | Character_set_connection | gb2312 |
- | Character_set_database | utf8 |
- | Character_set_filesystem | binary |
- | Character_set_results | gb2312 |
- | Character_set_server | utf8 |
- | Character_set_system | utf8 |
- | Character_sets_dir | D: \ MySQL \ share \ charsets \ |
- + -------------------------- +
- Mysql> use test;
- Mysql> select * from messages;
- + ---- + ------------------- +
- | Id | message |
- + ---- + ------------------- +
- | 1 | ???? MySQL ???????? |
- | 2 | ?? MySQL ?? |
- | 3 | test MySQL encoding |
- + ---- + ------------------- +
Now we can see that the Chinese language is normally displayed (mainly because utf8 also supports Chinese), so when we use the command line tool to connect to the database, we 'd better change the client encoding, if the GUI is used, the program can still be displayed normally after the client encoding is modified (the above two points have been tested ).
So if you want to display Chinese characters in the program, you can use utf8, gb2312, and gbk encoding, however, if you want to add or view Chinese data in the command line, you need to set the client encoding to gb2312 or gbk. It is still related to the CMD encoding.
This article permanently updates the link address:
