程序師世界是廣大編程愛好者互助、分享、學習的平台,程序師世界有你更精彩!
首頁
編程語言
C語言|JAVA編程
Python編程
網頁編程
ASP編程|PHP編程
JSP編程
數據庫知識
MYSQL數據庫|SqlServer數據庫
Oracle數據庫|DB2數據庫
 程式師世界 >> 數據庫知識 >> MYSQL數據庫 >> MySQL綜合教程 >> Mysql中文亂碼解決思路和過程

Mysql中文亂碼解決思路和過程

編輯:MySQL綜合教程

Mysql 中文亂碼問題是常見的問題,解決也是不難的,本文將通過我在遇到問題時候,如何的查找和解決為導向,來呈現分析解決問題的思路和過程。

在JavaEE項目開發中,通常的中文亂碼問題分為web前端,web後端,數據庫亂碼,如果想查看web端的中文亂碼問題,請參照博文http://blog.csdn.net/songdeitao/article/details/17577823,一但排除了web端的中文亂碼,也就說亂碼問題是在數據庫端發生的了。

問題產生:

首先在創建用戶,如圖1所示:

\

圖1

在點擊增加用戶的時候,數據庫中userName字段顯示為亂碼:

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)

解決思路

在javaweb項目中,在解決亂碼問題之前,首先要確保是在數據庫這裡發生亂碼,如果確實在數據庫這端發生亂碼,請試著通過以下步驟查找原因:
步驟一:查看數據庫編碼狀態,如下所示:
mysql> status;
--------------
mysql  Ver 14.12 Distrib 5.0.22, for Win32 (ia32)

Connection id:          3
Current database:       steven
Current user:           root@localhost
SSL:                    Not in use
Using delimiter:        ;
Server version:         5.0.22-community-nt
Protocol version:       10
Connection:             localhost via TCP/IP
Server characterset:    latin1
Db     characterset:    latin1
Client characterset:    latin1
Conn.  characterset:    latin1
TCP port:               3306
Uptime:                 2 hours 10 min 15 sec

Threads: 1  Questions: 121  Slow queries: 0  Opens: 2  Flush tables: 1  Open tab
les: 0  Queries per second avg: 0.015
--------------

發現Server,Db,Client,Conn.的字符編碼均為latin1,所以一定會出現中文亂碼的情況。 解決方案一:(window下) 通過MySQL Server Instance Configuration Wizard重新設置編碼方案,如圖2所示: \
圖2 然後一直Next,然後到Please select the database usage 的時候選擇第三項,如圖3所示: \
圖3 接著一直Next,到Please select the default character set.的時候選擇gbk,字符編碼,如圖4所示: \
圖4 這個時候一直Next,確認密碼後,Next,然後就Execute執行,如圖5所示: \
圖5 Finish後,退出數據庫命令控制台然後再進去,查看數據庫編碼狀態,如下所示:
mysql> use steven;
Database changed
mysql> status;
--------------
mysql  Ver 14.12 Distrib 5.0.22, for Win32 (ia32)

Connection id:          2
Current database:       steven
Current user:           root@localhost
SSL:                    Not in use
Using delimiter:        ;
Server version:         5.0.22-community-nt
Protocol version:       10
Connection:             localhost via TCP/IP
Server characterset:    gbk
Db     characterset:    latin1
Client characterset:    gbk
Conn.  characterset:    gbk
TCP port:               3306
Uptime:                 1 min 20 sec

Threads: 1  Questions: 12  Slow queries: 0  Opens: 0  Flush tables: 1  Open tabl
es: 6  Queries per second avg: 0.150
--------------
發現編碼除了Db characterset外其他的都改為gbk了,這是方案一,依然可以實現這樣的更改。
解決方案二:通過配置文件,找到Mysql安裝的目錄,找到根目錄下my.ini文件,如圖6所示: \
圖6 然後打開後,進行如下更改,如圖7所示: \
圖7 將latin1更改gbk編碼方式,然後將數據庫服務重新啟動,如下所示:
C:\Users\Administrator>net stop mysql
The MySQL service is stopping.
The MySQL service was stopped successfully.


C:\Users\Administrator>net start mysql
The MySQL service is starting.
The MySQL service was started successfully.
這個時候和方案一一樣的效果,然而此時並沒有解決問題。
mysql> use steven;
Database changed
mysql> status;
--------------
mysql  Ver 14.12 Distrib 5.0.22, for Win32 (ia32)

