mysql中文乱码终结
2008-03-20 13:00
接下来就是创建数据库,名字为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 从 employee! 有的情况是:| 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
接着你再看看执行那个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文件 |