mysql5.0中文字符问题
为了用存储过程和触发器，我把我的mysql升级到了5.0的版本。
用mysql -u root -p -D db_name <c:\db_name.txt
将数据导入，
在命令窗口显示正常，
但当我再次用mysqldump -u root -p db_name >c:\db_name.txt
时，文本文件中的中文字符全是乱码，
在网上找了些资料，吧my.ini中的
两个
default-character-set=latin1
改为
default-character-set=gb2312
后，重新导入数据，
再导出，文本文件中文显示正常了。
但程序中的中文字符始终无法正常显示，
只是前者是乱码，后者全是问号。
后来我去mysql网上下了一个最新的jdbc驱动
重启服务器，程序中的中午字符显示正常，
但无法中文检索，
报错如下
exception
javax.servlet.ServletException: javax.servlet.jsp.JspException:
SELECT password,status,root FROM usr WHERE usr_name='匿名用户'
: Illegal mix of collations (gb2312_chinese_ci,IMPLICIT) and (utf8_general_ci,COERCIBLE) for operation '='
org.apache.jasper.runtime.PageContextImpl.doHandlePageException(PageContextImpl.java:844)
org.apache.jasper.runtime.PageContextImpl.handlePageException(PageContextImpl.java:781)
org.apache.jsp.validate.logcheck_jsp._jspService(org.apache.jsp.validate.logcheck_jsp:220)
org.apache.jasper.runtime.HttpJspBase.service(HttpJspBase.java:97)
javax.servlet.http.HttpServlet.service(HttpServlet.java:802)
org.apache.jasper.servlet.JspServletWrapper.service(JspServletWrapper.java:322)
org.apache.jasper.servlet.JspServlet.serviceJspFile(JspServlet.java:291)
org.apache.jasper.servlet.JspServlet.service(JspServlet.java:241)
javax.servlet.http.HttpServlet.service(HttpServlet.java:802)
root cause
java.sql.SQLException: Illegal mix of collations (gb2312_chinese_ci,IMPLICIT) and (utf8_general_ci,COERCIBLE) for operation '='
com.mysql.jdbc.MysqlIO.checkErrorPacket(MysqlIO.java:2901)
com.mysql.jdbc.MysqlIO.sendCommand(MysqlIO.java:1584)
com.mysql.jdbc.Connection.serverPrepare(Connection.java:4932)
com.mysql.jdbc.Connection.prepareStatement(Connection.java:1312)
com.mysql.jdbc.Connection.prepareStatement(Connection.java:1284)
org.apache.taglibs.standard.tag.common.sql.QueryTagSupport.doEndTag(QueryTagSupport.java:246)
org.apache.jsp.validate.logcheck_jsp._jspx_meth_sql_query_0(org.apache.jsp.validate.logcheck_jsp:312)
org.apache.jsp.validate.logcheck_jsp._jspService(org.apache.jsp.validate.logcheck_jsp:112)
org.apache.jasper.runtime.HttpJspBase.service(HttpJspBase.java:97)
javax.servlet.http.HttpServlet.service(HttpServlet.java:802)
org.apache.jasper.servlet.JspServletWrapper.service(JspServletWrapper.java:322)
org.apache.jasper.servlet.JspServlet.serviceJspFile(JspServlet.java:291)
org.apache.jasper.servlet.JspServlet.service(JspServlet.java:241)
javax.servlet.http.HttpServlet.service(HttpServlet.java:802)
－－－－－－－－－－－－－－－－－－－－－－－－－－－－－－
mysql现在是mysql-5.0.15-win32
操作系统是winxpsp2
web服务器是tomcat5.5.9
问题点数：100、回复次数：15Top
1 楼duwan（刻骨铭心）回复于 2005-11-02 14:56:43 得分 0
还有
mysql> show variables like 'ch%';
+--------------------------+-------------------------------------------+
| Variable_name | Value |
+--------------------------+-------------------------------------------+
| character_set_client | gb2312 |
| character_set_connection | gb2312 |
| character_set_database | gb2312 |
| character_set_results | gb2312 |
| character_set_server | gb2312 |
| character_set_system | utf8 |
| character_sets_dir | I:\MySQL\MySQL Server 5.0\share\charsets/ |
+--------------------------+-------------------------------------------+
7 rows in set (0.00 sec)Top
2 楼hy2003fly（）回复于 2005-11-02 14:58:13 得分 0
不是说得很清楚了吗？你的字符集关联不一致，一个是gb2312，一个是utf8.
你可以用以下命令查看一下：
mysql> show variables like "%coll%";
+----------------------+-----------------+
| Variable_name | Value |
+----------------------+-----------------+
| collation_connection | utf8_general_ci |
| collation_database | utf8_general_ci |
| collation_server | utf8_general_ci |
+----------------------+-----------------+
3 rows in set (0.06 sec)
也可以用命令检查一下字符集是否一致：
mysql> show variables like "%char%";
+--------------------------+----------------------------------------------------
-----+
| Variable_name | Value
|
+--------------------------+----------------------------------------------------
-----+
| character_set_client | utf8
|
| character_set_connection | utf8
|
| character_set_database | utf8
|
| character_set_results | utf8
|
| character_set_server | utf8
|
| character_set_system | utf8
|
| character_sets_dir | C:\Program Files\MySQL\MySQL Server 4.1\share\chars
ets/ |
+--------------------------+----------------------------------------------------
-----+
7 rows in set (0.01 sec)
Top
3 楼hy2003fly（）回复于 2005-11-02 15:03:02 得分 100
你用show variables like 'ch%';得出的结果表明你的字符集设置没有问题，现在只需要把字符集的关联也改成一致，应该就没有问题了。Top
4 楼duwan（刻骨铭心）回复于 2005-11-02 15:06:33 得分 0
我最近才开始用mysql，告诉我该怎么操作啊，show了以后怎么办啊？Top
5 楼duwan（刻骨铭心）回复于 2005-11-02 15:13:39 得分 0
我刚刚把
default-character-set=bg2312
改回
default-character-set=latin1
中文显示依旧正常，
看来最早的乱码可能和jdbc驱动有关，
不过插入和查询依旧不行
mysql> show variables like "%coll%";
+----------------------+-------------------+
| Variable_name | Value |
+----------------------+-------------------+
| collation_connection | latin1_swedish_ci |
| collation_database | latin1_swedish_ci |
| collation_server | latin1_swedish_ci |
+----------------------+-------------------+
3 rows in set (0.00 sec)
mysql> show variables like "%char%";
+--------------------------+-------------------------------------------+
| Variable_name | Value |
+--------------------------+-------------------------------------------+
| character_set_client | latin1 |
| character_set_connection | latin1 |
| character_set_database | latin1 |
| character_set_results | latin1 |
| character_set_server | latin1 |
| character_set_system | utf8 |
| character_sets_dir | I:\MySQL\MySQL Server 5.0\share\charsets/ |
+--------------------------+-------------------------------------------+
7 rows in set (0.00 sec)Top
6 楼hy2003fly（）回复于 2005-11-02 15:26:16 得分 0
你用如下命令后会出现类似的结果：
mysql> show variables like "%coll%";
+----------------------+-----------------+
| Variable_name | Value |
+----------------------+-----------------+
| collation_connection | utf8_general_ci |
| collation_database | utf8_general_ci |
| collation_server | utf8_general_ci |
+----------------------+-----------------+
然后你把那个是utf8的作如下操作，看看是不是你想要的结果先。
例如：
mysql> set collation_connection = 'gb2312_chinese_ci';
Query OK, 0 rows affected (0.12 sec)
不过set命令是暂时的，关闭了mysql就不再有效。
如果set后得出的结果是你想要的话，你可以用mysql query browser，在里面打开该表所的数据库->该表,然后右击->edit table->table option-> charset->collation
Top
7 楼hy2003fly（）回复于 2005-11-02 15:28:17 得分 0
字符集latin1不支持中文的。Top
8 楼duwan（刻骨铭心）回复于 2005-11-02 15:41:28 得分 0
还是不行啊，
我现在的设置是
mysql> show variables like "%coll%";
+----------------------+-------------------+
| Variable_name | Value |
+----------------------+-------------------+
| collation_connection | gb2312_chinese_ci |
| collation_database | gb2312_chinese_ci |
| collation_server | gb2312_chinese_ci |
+----------------------+-------------------+
3 rows in set (0.00 sec)
mysql> show variables like "%char%";
+--------------------------+-------------------------------------------+
| Variable_name | Value |
+--------------------------+-------------------------------------------+
| character_set_client | gb2312 |
| character_set_connection | gb2312 |
| character_set_database | gb2312 |
| character_set_results | gb2312 |
| character_set_server | gb2312 |
| character_set_system | utf8 |
| character_sets_dir | I:\MySQL\MySQL Server 5.0\share\charsets/ |
+--------------------------+-------------------------------------------+
7 rows in set (0.00 sec)
依旧没法中文超作Top
9 楼hy2003fly（）回复于 2005-11-02 15:56:24 得分 0
你是写jsp的吧？我现在才注意到，你写jsp对数据库的操作！
如果是的话，你可不可以把你的jsp代码贴出来？
我从表单得到的中文数据一般要经过转换才用来查询的。
例如：
String name=new String(reqeust.getParameter("name").getBytes("ISO-8859-1"),"gb2312");
Top
10 楼hy2003fly（）回复于 2005-11-02 16:04:37 得分 0
由你上面来看，应该就不关mysql的事了，你可以在mysql下测试一下。
mysql>SELECT password,status,root FROM usr WHERE usr_name='匿名用户';
Top
11 楼duwan（刻骨铭心）回复于 2005-11-02 16:08:34 得分 0
谢谢你的帮助，
我刚刚找到了原因了，
不过我不知道为什么?
我的数据库连接本来是：
<!-- connect database begin -->
<sql:setDataSource driver="com.mysql.jdbc.Driver"
url="jdbc:mysql://localhost:3306/sh_ly_infosys?useUnicode=true&characterEncoding=GB18030"
user="root" password="browser"/>
<!-- connect database end -->
我把他改成了
<!-- connect database begin -->
<sql:setDataSource driver="com.mysql.jdbc.Driver"
url="jdbc:mysql://localhost:3306/sh_ly_infosys?useUnicode=true&characterEncoding=utf8"
user="root" password="browser"/>
<!-- connect database end -->
就好了，
插入的也正常了。
没有乱码。
用console select 出来也正常，
上面的连接字符串，我是在书上看来的，
characterEncoding=GB18030就是为了解决中文乱码，
当时针对的版本是4.0版的mysql，
刚才我发现
character_set_system
始终是utf8
用set 提示read only
于是去查文档，发现
10.6. UTF8 for Metadata
Metadata 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.
Representation of metadata must satisfy these requirements:
All 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.
Metadata must include all characters in all languages. Otherwise, users wouldn't be able to name columns and tables in their own languages.
In 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.
This 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().
看的不太明白，
好像元数据必须用utf8，
所以我就去改了一下连接字符串，
现在正常了，
你能给我一个解释吗？
为什么我的jsp中的编码是gb2312或gb18030的，
数据库中是utf8的，为什么插入还能正常呢？Top
12 楼duwan（刻骨铭心）回复于 2005-11-02 16:10:48 得分 0
下面是其中一个页面的代码
<%@ 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" %>
<fmt:requestEncoding value="GB18030"/>
<!-- connect database begin -->
<sql:setDataSource driver="com.mysql.jdbc.Driver"
url="jdbc:mysql://localhost:3306/sh_ly_infosys?useUnicode=true&characterEncoding=utf8"
user="root" password="browser"/>
<!-- connect database end -->
<!-- select begin -->
<sql:query var="find_node_index">
select max(node_index) as max_node_index from tree where father_node_id=${param.father_node_id }
</sql:query>
<sql:query var="pathway">
select pathway from tree where node_id=${param.father_node_id }
</sql:query>
<!-- select end -->
<!-- insert begin -->
<sql:update>
insert 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}'))
</sql:update>
<!-- insert end -->
<!-- redirect begin-->
<c:redirect url="add_node.jsp">
<c:param name="message">分类添加成功!</c:param>
<c:param name="father_node_id">${param.father_node_id}</c:param>
</c:redirect>
<!-- redirect end -->
Top
13 楼hy2003fly（）回复于 2005-11-02 16:31:00 得分 0
一般写jsp都会这样的一句：
<%@ page contentType="text/html;charset=utf8" %>
来指定jsp页面的字符集编码,中文的默认是gb2312,不信你可以随便打开一个网页，右击查看->编码
从数据库读出数据一般都要转换成unicode先，再按指定的编码方式读出（gb2312,utf8），所以才会有：
useUnicode=true&characterEncoding=utf8
如果上面将页面设为gb2312,而下面的数据读入却是utf8当然会出现乱码啦。
character_set_system 一定是utf8，这个是系统的不关mysql的事，至于为什么我也不知道，我也是
看来的。
你给的英文我没怎么看，英语差！以上就是我的理解，希望对你有所帮助，有什么说错的地方也请见谅。
Top
14 楼duwan（刻骨铭心）回复于 2005-11-02 16:43:55 得分 0
还是不明白，不过还是谢谢你。Top
15 楼sjt3749（sjt3749）回复于 2005-11-27 17:39:39 得分 0
路过!Top
