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)
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 --------------
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了,這是方案一,依然可以實現這樣的更改。
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 --------------
--修改數據庫編碼為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,所以異常的原因就是中文的編碼和表的默認編碼不匹配,造成數據沖突引起的。
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數據庫編碼已經解決了。
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;