Jdbc 连接 mysql5.0 中文字符乱码问题

为了用存储过程和触发器，我把我的mysql升级到了5.0的版本。
用mysql -u root -p -D db_name

  1<c:\db_name.txt -p="" -u="" db_name="" root="" 但当我再次用mysqldump="" 在命令窗口显示正常，="" 将数据导入，="">c:\db_name.txt   
  2时，文本文件中的中文字符全是乱码，   
  3  
  4在网上找了些资料，吧my.ini中的   
  5两个   
  6default-character-set=latin1   
  7改为   
  8default-character-set=gb2312   
  9后，重新导入数据，   
 10再导出，文本文件中文显示正常了。   
 11  
 12但程序中的中文字符始终无法正常显示，   
 13只是前者是乱码，后者全是问号。   
 14  
 15后来我去mysql网上下了一个最新的jdbc驱动   
 16重启服务器，程序中的中午字符显示正常，   
 17但无法中文检索，   
 18报错如下   
 19  
 20exception   
 21  
 22javax.servlet.ServletException: javax.servlet.jsp.JspException:   
 23SELECT password,status,root FROM usr WHERE usr_name='匿名用户'   
 24: Illegal mix of collations (gb2312_chinese_ci,IMPLICIT) and (utf8_general_ci,COERCIBLE) for operation '='   
 25org.apache.jasper.runtime.PageContextImpl.doHandlePageException(PageContextImpl.java:844)   
 26org.apache.jasper.runtime.PageContextImpl.handlePageException(PageContextImpl.java:781)   
 27org.apache.jsp.validate.logcheck_jsp._jspService(org.apache.jsp.validate.logcheck_jsp:220)   
 28org.apache.jasper.runtime.HttpJspBase.service(HttpJspBase.java:97)   
 29javax.servlet.http.HttpServlet.service(HttpServlet.java:802)   
 30org.apache.jasper.servlet.JspServletWrapper.service(JspServletWrapper.java:322)   
 31org.apache.jasper.servlet.JspServlet.serviceJspFile(JspServlet.java:291)   
 32org.apache.jasper.servlet.JspServlet.service(JspServlet.java:241)   
 33javax.servlet.http.HttpServlet.service(HttpServlet.java:802)   
 34  
 35  
 36root cause   
 37  
 38java.sql.SQLException: Illegal mix of collations (gb2312_chinese_ci,IMPLICIT) and (utf8_general_ci,COERCIBLE) for operation '='   
 39com.mysql.jdbc.MysqlIO.checkErrorPacket(MysqlIO.java:2901)   
 40com.mysql.jdbc.MysqlIO.sendCommand(MysqlIO.java:1584)   
 41com.mysql.jdbc.Connection.serverPrepare(Connection.java:4932)   
 42com.mysql.jdbc.Connection.prepareStatement(Connection.java:1312)   
 43com.mysql.jdbc.Connection.prepareStatement(Connection.java:1284)   
 44org.apache.taglibs.standard.tag.common.sql.QueryTagSupport.doEndTag(QueryTagSupport.java:246)   
 45org.apache.jsp.validate.logcheck_jsp._jspx_meth_sql_query_0(org.apache.jsp.validate.logcheck_jsp:312)   
 46org.apache.jsp.validate.logcheck_jsp._jspService(org.apache.jsp.validate.logcheck_jsp:112)   
 47org.apache.jasper.runtime.HttpJspBase.service(HttpJspBase.java:97)   
 48javax.servlet.http.HttpServlet.service(HttpServlet.java:802)   
 49org.apache.jasper.servlet.JspServletWrapper.service(JspServletWrapper.java:322)   
 50org.apache.jasper.servlet.JspServlet.serviceJspFile(JspServlet.java:291)   
 51org.apache.jasper.servlet.JspServlet.service(JspServlet.java:241)   
 52javax.servlet.http.HttpServlet.service(HttpServlet.java:802)   
 53  
 54－－－－－－－－－－－－－－－－－－－－－－－－－－－－－－   
 55mysql现在是mysql-5.0.15-win32   
 56操作系统是winxpsp2   
 57web服务器是tomcat5.5.9   
 58\---------------------------------------------------------------   
 59我的数据库连接本来是：   
 60<!-- connect database begin -->
 61<sql:setdatasource driver="com.mysql.jdbc.Driver" password="browser" url="jdbc:mysql://localhost:3306/sh_ly_infosys?useUnicode=true&amp;characterEncoding=GB18030" user="root"></sql:setdatasource>
 62<!-- connect database end -->   
 63我把他改成了   
 64<!-- connect database begin -->
 65<sql:setdatasource driver="com.mysql.jdbc.Driver" password="browser" url="jdbc:mysql://localhost:3306/sh_ly_infosys?useUnicode=true&amp;characterEncoding=utf8" user="root"></sql:setdatasource>
 66<!-- connect database end -->   
 67就好了，   
 68插入的也正常了。   
 69没有乱码。   
 70用console select 出来也正常，   
 71上面的连接字符串，我是在书上看来的，   
 72characterEncoding=GB18030就是为了解决中文乱码，   
 73当时针对的版本是4.0版的mysql，   
 74刚才我发现   
 75character_set_system   
 76始终是utf8   
 77用set 提示read only   
 78于是去查文档，发现   
 7910.6. UTF8 for Metadata   
 80Metadata is the data about the data. Anything that describes the database, as opposed to being the contents of the database, is metadata. Thus column names, database names, usernames, version names, and most of the string results from SHOW are metadata.   
 81  
 82Representation of metadata must satisfy these requirements:   
 83  
 84All metadata must be in the same character set. Otherwise, SHOW wouldn't work properly because different rows in the same column would be in different character sets.   
 85  
 86Metadata must include all characters in all languages. Otherwise, users wouldn't be able to name columns and tables in their own languages.   
 87  
 88In order to satisfy both requirements, MySQL stores metadata in a Unicode character set, namely UTF8. This does not cause any disruption if you never use accented characters. But if you do, you should be aware that metadata is in UTF8.   
 89  
 90This means that the USER(), CURRENT_USER(), DATABASE(), and VERSION() functions have the UTF8 character set by default, as do synonyms such as SESSION_USER() and SYSTEM_USER().   
 91  
 92看的不太明白，   
 93好像元数据必须用utf8，   
 94所以我就去改了一下连接字符串，   
 95现在正常了，   
 96你能给我一个解释吗？   
 97为什么我的jsp中的编码是gb2312或gb18030的，   
 98数据库中是utf8的，为什么插入还能正常呢？   
 99  
