How to solve the problem of Chinese garbled characters when inserting table data into MySQL

1. Problem

During development, when inserting data from other databases into the MySQL database table, an error similar to the following is always reported:

Incorrect string value: '\xE6\x88\x91' for column 'name' at row 1

2. Analysis

1. I first checked the types of this field in the two database tables and found that they are both varchar, so there is no problem of insertion error caused by inconsistent types.

2. After eliminating the error caused by inconsistent field types, I guessed that it was a problem with Chinese garbled characters. Then I inserted pure English data and found that there was no error, so I determined that it was a problem with Chinese garbled characters. Then I searched Baidu and found that the garbled Chinese characters were caused by the default encoding problem of MySQL.

3. Solution

1. Check the my.ini configuration file in the local MySQL installation file directory to see if the default encoding of the server and client is utf8.

[mysqld]
# The default character set used by the server is UTF8
character-set-server=utf8

[client]
# Set the default port used by the mysql client to connect to the server default-character-set=utf8

2. After the previous step is correct, open the command line and enter: net start mysql to start the MySQL service;

After the message "The service has been successfully started" is displayed, enter the command "mysql" to use the database;

Check show create table test.xtt_test_copy; "

As shown in the figure above, it is found that the default is "latin1" instead of "utf8". You need to continue to modify it manually. Enter the command " ALTER TABLE test.xtt_test_copy CONVERT TO CHARACTER SET utf8 COLLATE utf8_general_ci; ", and continue to enter the command " show create table test.xtt_test_copy; has been changed to "utf8";

Check show creat database test; "

As shown in the figure above, the default is "latin1" instead of "utf8". You need to continue to modify it manually. Enter the command " ALTER DATABASE test DEFAULT CHARACTER SET utf8 COLLATE utf8_general_ci; ", and continue to enter the command " SHOW CREATE database test; " to check that the character set encoding of the default database table has been changed to "utf8";

3. Finally, restart the service.

mysql> net stop mysql;
mysql> net start mysql;

In our daily use of MySQL, we often encounter Chinese garbled characters. Based on my usual work experience, I have summarized the following four points that need attention:

1. The character set when MySQL is started, configure default-character-set=character set in the myini file and put it above [WinMySQLadmin];

2. Set the character set during database table creation, in the CHARSET= character set statement at the end of the CREATE statement;

3. Set the character set in the options in the driver url, jdbc:mysql://localhost:3306/test?useUnicode=true&characterEncoding=character set;

4. Set the displayed character set in the web display page, <%@ page contentType="text/html; charset=character set" language="java" import="java.sql.*" errorPage="" %>In servlet, it is response.setContentType("text/html; charset=character set");

Summarize

The above is the full content of this article. I hope that the content of this article will have certain reference learning value for your study or work. If you have any questions, you can leave a message to communicate. Thank you for your support for 123WORDPRESS.COM.