完美解决MySQL中文乱码
方法一：http://www.jz123.cn/text/1638253.html
解决MySQL中文乱码的问题
看到从数据库中取出的数据全部是“？？？？？”，太让人郁闷了。
网上有很多方法来解决这个问题，可以分为二种，1是修改my.ini配置文件，如果是在网上的空间的话，我们一般没有权限去修改这个文件。
我推荐使用使用第二种方法，在数据库连接过程中，设定字符编码。
如下所示：（加粗部分）
@ $db_link=mysql_connect('localhost','user','pass');
MySQL_query("SET NAMES 'utf8'");
mysql_select_db('databasename',$db_link);
?
?
方法二：转自http://blog.chinaunix.net/space.php?uid=9195812&do=blog&id=2006339
?
?为了说的明白一些，我觉个例子：
很简单，就是从html中接受两个输入，然后由jsp处理写到mysql且从数据库返回这个结果显示出来。
Mysql_jstl.html
<html>
<head>
??<title>CH14?-?Mysql_jstl.html</title>
<meta?http-equiv="Content-Type"?content="text/html; charset= GB2312">
</head>
<body>
?
<h2>将信息存入 Mysql 中?-?使用 JSTL 写法</h2>
<form?name="form"?action="Mysql_jstl.jsp"?method="post"?>
??<p>姓：<input?name="last_name"?type="text"?id="last_name"></p>
??<p>名：<input?name="first_name"?type="text"?id="first_name"></p>
??<p>?
????<input?type="submit"?value="传送">
????<input?type="reset"?value="取消">
??</p>
</form>
</body>
</html>
Mysql_jstl.jsp<%@?page contentType="text/html;charset=GB2312"?%>
<%@?taglib prefix="c"?uri="http://java.sun.com/jsp/jstl/core"?%>
<%@?taglib prefix="sql"?uri="http://java.sun.com/jsp/jstl/sql"?%>
<%@?taglib prefix="fmt"?uri="http://java.sun.com/jsp/jstl/fmt"?%>
<html>
<head>
??<title>CH14?-?Mysql_jstl.jsp</title>
</head>
<body>
<h2>将信息存入 Mysql 中?-?使用 JSTL 写法</h2>
<fmt:requestEncoding?value="GB2312"?/>
<c:set?var="birth"?value="1978/12/11"?/>
<c:set?var="sex"?value="F"?/>
<c:set?var="email"?value="aaa@asdf.com"?/>
<sql:setDataSource driver="com.mysql.jdbc.Driver"
???????? url="jdbc:mysql://localhost:3306/sample?useUnicode=true&characterEncoding=UTF-8"
???????? user="root"
???????? password="44444"?/>
<sql:update>
??INSERT INTO employee(employee_id,?last_name,?first_name,?birth,sex,?emmail)?
??VALUES?(???,???,???,???,???,???)
??
??<sql:param?value="${employee_id}"?/>
??<sql:param?value="${param.last_name}"?/>
??<sql:param?value="${param.first_name}"?/>
??<sql:param?value="${birth}"?/>
??<sql:param?value="${sex}"?/>
??<sql:param?value="${email}"?/>?
</sql:update>
<sql:query?var="result">
??SELECT?*?FROM employee
</sql:query>
从 employee 取出所有新增的姓名：<br>
<c:forEach items="${result.rows}"?var="row"?>
新增姓名：<c:out?value="${row.last_name}"?/>
?????<c:out?value="${row.first_name}"?/><br>
</c:forEach>
</body>
</html>
接下来就是创建数据库，名字为sample
然后建立一个table: employee,内容如下（其中应该是email,可是我不小心在建数据库打错了，将错就错了）：
+-------------+------------------+------+-----+---------+----------------+
| Field?????? | Type???????????? | Null | Key | Default | Extra????????? |
+-------------+------------------+------+-----+---------+----------------+
| employee_id | int(10) unsigned | NO?? | PRI | NULL??? | auto_increment |
| last_name?? | varchar(20)????? | YES? |???? | NULL??? |??????????????? |
| first_name? | varchar(20)????? | YES? |???? | NULL??? |??????????????? |
| birth?????? | date???????????? | YES? |???? | NULL??? |??????????????? |
| sex???????? | enum('m','f')??? | YES? |???? | m?????? |??????????????? |
| emmail????? | varchar(39)????? | YES? |???? | NULL??? |??????????????? |
+-------------+------------------+------+-----+---------+----------------+
如果只是这样的话，就会出现这样的错误：报告的错误是：sqle=com.mysql.jdbc.MysqlDataTruncation:?
Data?truncation:?Data?too?long?
for?column?'last_name'?at?row?1?.而且select last_name from employee.来看也出现了如下的情况：| last_name????????|
+-------------+
|?????????????|
|?54243654321?|
|?????????????|
|?????????????|
+-------------+出现问号！
前面我们已经说过了，mysql默认的编码是latin1,不是我们所需要的gbk,所以我们要修改成为utf8，因为若要正确显示中文繁、简、日文、韩文 使用utf8，修改方法如下：
??ALTER DATABASE sample?####这里修改整个数据库的编码
?????? CHARACTER SET utf8
?????? DEFAULT CHARACTER SET utf8
?????? COLLATE utf8_general_ci
?????? DEFAULT COLLATE utf8_general_ci;当然了，你也可在在建数据库的时候指定编码，比如：
CREATE DATABASE sample
???????CHARACTER SET utf8
?????? DEFAULT CHARACTER SET utf8
?????? COLLATE utf8_general_ci
?????? DEFAULT COLLATE utf8_general_ci ;
接下来要做的是打开mysql所在的目录下的my.nin在[mysqld]段加入一下代码改成：
default-character-set=utf8 启动mysql，输入：执行下列语句，看看结果是不是下面的：mysql> show variables like '%character%';+--------------------------+-------------------------------------------+| Variable_name | Value |+--------------------------+-------------------------------------------+| character_set_client | latin1 || character_set_connection | latin1 || character_set_database | utf8 || character_set_results | latin1 || character_set_server | utf8 | character_set_system | utf8 || character_sets_dir | C:\MySQL\MySQL Server 5.0\share\charsets\ |+--------------------------+-------------------------------------------+mysql> show variables like '%collation%';+----------------------+-------------------+| Variable_name | Value |+----------------------+-------------------+| collation_connection | latin1_swedish_ci || collation_database | utf8_general_ci || collation_server | utf8_general_ci |+----------------------+-------------------+
接着你再看看执行那个Mysql.html 文件：这回你可以看到的是
mysql> select * from employee;
+-------------+-----------+------------+------------+------+--------------+
| employee_id | last_name | first_name | birth????? | sex? | emmail?????? |
+-------------+-----------+------------+------------+------+--------------+
|????????? 12 | ????????? | ?????????? | 1978-12-11 | f??? | aaa@asdf.com |
|????????? 13 | ????????? | ?????????? | 1978-12-11 | f??? | aaa@asdf.com |
|????????? 14 | ????????? | ?????????? | 1978-12-11 | f??? | aaa@asdf.com |
|????????? 15 | ????????? | ?????????? | 1978-12-11 | f??? | aaa@asdf.com |
|????????? 16 | ????????? | ?????????? | 1978-12-11 | f??? | aaa@asdf.com |
|????????? 17 | ????????? | ?????????? | 1978-12-11 | f??? | aaa@asdf.com |
+-------------+-----------+------------+------------+------+--------------+
这当然不是我们希望看到的，我们需要的现实完美正确的中文：
我们还有最后一招：
mysql>?SET NAMES 'gbk' ;
Query OK, 0 rows affected (0.00 sec)
因为我们需要的是gbk.
看看mysql中的character设置情况：
mysql> SHOW VARIABLES LIKE '%character%' ;
+--------------------------+-------------------------------------------+
| Variable_name??????????? | Value???????????????????????????????????? |
+--------------------------+-------------------------------------------+
| character_set_client???? | gbk?????????????????????????????????????? |
| character_set_connection | gbk?????????????????????????????????????? |
| character_set_database?? | utf8????????????????????????????????????? |
| character_set_results??? | gbk?????????????????????????????????????? |
| character_set_server???? | utf8????????????????????????????????????? |
| character_set_system???? | utf8????????????????????????????????????? |
| character_sets_dir?????? | C:\MySQL\MySQL Server 5.0\share\charsets\ |
+--------------------------+-------------------------------------------+
7 rows in set (0.00 sec)
mysql> SHOW VARIABLES LIKE '%collation%' ;
+----------------------+-----------------+
| Variable_name??????? | Value?????????? |
+----------------------+-----------------+
| collation_connection | gbk_chinese_ci? |
| collation_database?? | utf8_general_ci |
| collation_server???? | utf8_general_ci |
+----------------------+-----------------+
这个才是我们最终需要的。
在来查看执行完Mysql_jstl.jsp后的数据库中的结果：
mysql> select * from employee;
|????????? 14 | 王??????? | 彭给?????? | 1978-12-11 | f??? | aaa@asdf.com |
|????????? 15 | 田??????? | 王光?????? | 1978-12-11 | f??? | aaa@asdf.com |
|????????? 16 | 息??????? | 存入?????? | 1978-12-11 | f??? | aaa@asdf.com |
|????????? 17 | 往??????? | 小杯?????? | 1978-12-11 | f??? | aaa@asdf.com |
+-------------+-----------+------------+------------+------+--------------+
可以高兴得看到了中文，并且在浏览器中也显示正确。
但是仅仅这样的话，当你重新启动mysql的时候
所有的设置又失效了。
mysql> show variables like '%character%';
+--------------------------+-------------------------------------------+
| Variable_name??????????? | Value???????????????????????????????????? |
+--------------------------+-------------------------------------------+
| character_set_client???? | latin1??????????????????????????????????? |
| character_set_connection | latin1??????????????????????????????????? |
| character_set_database?? | utf8????????????????????????????????????? |
| character_set_results??? | latin1??????????????????????????????????? |
| character_set_server???? | utf8????????????????????????????????????? |
| character_set_system???? | utf8????????????????????????????????????? |
| character_sets_dir?????? | C:\MySQL\MySQL Server 5.0\share\charsets\ |
+--------------------------+-------------------------------------------+
mysql> SHOW VARIABLES LIKE '%collation%' ;
+----------------------+-------------------+
| Variable_name??????? | Value???????????? |
+----------------------+-------------------+
| collation_connection | latin1_swedish_ci |
| collation_database?? | utf8_general_ci?? |
| collation_server???? | utf8_general_ci?? |
+----------------------+-------------------+
重新读取又出现乱码：
mysql> select * from employee;
+-------------+-----------+------------+------------+--
| employee_id | last_name | first_name | birth????? | s
+-------------+-----------+------------+------------+--
|????????? 12 | ????????? | ?????????? | 1978-12-11 | f
|????????? 13 | ????????? | ?????????? | 1978-12-11 | f
|????????? 14 | ????????? | ?????????? | 1978-12-11 | f
|????????? 15 | ????????? | ?????????? | 1978-12-11 | f
|????????? 16 | ????????? | ?????????? | 1978-12-11 | f
|????????? 17 | ????????? | ?????????? | 1978-12-11 | f
+-------------+-----------+------------+------------+--
所以我们需要在客户端设置系统能识别中文的编码gbk并没有保存到my.ini文件中。所以要修改my.ini文件在[mysql]段加入一下代码改成：default-character-set=gbk 这样设置就得到保存了。重启就可以了。mysql> show variables like '%character%';+--------------------------+----------------| Variable_name | Value+--------------------------+----------------| character_set_client | gbk| character_set_connection | gbk| character_set_database | utf8| character_set_results | gbk| character_set_server | utf8| character_set_system | utf8| character_sets_dir | C:\MySQL\MySQL+--------------------------+----------------mysql> SHOW VARIABLES LIKE '%collation%' ;+----------------------+-----------------+| Variable_name | Value |+----------------------+-----------------+| collation_connection | gbk_chinese_ci || collation_database | utf8_general_ci || collation_server | utf8_general_ci |+----------------------+-----------------+?
）：