mysql乱码处理

用MS SQL和oracle9 太吃内存,有点大,全给卸载了. 用MS SQL和oracle9 太吃内存,有点大,全给卸载了. 安装了mysql占内存小,方便使用! version:mysql-essential-5.1.36 在 MySQL Command Line Client显示中文一切正常; 在eclipse中新 工程 路基工程安全技术交底工程项目施工成本控制工程量增项单年度零星工程技术标正投影法基本原理 ,连接到mysql,读取一个 表 关于同志近三年现实表现材料材料类招标技术评分表图表与交易pdf视力表打印pdf用图表说话 pdf 显示: 代码: package com.mch.mysql; import java.sql.Connection; import java.sql.DriverManager; import java.sql.ResultSet; import java.sql.SQLException; import java.sql.Statement; public class Mysql1 { public static void main(String[] args) { String url ="jdbc:mysql://localhost/test"; String user="root"; String password="******"; try { Class.forName("org.gjt.mm.mysql.Driver").newInstance(); Connection conn= DriverManager.getConnection(url,user,password); Statement stmt = conn.createStatement(); ResultSet rs = stmt.executeQuery("select * from pet"); while(rs.next()){ System.out.print("name：" + rs.getString(1)); System.out.print("\t所有者：" + rs.getString(2)); System.out.print("\tbirth：" + rs.getString("birth")); System.out.println(); } rs.close(); stmt.close(); conn.close(); } catch (InstantiationException e) { e.printStackTrace(); } catch (IllegalAccessException e) { e.printStackTrace(); } catch (ClassNotFoundException e) { e.printStackTrace(); } catch (SQLException e) { e.printStackTrace(); } } } 显示乱码?号. name：?í?í 所有者：?????? birth：2007-08-21 name：???? 所有者：?????? birth：2007-06-12 name：?? 所有者：?????? birth：2007-08-21 name：°??? 所有者：???? birth：1999-03-30 name：Puffball 所有者：Diane birth：1999-03-30 在网上查了下,很多人都有这个问题,大部分人说修改my.ini文件, //原文件 [client] port=3306 [mysql] default-character-set=latin1 [mysqld] port=3306 default-character-set=latin1 我把:default-character-set=utf-8[两处,大小写都试过] mysql不能启动. 再改成default-character-set=gb2312 可以正常启动, 但在MySQL Command Line Client显示乱码, eclipse输出也是码乱. 查了下mysql支持字符集,得到default-character-set=utf8 要这样设置,中间没有杠[-], 查看mysql使用的编码: 引用: mysql> show variables like 'character\_set\_%'; +--------------------------+--------+ | Variable_name | Value | +--------------------------+--------+ | character_set_client | utf8 | | character_set_connection | utf8 | | character_set_database | utf8 | | character_set_filesystem | binary | | character_set_results | utf8 | | character_set_server | utf8 | | character_set_system | utf8 | +--------------------------+--------+ 7 rows in set (0.00 sec) mysql> 插入数据: 引用: mysql> insert into event values("猪猪",'2009-02-21',"发烧","打针就好了!"); ERROR 1366 (HY000): Incorrect string value: '\xD6\xED\xD6\xED' for column 'name' at row 1 mysql> insert into event values("zhu",'2009-02-21',"fashao","da zhen jiu hao le!"); Query OK, 1 row affected (0.06 sec) 插入中文失败, 插入英文可以. 查看看显示: 引用: mysql> select * from pet -> ; +----------+--------------+----------+------+------------+-------+ | name | owner | species | sex | birth | death | +----------+--------------+----------+------+------------+-------+ | 脰铆脰铆 | 脥玫脮帽露芦 | 脰铆 | n | 2007-08-21 | NULL | | 鲁卢鲁卢 | 脥玫脮帽露芦 | 鹿路 | n | 2007-06-12 | NULL | | 脫茫 | 脥玫脮帽露芦 | 脫茫 | t | 2007-08-21 | NULL | | 掳垄脜拢 | 脩么脥镁 | 脜拢 | F | 1999-03-30 | NULL | | Puffball | Diane | hamster | f | 1999-03-30 | NULL | | bile | Hane | defaster | f | 2004-09-30 | NULL | +----------+--------------+----------+------+------------+-------+ 6 rows in set (0.00 sec) 另默认的那个字符集是8859-1,在这个状态下可以输入汉字. 很怪,可能很简单,新手就是不会...... 再次作测试: default-character-set=gb2312 ERROR 1366 (HY000): Incorrect string value: '\xB9\xD8\xD3\xF0' for column 'name' at row 1 向表中插入中文字符时，出现错误。 mysql> select * from pet; +--------+--------+---------+------+------------+------------+ | name | owner | species | sex | birth | death | +--------+--------+---------+------+------------+------------+ | ?í?í | ?????? | ?í | ? | 2007-08-21 | 0000-00-00 | | ???? | ?????? | ?? | ? | 2007-06-12 | 0000-00-00 | | ?? | ?????? | ?? | | 2007-08-21 | 0000-00-00 | +--------+--------+---------+------+------------+------------+ 3 rows in set (0.02 sec) 表中的中文字符位乱码。 解决办法： 使用命令： mysql> status; -------------- C:\Program Files\MySQL\MySQL Server 5.1\bin\mysql.exe Ver 14.14 Distrib 5.1.36 for Win32 (ia32) Connection id: 1 Current database: test1 Current user: root@localhost SSL: Not in use Using delimiter: ; Server version: 5.1.36-community MySQL Community Server (GPL) Protocol version: 10 Connection: localhost via TCP/IP Server characterset: gb2312 Db characterset: latin1 Client characterset: gb2312 Conn. characterset: gb2312 TCP port: 3306 Uptime: 6 min 31 sec Threads: 1 Questions: 15 Slow queries: 0 Opens: 20 Flush tables: 1 Open ta les: 9 Queries per second avg: 0.38 -------------- 查看mysql发现Db characterset的字符集设成了latin1，所以出现中文乱码。 更改表的字符集。 mysql> alter table pet character set gb2312; ------------------------------+ | pet | CREATE TABLE `pet` ( `name` varchar(20) CHARACTER SET latin1 DEFAULT NULL, `owner` varchar(20) CHARACTER SET latin1 DEFAULT NULL, `species` varchar(20) CHARACTER SET latin1 DEFAULT NULL, `sex` char(1) CHARACTER SET latin1 DEFAULT NULL, `birth` date DEFAULT NULL, `death` date DEFAULT NULL ) ENGINE=InnoDB DEFAULT CHARSET=gb2312 | +-------+-------------------------------------------------------- 查看表的结构： mysql> show create table pet; --------------------------------------+ | pet | CREATE TABLE `pet` ( `name` char(20) DEFAULT NULL, `owner` char(20) DEFAULT NULL, `species` char(20) DEFAULT NULL, `sex` char(1) CHARACTER SET latin1 DEFAULT NULL, `birth` date DEFAULT NULL, `death` date DEFAULT NULL ) ENGINE=InnoDB DEFAULT CHARSET=gb2312 | +-------+--------------------------------------------- mysql> desc pet; +---------+----------+------+-----+---------+-------+ | Field | Type | Null | Key | Default | Extra | +---------+----------+------+-----+---------+-------+ | name | char(20) | YES | | NULL | | | owner | char(20) | YES | | NULL | | | species | char(20) | YES | | NULL | | | sex | char(1) | YES | | NULL | | | birth | date | YES | | NULL | | | death | date | YES | | NULL | | +---------+----------+------+-----+---------+-------+ 6 rows in set (0.00 sec) 这时向表中插入中文然后有错误。 mysql> INSERT INTO pet VALUES ('猪八戒','唐僧','神仙','f','2001-12-01',NULL); ERROR 1366 (HY000): Incorrect string value: '\xD6\xD0\xCE\xC4' for column 'name' at row 1 还要更改pet表三个列的字符集。 因为表中已经有数据，所以更改字符集的操作失败, 清空pet表中的数据 mysql> truncate table users; 再更新三个字段的字符集: mysql>alter table pet modify name char(20) character set gb2312; mysql>alter table pet modify owner char(20) character set gb2312; mysql>alter table pet modify species char(20) character set gb2312; 这时再插入中文字符，成功。 mysql> INSERT INTO pet VALUES ('猪八戒','唐僧','神仙','f','2001-12-01',NULL); Query OK, 1 row affected (0.05 sec) mysql> select * from pet; +--------+-------+---------+------+------------+-------+ | name | owner | species | sex | birth | death | +--------+-------+---------+------+------------+-------+ | 猪八戒 | 唐僧 | 神仙 | f | 2001-12-01 | NULL | +--------+-------+---------+------+------------+-------+ 1 row in set (0.00 sec) 到此终于搞定了,花了一个下午时间........