用MS SQL和oracle9 太吃内存,有点大,全给卸载了.安装了mysql占内存小,方便使用!version:mysql-essential-5.1.36在 MySQL Command Line Client显示中文一切正常;在eclipse中新工程,连接
安装了mysql占内存小,方便使用!
version:mysql-essential-5.1.36
在 MySQL Command Line Client显示中文一切正常;
在eclipse中新工程,连接到mysql,读取一个表显示:
|
package com.mch.mysql;
imp imp imp imp imp 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: 'xD6xEDxD6xED'
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: 'xB9xD8xD3xF0' 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 FilesMySQLMySQL Server 5.1binmysql.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: 'xD6xD0xCExC4' 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)
到此终于搞定了,花了一个下午时间........
声明： 此文观点不代表本站立场；转载须要保留原文链接；版权疑问请联系我们。
