MySQL中文乱码

woodpeckerboy

浏览: 84340 次
性别:
来自: 广州

最近访客更多访客>>

sliu123

pengshuo1981

liangzi_001

MyDicta

博主相关

博客

微博

相册

留言

关于我

文章分类

社区版块

存档分类

博客分类：

MySQL

背景：
从另外一台机子上用 Navicat 导出 mysql 版本是5.01.41-community 数据库为ibank.sql文件。

1.在我的win7 64bit mysql-4.01.41-community上用 navicat 导入成功。
问题：java的后台获取到的是乱码。显示到前台页面乱码。mysql查出来的是乱码。navicat里面是OK的，有中文显示.
连接属性：

2.在我的win7 64bit mysql-4.01.41-community上用
mysql -u root -p ibank<F:\ibank.sql
导入成功。

问题：java的后台获取到的是中文。显示到前台页面中文。mysql查出来的是乱码。navicat里面是乱码.用MySQL WorkBench显示到的是中文。

INSERT INTO sys_user(id,createTime,updateTime,version,bz,email,lastvisit,mobile,password,realname,sfsc,username,sfqy,type)
VALUES ('21', '2013-10-05 09:27:54', '2013-10-05 09:27:54', '1', ' fsdf ', 'liuhaihui1988824@163.com', null, '13600332958',
'e10adc3949ba59abbe56e057f20f883e', '刘海辉', null, 'woodpecker', '0', 'COMMONUSER');

成功插入。

正确的操作步骤：

1.安装数据库的时候指定:utf8
2.建表的时候用utf8

DROP TABLE IF EXISTS sys_user;
CREATE TABLE sys_user (
  id bigint(20) NOT NULL AUTO_INCREMENT,
  createTime datetime NOT NULL,
  updateTime datetime NOT NULL,
  version int(11) NOT NULL,
  bz varchar(150) DEFAULT NULL,
  email varchar(50) DEFAULT NULL,
  lastvisit varchar(20) DEFAULT NULL,
  mobile varchar(20) DEFAULT NULL,
  password varchar(255) NOT NULL,
  realname varchar(255) NOT NULL,
  sfsc int(11) DEFAULT NULL,
  username varchar(255) NOT NULL,
  sfqy int(11) DEFAULT NULL,
  type varchar(255) DEFAULT NULL,
  PRIMARY KEY (id)
) ENGINE=InnoDB AUTO_INCREMENT=21 DEFAULT CHARSET=utf8;

3.navicat 新建连接的时候指定 utf8
4.导入数据库的时候指定 utf8 和 utf8-generate-ci

常用的mysql乱码修改编码语句：

set character_set_client = gbk;
set character_set_results = gbk;
set character_set_connection = gbk;
set character_set_server = gbk;

set character_set_client = gb2312;
set character_set_results = gb2312;
set character_set_connection = gb2312;
set character_set_server = gb2312;
set character_set_system = gb2312;

set character_set_client = utf8;
set character_set_results = utf8;
set character_set_connection = utf8;
set character_set_server = utf8;


set character_set_client = latin1;
set character_set_results = latin1;
set character_set_connection = latin1; 
set character_set_server = latin1;
set character_set_database = latin1;

show variables like 'character_set_%';



drop database ibank;
CREATE DATABASE ibank DEFAULT CHARACTER SET utf8 COLLATE utf8_general_ci;


--这个是把数据库的数据导出到.sql文件
mysql -u root -p ibank>F:\project\ibank\ibank_v2.sql

--这个是把.sql文件的数据导入到数据库
mysql -u root -p ibank<F:\project\ibank\ibank_v2.sql

mysql -u root -p

select realname from sys_user;

C:\Program Files (x86)\MySQL\MySQL Server 5.1\bin


ALTER DATABASE ibank DEFAULT CHARACTER SET utf8 COLLATE utf8_general_ci;
--ALTER DATABASE ibank DEFAULT CHARACTER SET gb2312 COLLATE gb2312_chinese_ci;

小Tips：

mysql> show create database ibank;
ERROR 2006 (HY000): MySQL server has gone away
No connection. Trying to reconnect...
Connection id:    2
Current database: ibank

+----------+----------------------------------------------------------------+
| Database | Create Database                                                |
+----------+----------------------------------------------------------------+
| ibank    | CREATE DATABASE `ibank` /*!40100 DEFAULT CHARACTER SET utf8 */ |
+----------+----------------------------------------------------------------+
1 row in set (0.00 sec)

