Java solution to garbled data inserted into mysql

This article mainly introduces in detail the solution to the problem of Java inserting garbled data into mysql. It has a certain reference value. Interested friends can refer to it.

Encountered Java inserting into mysql How to solve the problem of garbled data?

MySQL default 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 ("测试MySQL中文显示"); 
mysql> select * from messages; 
+----+-------------------+ 
| id | message      | 
+----+-------------------+ 
| 1 | 测试MySQL中文显示 | 
+----+-------------------+

Writing a program (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.executeUpdate("insert into messages (message) values ('测试MySQL编码')"); 
      ResultSet rs = stmt.executeQuery("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? ????????? Chinese cannot be displayed normally, so we need to modify the default encoding of MySQL and edit the my.ini (MySQL configuration file) file to modify the encoding

Set the default character set of MySQL to utf8 and find the customer The client configuration [client] is added below.

default-character-set=utf8

Find the server configuration [mysqld] and add it below
default-character-set=utf8

Set the MySQL database to run with utf8 encoding, use utf8 encoding when connecting to the MySQL database Stop and restart MySQL

net stop mysql

net start mysql

Reconnect to the database, check the encoding, and the 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 | 虏芒脢脭MySQL脰脨脦脛脧脭脢戮       | 
| 2 | ??MySQL??           | 
+----+-------------------------------+

The display here is still garbled mainly because the encoding used before is different. Re- Run the program written before: java JDBCTest

1 ????MySQL????????
2 ??MySQL??

3 Test MySQL encoding

From the third record we can see that the program can now add and display Chinese normally when connecting to the database

mysql> select * from messages; 
+----+-------------------------------+ 
| id | message            | 
+----+-------------------------------+ 
| 1 | 虏芒脢脭MySQL脰脨脦脛脧脭脢戮       | 
| 2 | ??MySQL??           | 
| 3 | 娴嬭瘯MySQL缂栫爜         | 
+----+-------------------------------+

Looking back at the database display, we will be surprised to find out why the displayed characters are all garbled. This is mainly related to the encoding of the command line under Windows. View the current code page of the Properties-> option on the command line: 936 (ANSI/OEM - Simplified Chinese GBK) (This is how it is displayed on my machine)

That is to say, the GBK encoding is used on the command line, and we use utf8 to add it when the program is connected, so there will be Garbled characters, now let's change the client's encoding to gb2312 or gbk and try it

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 | 测试MySQL编码   | 
+----+-------------------+

Now you can see that Chinese is displayed normally (mainly because utf8 also supports Chinese), so When we use the command line tool to connect to the database, it is best to change the client's encoding. If you use a GUI, it is not necessary. At the same time, after modifying the client's encoding, the program can still display normally (the above two points have been tested)

So if we want to display Chinese in the program, we can choose three encodings: utf8, gb2312, and gbk. However, if we want to add Chinese data or view it on the command line, we need to set the client's encoding to gb2312 or gbk. Still the same Sentence, related to CMD encoding.

The above is the detailed content of Java solution to garbled data inserted into mysql. For more information, please follow other related articles on the PHP Chinese website!