Mysql Chinese garbled solution ideas and processes _ MySQL

Mysql Chinese garbled solution ideas and processes bitsCN.com

Mysql Chinese garbled text is a common problem, and it is not difficult to solve it. This article will be guided by how to find and solve the problem when I encounter it, to present the ideas and processes for analyzing and solving the problem.

During Java EE project development, common Chinese garbled text problems are divided into web front-end, web back-end, and database garbled text. to view Chinese garbled text on the web end, refer to the blog article.

Problem:

First, create a user, as shown in 1:

When you click add user, the userName field in the database is garbled:

mysql> select * from t_user;+--------+----------+------+---------------------+-------+| userId | userName | age  | birthday            | isVip |+--------+----------+------+---------------------+-------+|      1 | ????     |    0 | 2014-01-01 00:00:00 |     1 |+--------+----------+------+---------------------+-------+1 row in set (0.00 sec)

In the Java web project, before solving the garbled text problem, make sure that the garbled text occurs in the database. if the garbled text occurs on the database end, try the following steps to find the cause:
Step 1: View the database encoding status, as shown below:

mysql> status;--------------mysql  Ver 14.12 Distrib 5.0.22, for Win32 (ia32)Connection id:          3Current database:       stevenCurrent user:           root@localhostSSL:                    Not in useUsing delimiter:        ;Server version:         5.0.22-community-ntProtocol version:       10Connection:             localhost via TCP/IPServer characterset:    latin1Db     characterset:    latin1Client characterset:    latin1Conn.  characterset:    latin1TCP port:               3306Uptime:                 2 hours 10 min 15 secThreads: 1  Questions: 121  Slow queries: 0  Opens: 2  Flush tables: 1  Open tables: 0  Queries per second avg: 0.015--------------

It is found that the character encoding of Server, Db, Client, and Conn is latin1, so Chinese characters may be garbled. Solution 1:(Window) reset the encoding scheme through MySQL Server Instance Configuration Wizard, as shown in Figure 2:
Next, and then select the third item in Please select the database usage, as shown in 3:
Next until Please select the default character set. select gbk for character encoding, as shown in Figure 4:
At this time, continue to Next. after confirming the password, click Next and Execute, as shown in Figure 5:
After you Finish, log out of the database command console and go in to view the database encoding status, as shown below:

mysql> use steven;Database changedmysql> status;--------------mysql  Ver 14.12 Distrib 5.0.22, for Win32 (ia32)Connection id:          2Current database:       stevenCurrent user:           root@localhostSSL:                    Not in useUsing delimiter:        ;Server version:         5.0.22-community-ntProtocol version:       10Connection:             localhost via TCP/IPServer characterset:    gbkDb     characterset:    latin1Client characterset:    gbkConn.  characterset:    gbkTCP port:               3306Uptime:                 1 min 20 secThreads: 1  Questions: 12  Slow queries: 0  Opens: 0  Flush tables: 1  Open tables: 6  Queries per second avg: 0.150--------------

It is found that the encoding except Db characterset is changed to gbk. this is Solution 1 and can still be changed.
Solution 2: Find the Mysql installation directory through the configuration file, and find the my. ini file under the root directory, as shown in 6:
Then, make the following changes, as shown in 7:
Change the gbk encoding method of latin1 and restart the database service, as shown below:

C:/Users/Administrator>net stop mysqlThe MySQL service is stopping.The MySQL service was stopped successfully.C:/Users/Administrator>net start mysqlThe MySQL service is starting.The MySQL service was started successfully.

This is the same as solution 1, but it does not solve the problem at this time.

mysql> use steven;Database changedmysql> status;--------------mysql  Ver 14.12 Distrib 5.0.22, for Win32 (ia32)Connection id:          2Current database:       stevenCurrent user:           root@localhostSSL:                    Not in useUsing delimiter:        ;Server version:         5.0.22-community-ntProtocol version:       10Connection:             localhost via TCP/IPServer characterset:    gbkDb     characterset:    latin1Client characterset:    gbkConn.  characterset:    gbkTCP port:               3306Uptime:                 1 min 20 secThreads: 1  Questions: 12  Slow queries: 0  Opens: 0  Flush tables: 1  Open tables: 6  Queries per second avg: 0.150--------------

Step 2: Change the encoding method of Db characterset and execute the following SQL statement:

-- Modify the database encoding to gbk alter database steven character set gbk;

Among them, steven is the database where the data table is garbled. in this case, execute status check.

mysql> status;--------------mysql  Ver 14.12 Distrib 5.0.22, for Win32 (ia32)Connection id:          2Current database:       stevenCurrent user:           root@localhostSSL:                    Not in useUsing delimiter:        ;Server version:         5.0.22-community-ntProtocol version:       10Connection:             localhost via TCP/IPServer characterset:    gbkDb     characterset:    gbkClient characterset:    gbkConn.  characterset:    gbkTCP port:               3306Uptime:                 8 min 30 secThreads: 1  Questions: 32  Slow queries: 0  Opens: 1  Flush tables: 1  Open tables: 7  Queries per second avg: 0.063--------------

At this time, the encoding format of gbk is changed. Note: If the database encoding method is gbk by default, the problem is generally solved. if the problem persists, the following exception occurs when Chinese data is inserted:

com.mysql.jdbc.MysqlDataTruncation: Data truncation: Data too long for column 'userName' at row 1at com.mysql.jdbc.MysqlIO.checkErrorPacket(MysqlIO.java:3489)at com.mysql.jdbc.MysqlIO.checkErrorPacket(MysqlIO.java:3423)at com.mysql.jdbc.MysqlIO.sendCommand(MysqlIO.java:1936)at com.mysql.jdbc.MysqlIO.sqlQueryDirect(MysqlIO.java:2060)at com.mysql.jdbc.ConnectionImpl.execSQL(ConnectionImpl.java:2542)at com.mysql.jdbc.PreparedStatement.executeInternal(PreparedStatement.java:1734)at com.mysql.jdbc.PreparedStatement.executeUpdate(PreparedStatement.java:2019)at com.mysql.jdbc.PreparedStatement.executeUpdate(PreparedStatement.java:1937)at com.mysql.jdbc.PreparedStatement.executeUpdate(PreparedStatement.java:1922)at com.steven.util.DaoHandle.executeDML(DaoHandle.java:49)at com.steven.dao.impl.UserDao.doCreate(UserDao.java:33)at com.steven.model.UserAddAction.execute(UserAddAction.java:80)at com.steven.controller.ActionServlet.doPost(ActionServlet.java:40)at javax.servlet.http.HttpServlet.service(HttpServlet.java:643)at javax.servlet.http.HttpServlet.service(HttpServlet.java:723)at org.apache.catalina.core.ApplicationFilterChain.internalDoFilter(ApplicationFilterChain.java:290)at org.apache.catalina.core.ApplicationFilterChain.doFilter(ApplicationFilterChain.java:206)at com.steven.util.EncodeFilter.doFilter(EncodeFilter.java:35)at org.apache.catalina.core.ApplicationFilterChain.internalDoFilter(ApplicationFilterChain.java:235)at org.apache.catalina.core.ApplicationFilterChain.doFilter(ApplicationFilterChain.java:206)at org.apache.catalina.core.StandardWrapperValve.invoke(StandardWrapperValve.java:233)at org.apache.catalina.core.StandardContextValve.invoke(StandardContextValve.java:191)at org.apache.catalina.core.StandardHostValve.invoke(StandardHostValve.java:127)at org.apache.catalina.valves.ErrorReportValve.invoke(ErrorReportValve.java:103)at org.apache.catalina.core.StandardEngineValve.invoke(StandardEngineValve.java:109)at org.apache.catalina.connector.CoyoteAdapter.service(CoyoteAdapter.java:293)at org.apache.coyote.http11.Http11Processor.process(Http11Processor.java:861)at org.apache.coyote.http11.Http11Protocol$Http11ConnectionHandler.process(Http11Protocol.java:606)at org.apache.tomcat.util.net.JIoEndpoint$Worker.run(JIoEndpoint.java:489)at java.lang.Thread.run(Thread.java:722)

If the data is too long, the default encoding method for viewing the data table is as follows:

mysql> show create table t_user;+--------+------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+| Table  | Create Table                                        |+--------+------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+| t_user | CREATE TABLE `t_user` (  `userId` int(10) NOT NULL auto_increment,  `userName` varchar(100) NOT NULL,  `age` int(2) default NULL,  `birthday` datetime default NULL,  `isVip` tinyint(1) default NULL,  PRIMARY KEY  (`userId`)) ENGINE=MyISAM DEFAULT CHARSET=latin1 |+--------+------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+1 row in set (0.00 sec)

The default data table encoding is latin1. The cause of the exception is that the Chinese encoding does not match the default table encoding, causing data conflicts.
Step 3: Solve the exception and execute the following two statements to change the default encoding method of the database; Solution 1: Drop the table and create a new table. The table creation statement is as follows;

create table t_user(userId    int(10) not null primary key auto_increment,userName    varchar(100) not null,age        int(2),birthday    datetime,isVip       boolean) DEFAULT CHARSET=gbk;

Solution 2: Execute the following two commands for mysql:

-- Modify the table to use gbkalter table t_user character set gbk by default; -- modify the userName field to be encoded as gbkalter table t_user modify userName varchar (100) character set gbk;

After the execution, the encoding method of the database fields and tables is changed to gbk. when inserting data, the data in the data table is:

Mysql> select * from t_user; + -------- + ---------- + ------ + ------------------- + ------- + | userId | userName | age | birthday | isVip | + -------- + ---------- + ------ + --------------------- + ------- + | 1 | ???? | 0 | 00:00:00 | 1 | 2 | happy new year | 0 | 00:00:00 | 1 | + -------- + ---------- + ------ + ----------------------- + ------- + 2 rows in set (0.00 sec)

The first is the data inserted when garbled characters are involved, and the second is the data inserted after the solution. at this time, the Mysql database encoding has been fixed.

Summary: The database garbled solution can be summarized as follows: use the software or configuration file to first modify the encoding and use the mysql command to change the database encoding (if the default encoding supports Chinese characters, this step can be omitted.) Data too long for column ...... An exception occurs. you can solve the problem of modifying the Chinese character encoding in the mysql database. the encoding can also be set to utf8 encoding, but sometimes the encoding in the database is traditional Chinese, then run the set names "gbk" command to display simplified Chinese.
The following provides common mysql commands for modifying Chinese character garbled characters:

Set names 'gbk'; -- it is equivalent to the following three commands: set character_set_client = gbk; set character_set_results = gbk; set character_set_connection = gbk; -- display the data table encoding show create table t_user (table name); -- modify the database encoding to gbk alter database steven (database name) character set gbk; -- modify the table to use gbkalter table t_user (table name) character set gbk by default; -- modify the userName field to utf8alter table t_user (table name) modify userName (table field name) varchar (100) character set gbk; -- create table t_user (userId int (10) not null primary key auto_increment, userName varchar (100) not null, age int (2 ), birthday datetime, isVip boolean) default charset = gbk;

BitsCN.com