mysql> select realname from sys_user;
+-----------------+
| realname        |
+-----------------+
| 绯荤粺绠＄悊鍛?     |
| 灏忓己            |
| 鏉庢槑            |
| 鐜嬬惣            |
| 钄℃槑鍠?         |
| 鍗″崱            |
| 钃濆ぉ            |
| 鍗″崱            |
| 鏉庡姞            |
| 鏄傛槀            |
| 鏄傛槀            |
| 鐜嬬惣鐞?         |
| 鍛ㄤ笟鎴?         |
+-----------------+
13 rows in set (0.08 sec)

mysql> exit;
Bye

C:\Users\Administrator>net stop mysql
MySQL 服务正在停止..
MySQL 服务已成功停止。


C:\Users\Administrator>net start mysql
MySQL 服务正在启动 .
MySQL 服务已经启动成功。


C:\Users\Administrator>mysql -u root -p
Enter password: ***
Welcome to the MySQL monitor.  Commands end with ; or \g.
Your MySQL connection id is 1
Server version: 6.0.11-alpha-community MySQL Community Server (GPL)

Type 'help;' or '\h' for help. Type '\c' to clear the current input statement.

mysql> use ibank;
Database changed
mysql> select realname from sys_user;
+------------+
| realname   |
+------------+
| 系统管理员 |
| 小强       |
| 李明       |
| 王琼       |
| 蔡明喜     |
| 卡卡       |
| 蓝天       |
| 卡卡       |
| 李加       |
| 昂昂       |
| 昂昂       |
| 王琼琼     |
| 周业成     |
+------------+
13 rows in set (0.09 sec)

mysql>

这里是因为我用MySQL Server Instance Config Wizard 将编码设置为gb2312的原因。

<?xml version="1.0" encoding="UTF-8"?>
<persistence xmlns="http://java.sun.com/xml/ns/persistence" version="1.0" 
	xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance"
	xsi:schemaLocation="http://java.sun.com/xml/ns/persistence persistence_1_0.xsd ">

    <persistence-unit name="AppProductPersistenceUnit" transaction-type="RESOURCE_LOCAL">
    	<provider>org.hibernate.ejb.HibernatePersistence</provider>
		<properties>
   			<property name="hibernate.dialect" value="org.hibernate.dialect.MySQLDialect"/>
   			<property name="hibernate.connection.driver_class"  value="com.mysql.jdbc.Driver"/>
   			<property name="hibernate.connection.username" value="root" />
				<property name="hibernate.connection.password" value="123" />
				<property name="hibernate.connection.url" value="jdbc:mysql://localhost:3306/ibank?useUnicode=true&amp;characterEncoding=UTF-8"/>
				<property name="hibernate.hbm2ddl.auto" value="update" />
				<property name="hibernate.show_sql" value="true" />
				<property name="hibernate.format_sql" value="true" />
		</properties>
    </persistence-unit>
</persistence>

### \u5f00\u53d1\u73af\u5883\u6570\u636e\u914d\u7f6e
jdbc.driver=com.mysql.jdbc.Driver
jdbc.url=jdbc:mysql://localhost:3306/ibank?useUnicode=true&characterEncoding=UTF-8
jdbc.username=root
jdbc.password=123

### \u6570\u636e\u5e93\u8fde\u63a5\u6c60\u7684\u6700\u5c0f\u8fde\u63a5\u6570\u91cf\u3001\u6700\u5927\u8fde\u63a5\u6570\u91cf\u3001\u521d\u59cb\u8fde\u63a5\u6c60\u5927\u5c0f
jdbc.minPoolSize=1
jdbc.maxPoolSize=3
jdbc.initialPoolSize=2

### \u8fde\u63a5\u7684\u6700\u5927\u7a7a\u95f2\u65f6\u95f4\uff08\u5355\u4f4d\u79d2\uff0c0\u8868\u793a\u6c38\u4e0d\u4e22\u5f03\uff09\u3001\u8fde\u63a5\u8017\u5c3d\u65f6\u4e00\u6b21\u83b7\u53d6\u7684\u8fde\u63a5\u6570\u91cf
jdbc.maxIdleTime=0
jdbc.acquireIncrement=1