Connection id:          2
Current database:       steven
Current user:           root@localhost
SSL:                    Not in use
Using delimiter:        ;
Server version:         5.0.22-community-nt
Protocol version:       10
Connection:             localhost via TCP/IP
Server characterset:    gbk
Db     characterset:    latin1
Client characterset:    gbk
Conn.  characterset:    gbk
TCP port:               3306
Uptime:                 1 min 20 sec

Threads: 1  Questions: 12  Slow queries: 0  Opens: 0  Flush tables: 1  Open tabl
es: 6  Queries per second avg: 0.150
--------------

步驟二:改變Db characterset的編碼方式,執行以下sql語句:
 --修改數據庫編碼為gbk
 alter database steven character set gbk; 
其中steven是數據表亂碼所在的數據庫,這個時候執行status查看
mysql> status;
--------------
mysql  Ver 14.12 Distrib 5.0.22, for Win32 (ia32)

Connection id:          2
Current database:       steven
Current user:           root@localhost
SSL:                    Not in use
Using delimiter:        ;
Server version:         5.0.22-community-nt
Protocol version:       10
Connection:             localhost via TCP/IP
Server characterset:    gbk
Db     characterset:    gbk
Client characterset:    gbk
Conn.  characterset:    gbk
TCP port:               3306
Uptime:                 8 min 30 sec

Threads: 1  Questions: 32  Slow queries: 0  Opens: 1  Flush tables: 1  Open tabl
es: 7  Queries per second avg: 0.063
--------------
此時都成為gbk的編碼格式了。 注:如果此時數據庫編碼默認就為gbk的編碼方式的,此時問題一般都已經解決了,如果還沒有解決,在插入含有中文數據的時候會有以下異常:
com.mysql.jdbc.MysqlDataTruncation: Data truncation: Data too long for column 'userName' at row 1
	at 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)

說數據過長,此時查看數據表默認編碼方式如下所示:
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)
會發現數據表默認編碼是latin1,所以異常的原因就是中文的編碼和表的默認編碼不匹配,造成數據沖突引起的。
步驟三:解決異常執行以下兩句進行更改數據庫的默認編碼方式; 方案一:drop表,然後重新建表,建表語句如下所示;
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;
方案二:mysql自帶的命令執行以下兩句命令:
--修改表默認用gbk
alter table t_user character set gbk;
--修改userName字段編碼為gbk
alter table t_user modify userName varchar(100) CHARACTER SET gbk; 
執行完之後,數據庫字段和表的編碼方式都更改為了gbk,此時在進行插入數據時候,數據表中的數據為:
mysql> select * from t_user;
+--------+----------+------+---------------------+-------+
| userId | userName | age  | birthday            | isVip |
+--------+----------+------+---------------------+-------+
|      1 | ????     |    0 | 2014-01-01 00:00:00 |     1 |
|      2 | 元旦快樂 |    0 | 2014-01-01 00:00:00 |     1 |
+--------+----------+------+---------------------+-------+
2 rows in set (0.00 sec)
第一條為亂碼時候插入的數據,第二條為解決後插入的數據,此時Mysql數據庫編碼已經解決了。

總結:對於數據庫亂碼解決方案,可以歸納如下: 通過軟件或者配置文件進行第一步修改編碼通過mysql命令更改數據庫編碼(如果默認編碼支持中文,此步驟就可以省略了)發生Data too long for column……異常,進行數據表字符編碼更改 一般情況的mysql數據庫中文編碼問題即可得到解決,編碼同樣可以設置成utf8編碼方式,但有時會出現數據庫中編碼是繁體字的現象,然後執行set names "gbk"命令,即可成功顯示簡體中文。
下面提供常用的修改中文字符亂碼的mysql命令:
set names 'gbk'; 
--它相當於下面的三句指令: 
set character_set_client = gbk; 
set character_set_results = gbk; 
set character_set_connection = gbk; 

--顯示數據表表的編碼
 show create table t_user(表的名稱);
 
 --修改數據庫編碼為gbk
 alter database steven(數據庫的名稱) character set gbk; 
 
--修改表默認用gbk
alter table t_user(表的名稱) character set gbk;

--修改userName字段編碼為utf8
alter table t_user(表的名稱) modify userName(表中字段的名稱) 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;

  1. 上一頁:
  2. 下一頁:
Copyright © 程式師世界 All Rights Reserved