关于linux中mysql中编码的问题(UTF-8)
今天用了一天时间来研究mysql中的编码问题,辛苦总算没有白费。
不管什么时候,编码一定要统一,这是保证数据能正确入库的前提。
首先要配置你的mysql的默认编码方式为UTF-8.
具体看my.cnf的配置.
(#cp /usr/local/mysql/support-files/my-medium.cnf /etc/my.cnf)
#下面是很重要的=====================================
[client]
#password = your_password
port = 3306
socket = /tmp/mysql.sock
default-character-set = utf8
[mysqld]
port = 3306
socket = /tmp/mysql.sock
skip-locking
key_buffer = 16M
max_allowed_packet = 1M
table_cache = 64
sort_buffer_size = 512K
net_buffer_length = 8K
read_buffer_size = 256K
read_rnd_buffer_size = 512K
myisam_sort_buffer_size = 8M
default-character-set = utf8
#===============================
当你配置好了以后,进入mysql,执行#status,可以看到以下结果。
--------------
mysql Ver 14.12 Distrib 5.0.41, for pc-linux-gnu (i686) using readline 5.0
Connection id: 1
Current database: jsp
Current user: root@localhost
SSL: Not in use
Current pager: stdout
Using outfile: ''
Using delimiter: ;
Server version: 5.0.41-log MySQL Community Server (GPL)
Protocol version: 10
Connection: Localhost via UNIX socket
Server characterset: utf8
Db characterset: utf8
Client characterset: utf8
Conn. characterset: utf8
UNIX socket: /tmp/mysql.sock
Uptime: 1 hour 12 min 21 sec
Threads: 2 Questions: 390 Slow queries: 0 Opens: 18 Flush tables: 1 Open tables: 8 Queries per second avg: 0.090
--------------
这就说明mysql编码方式为UTF-8.
下面有一个问题就是tomcat编码问题,每个页面我都设成 contentType="text/html; charset=utf-8" 但最后用request.getParameter()得到还是乱码,这只能说明在请求时编码方式不正确。
在要处理参数的页面加入request.setCharacterEncoding("utf-8");
。这时就OK了。。。。
下面为测试文件.
//index.jsp
<%@ page contentType="text/html; charset=utf-8" language="java" import="java.sql.*" errorPage="" %>
<!DOCTYPE HTML PUBLIC "-//W3C//DTD HTML 4.01 Transitional//EN" "http://www.w3.org/TR/html4/loose.dtd">
<%@ include file="conn.jsp"%>
<html>
<head>
<title></title>
</head>
<body>
<div align="center" class="style1">
<p>添加学生信息</p>
<form name="form1" method="post" action="add.jsp">
<p><span>姓名:</span>
<input type="text" name="name">
</p>
<p>
<input type="submit" name="queding" value="提交">
<input type="reset" name="chongtian" value="重置">
</p>
<p> </p>
</form>
<p> </p>
<%
ResultSet rs=stmt.executeQuery("select * from jsp");
rs.beforeFirst();
while(rs.next())
{
%>
<p><%=rs.getString("name")%></p>
<%
}
rs.close();
stmt.close();
conn.close();
%>
</div>
</body>
</html>
//add.jsp
<%@ page contentType="text/html; charset=utf-8" language="java" import="java.sql.*" errorPage="" %>
<!DOCTYPE HTML PUBLIC "-//W3C//DTD HTML 4.01 Transitional//EN" "http://www.w3.org/TR/html4/loose.dtd">
<%@ include file="conn.jsp"%>
<html>
<head>
<title></title>
</head>
<body>
<%
request.setCharacterEncoding("utf-8");//可以先取掉试试
String name=request.getParameter("name");
String sql="insert into jsp (name) values ('"+name+"')";
stmt.executeUpdate(sql);
stmt.close();
conn.close();
%>
<center>
<h2 >添加学生信息</h2>
<p ><%=name%><br>
新的数据已经添加到数据库中!
</p>
<p></p>
<form name="form1" method="post" action="index.jsp">
<input type="submit" id="back" name="back" value="返回">
</form>
<p> </p>
</center>
</body>
</html>
//conn.jsp
<%
String host="localhost:3306";
String user="root";
String pw="******";
String db="jsp";
String tab="jsp";
Class.forName("com.mysql.jdbc.Driver").newInstance();
String url="jdbc:mysql://"+host+"/"+db;
Connection conn=DriverManager.getConnection(url,user,pw);
Statement stmt=conn.createStatement();
%>
建立数据库
mysql>create database jsp;
mysql>use jsp;
mysql> create table jsp(
-> name varchar(20) default NULL
-> );
执行mysql> show create database jsp;
+----------+--------------------------------------------------------------+
| Database | Create Database |
+----------+--------------------------------------------------------------+
| jsp | CREATE DATABASE `jsp` /*!40100 DEFAULT CHARACTER SET utf8 */ |
+----------+--------------------------------------------------------------+
mysql> show create table jsp;
+-------+---------------------------------------------------------------------------------------------+
| Table | Create Table |
+-------+---------------------------------------------------------------------------------------------+
| jsp | CREATE TABLE `jsp` (
`name` varchar(20) default NULL
) ENGINE=MyISAM DEFAULT CHARSET=utf8 |
+-------+---------------------------------------------------------------------------------------------+
mysql> inster into jsp(name) values('工');
mysql> select * from jsp;
+------+
| name |
+------+
| 工 |
+------+
有这样的显视,mysql没什么问题了。
今天用了一天时间来研究mysql中的编码问题,辛苦总算没有白费。
不管什么时候,编码一定要统一,这是保证数据能正确入库的前提。
首先要配置你的mysql的默认编码方式为UTF-8.
具体看my.cnf的配置.
(#cp /usr/local/mysql/support-files/my-medium.cnf /etc/my.cnf)
#下面是很重要的=====================================
[client]
#password = your_password
port = 3306
socket = /tmp/mysql.sock
default-character-set = utf8
[mysqld]
port = 3306
socket = /tmp/mysql.sock
skip-locking
key_buffer = 16M
max_allowed_packet = 1M
table_cache = 64
sort_buffer_size = 512K
net_buffer_length = 8K
read_buffer_size = 256K
read_rnd_buffer_size = 512K
myisam_sort_buffer_size = 8M
default-character-set = utf8
#===============================
当你配置好了以后,进入mysql,执行#status,可以看到以下结果。
--------------
mysql Ver 14.12 Distrib 5.0.41, for pc-linux-gnu (i686) using readline 5.0
Connection id: 1
Current database: jsp
Current user: root@localhost
SSL: Not in use
Current pager: stdout
Using outfile: ''
Using delimiter: ;
Server version: 5.0.41-log MySQL Community Server (GPL)
Protocol version: 10
Connection: Localhost via UNIX socket
Server characterset: utf8
Db characterset: utf8
Client characterset: utf8
Conn. characterset: utf8
UNIX socket: /tmp/mysql.sock
Uptime: 1 hour 12 min 21 sec
Threads: 2 Questions: 390 Slow queries: 0 Opens: 18 Flush tables: 1 Open tables: 8 Queries per second avg: 0.090
--------------
这就说明mysql编码方式为UTF-8.
下面有一个问题就是tomcat编码问题,每个页面我都设成 contentType="text/html; charset=utf-8" 但最后用request.getParameter()得到还是乱码,这只能说明在请求时编码方式不正确。
在要处理参数的页面加入request.setCharacterEncoding("utf-8");
。这时就OK了。。。。
下面为测试文件.
//index.jsp
<%@ page contentType="text/html; charset=utf-8" language="java" import="java.sql.*" errorPage="" %>
<!DOCTYPE HTML PUBLIC "-//W3C//DTD HTML 4.01 Transitional//EN" "http://www.w3.org/TR/html4/loose.dtd">
<%@ include file="conn.jsp"%>
<html>
<head>
<title></title>
</head>
<body>
<div align="center" class="style1">
<p>添加学生信息</p>
<form name="form1" method="post" action="add.jsp">
<p><span>姓名:</span>
<input type="text" name="name">
</p>
<p>
<input type="submit" name="queding" value="提交">
<input type="reset" name="chongtian" value="重置">
</p>
<p> </p>
</form>
<p> </p>
<%
ResultSet rs=stmt.executeQuery("select * from jsp");
rs.beforeFirst();
while(rs.next())
{
%>
<p><%=rs.getString("name")%></p>
<%
}
rs.close();
stmt.close();
conn.close();
%>
</div>
</body>
</html>
//add.jsp
<%@ page contentType="text/html; charset=utf-8" language="java" import="java.sql.*" errorPage="" %>
<!DOCTYPE HTML PUBLIC "-//W3C//DTD HTML 4.01 Transitional//EN" "http://www.w3.org/TR/html4/loose.dtd">
<%@ include file="conn.jsp"%>
<html>
<head>
<title></title>
</head>
<body>
<%
request.setCharacterEncoding("utf-8");//可以先取掉试试
String name=request.getParameter("name");
String sql="insert into jsp (name) values ('"+name+"')";
stmt.executeUpdate(sql);
stmt.close();
conn.close();
%>
<center>
<h2 >添加学生信息</h2>
<p ><%=name%><br>
新的数据已经添加到数据库中!
</p>
<p></p>
<form name="form1" method="post" action="index.jsp">
<input type="submit" id="back" name="back" value="返回">
</form>
<p> </p>
</center>
</body>
</html>
//conn.jsp
<%
String host="localhost:3306";
String user="root";
String pw="******";
String db="jsp";
String tab="jsp";
Class.forName("com.mysql.jdbc.Driver").newInstance();
String url="jdbc:mysql://"+host+"/"+db;
Connection conn=DriverManager.getConnection(url,user,pw);
Statement stmt=conn.createStatement();
%>
建立数据库
mysql>create database jsp;
mysql>use jsp;
mysql> create table jsp(
-> name varchar(20) default NULL
-> );
执行mysql> show create database jsp;
+----------+--------------------------------------------------------------+
| Database | Create Database |
+----------+--------------------------------------------------------------+
| jsp | CREATE DATABASE `jsp` /*!40100 DEFAULT CHARACTER SET utf8 */ |
+----------+--------------------------------------------------------------+
mysql> show create table jsp;
+-------+---------------------------------------------------------------------------------------------+
| Table | Create Table |
+-------+---------------------------------------------------------------------------------------------+
| jsp | CREATE TABLE `jsp` (
`name` varchar(20) default NULL
) ENGINE=MyISAM DEFAULT CHARSET=utf8 |
+-------+---------------------------------------------------------------------------------------------+
mysql> inster into jsp(name) values('工');
mysql> select * from jsp;
+------+
| name |
+------+
| 工 |
+------+
有这样的显视,mysql没什么问题了。