100  
101下面是其中一个页面的代码

@ taglib prefix="sql" uri="http://java.sun.com/jsp/jstl/sql"

@ taglib prefix="c" uri="http://java.sun.com/jsp/jstl/core"

@ taglib prefix="fmt" uri="http://java.sun.com/jsp/jstl/fmt"

@ taglib prefix="my" uri="done.ChangeCode"

 1  
 2<fmt:requestencoding value="GB18030"></fmt:requestencoding>
 3<!-- connect database begin -->
 4<sql:setdatasource driver="com.mysql.jdbc.Driver" password="browser" url="jdbc:mysql://localhost:3306/sh_ly_infosys?useUnicode=true&amp;characterEncoding=utf8" user="root"></sql:setdatasource>
 5<!-- connect database end -->
 6<!-- select begin -->
 7<sql:query var="find_node_index">   
 8select max(node_index) as max_node_index from tree where father_node_id=${param.father_node_id }   
 9</sql:query>
10<sql:query var="pathway">   
11select pathway from tree where node_id=${param.father_node_id }   
12</sql:query>
13<!-- select end -->
14<!-- insert begin -->
15<sql:update>   
16insert into tree (father_node_id,value,node_index,pathway) values(${param.father_node_id},'${param.node_value}',${find_node_index.rows[0].max_node_index+1},concat('${pathway.rows[0].pathway}','/','${param.node_value}'))   
17</sql:update>
18<!-- insert end -->
19<!-- redirect begin-->
20<c:redirect url="add_node.jsp">
21<c:param name="message">分类添加成功!</c:param>
22<c:param name="father_node_id">${param.father_node_id}</c:param>
23</c:redirect>
24<!-- redirect end -->   
25  
26一般写jsp都会这样的一句：

@ page contentType="text/html;charset=utf8"

 1来指定jsp页面的字符集编码,中文的默认是gb2312,不信你可以随便打开一个网页，右击查看-&gt;编码   
 2  
 3从数据库读出数据一般都要转换成unicode先，再按指定的编码方式读出（gb2312,utf8），所以才会有：   
 4useUnicode=true&amp;characterEncoding=utf8   
 5  
 6如果上面将页面设为gb2312,而下面的数据读入却是utf8当然会出现乱码啦。   
 7  
 8character_set_system 一定是utf8，这个是系统的不关mysql的事，至于为什么我也不知道，我也是   
 9看来的。   
10  
11你给的英文我没怎么看，英语差！以上就是我的理解，希望对你有所帮助，有什么说错的地方也请见谅。</c:\db_name.txt>
Published At
Categories with 数据库类
comments powered by Disqus