还有
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)
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)
解决方案 »
- MySQL性能问题,使用固定内存,占用过多CPU
- PostgreSQL 如何字符串解析成表?
- 有什么数据库能做到每秒2万个INSERT?
- 我想卸载redhat linux 9自带的postgresql,该怎么做?求教。
- 有没有什么好方法将mysql 转为access?
- 用c API 编程,对一个数据库操作时,用在mysql_real_query()中使用“USE”吗?-- 阿菜
- 新手请教子查询和join查询问题
- 我的MYSQL怎么连不上呢?
- 这个MSSQL的语句如何转换为MySql
- mysql使用lvs+keepalived实现读负载均衡,通过VIP访问时报错
- 大家好,我现在碰到一个小难题 ,mySql command line Client 怎么用?
- 请问mysql自动 down机的可能原因是什么?
你可以用以下命令查看一下:
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)
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)
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
我现在的设置是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)
依旧没法中文超作
如果是的话,你可不可以把你的jsp代码贴出来?
我从表单得到的中文数据一般要经过转换才用来查询的。
例如:
String name=new String(reqeust.getParameter("name").getBytes("ISO-8859-1"),"gb2312");
mysql>SELECT password,status,root FROM usr WHERE usr_name='匿名用户';
我刚刚找到了原因了,
不过我不知道为什么?
我的数据库连接本来是:
<!-- 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的,为什么插入还能正常呢?
<%@ 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 -->
<%@ page contentType="text/html;charset=utf8" %>
来指定jsp页面的字符集编码,中文的默认是gb2312,不信你可以随便打开一个网页,右击查看->编码从数据库读出数据一般都要转换成unicode先,再按指定的编码方式读出(gb2312,utf8),所以才会有:
useUnicode=true&characterEncoding=utf8如果上面将页面设为gb2312,而下面的数据读入却是utf8当然会出现乱码啦。character_set_system 一定是utf8,这个是系统的不关mysql的事,至于为什么我也不知道,我也是
看来的。你给的英文我没怎么看,英语差!以上就是我的理解,希望对你有所帮助,有什么说错的地方也请见谅。