分类： Mysql/postgreSQL
2010-06-11 11:50:46
MySQL 4.1的字符集支持(Character Set Support)有两个方面：字符集(Character set)和排序方式(Collation)。对于字符集的支持细化到四个层次: 服务器(server)，数据库(database)，数据表(table)和连接(connection)。
mysql> SHOW VARIABLES LIKE 'character_set_%';
| character_set_database | latin1 |
| character_set_filesystem | binary|
| character_set_results | latin1 |
| character_set_server | latin1 |通过修改mysql.ini/my.ini配置文件 default_character_set=utf8后
mysql> SHOW VARIABLES LIKE 'character_set_%'; | character_set_database | utf8| | character_set_filesystem | binary|
+--------------------------+----------------------------+
| Variable_name | Value |
+--------------------------+----------------------------+
| character_set_client | latin1 |
| character_set_connection | latin1 |
| character_set_server | utf8|
| character_set_system | utf8 |
| character_sets_dir | /usr/share/mysql/charsets/ |
+--------------------------+----------------------------+
7 rows in set (0.00 sec)
mysql> SHOW VARIABLES LIKE 'collation_%';
+----------------------+-------------------+
| Variable_name | Value |
+----------------------+-------------------+
| collation_connection | latin1_swedish_ci |
| collation_database | utf8_general_ci |
| collation_server | utf8_general_ci |
+----------------------+-------------------+
3 rows in set (0.00 sec)
其实乱码出现就几个层次：页面编码控制了显示和输出，IDE的代码编码控制了编译，tomcat URIEncoding控制了服务器的编码，连接方式控制了server端连接访问mysql编码，mysql控制层次是如上提到的：表，数据库，连接，服务器，其中mysql的默认服务器utf-8，其余都是默认编译时的编码。
最方便和不出问题的解决办法，就是所有配置均使用统一编码。当然这也不是万无一失的，笔者就碰见过在手机浏览器上input乱码情况，这和客户端编码正确与否也有很大关系。
MySQL默认编码是latin1
mysql> show variables like 'character%';
+--------------------------+--------------------------+
| Variable_name | Value |
+--------------------------+--------------------------+
| character_set_client | latin1 |
| character_set_connection | latin1 |
| character_set_database | latin1 |
| character_set_filesystem | binary |
| character_set_results | latin1 |
| character_set_server | latin1 |
| character_set_system | utf8 |
| character_sets_dir | D:\MySQL\share\charsets\ |
+--------------------------+--------------------------+
创建数据表并插入数据
mysql> use test;
mysql> create table messages (
-> id int(4) unsigned auto_increment primary key,
-> message varchar(50) not null
-> ) engine=myisam default charset=utf8;
mysql> insert into messages (message) values ("测试MySQL中文显示"); mysql> select * from messages;
+----+-------------------+
| id | message |
+----+-------------------+
| 1 | 测试MySQL中文显示 |
+----+-------------------+
编写程序（Java）
import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.ResultSet;
import java.sql.Statement;
public class JDBCTest {
public static void main(String[] args) {
String driver = "com.mysql.jdbc.Driver";
String url = "jdbc:mysql://localhost:3306/test";
String user = "root";
String password = "root";
try {
Class.forName(driver);
Connection conn = DriverManager.getConnection (url,user,password);
Statement stmt = conn.createStatement();
stmt.executeUpdate("insert into messages (message) values ('测试MySQL编码')");
ResultSet rs = stmt.executeQuery("select * from messages");
while (rs.next()) {
int id = rs.getInt("id");
String message = rs.getString("message");
System.out.println(id + " " + message);
}
rs.close();
stmt.close();
conn.close();
} catch (Exception e) {
e.printStackTrace();
}
}
}
程序输出 1 ????MySQL???????? 2 ??MySQL??
我们看到，尽管使用数据库时我们能够正常的添加和显示中文，但是在使用程序连接数据库时并不能够正常显示中文，为此我们需要修改MySQL的默认编码，编辑mysql.ini/my.ini（MySQL配置文件）文件对编码进行修改
设置MySQL的默认字符集为utf8，找到客户端配置[client]在下面添加。
default-character-set=utf8
找到服务器配置[mysqld]在下面添加
default-character-set=utf8
设定MySQL数据库以utf8编码运行，连接MySQL数据库时使用utf8编码
停止和重新启动MySQL
net stop mysql
net start mysql
重新连接数据库，查看编码，数据表内容
mysql> show variables like 'character%';
+--------------------------+--------------------------+
| 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 |
| character_sets_dir | D:\MySQL\share\charsets\ |
+--------------------------+--------------------------+
mysql> use test; mysql> select * from messages;
+----+-------------------------------+
| id | message |
+----+-------------------------------+
| 1 | 虏芒脢脭MySQL脰脨脦脛脧脭脢戮 |
| 2 | ??MySQL?? |
+----+-------------------------------+
这里显示依然是乱码主要是因为之前使用的编码不同造成的，
重新运行之前写的程序：
java JDBCTest 1 ????MySQL???????? 2 ??MySQL?? 3 测试MySQL编码
从第三条记录我们可以看到现在程序连接数据库时可以正常的添加和显示中文了
mysql> select * from messages;
+----+-------------------------------+
| id | message |
+----+-------------------------------+
| 1 | 虏芒脢脭MySQL脰脨脦脛脧脭脢戮 |
| 2 | ??MySQL?? |
| 3 | 娴嬭瘯MySQL缂栫爜 |
+----+-------------------------------+
看回数据库的显示，我们会很奇怪的发现为什么显示的都是乱码，这主要是和windows下命令行的编码有关，在命令行上查看属性->选项的当前代码页：936 (ANSI/OEM - 简体中文 GBK)（本人机子上是这样显示的）
也就是说命令行上使用的是GBK编码，而我们是在程序连接时使用utf8进行添加的，所以会出现有乱码，现在我们将客户端的编码改成gb2312或gbk试一下
mysql> show variables like 'character%';
+--------------------------+--------------------------+
| Variable_name | Value |
+--------------------------+--------------------------+
| character_set_client | gb2312 |
| character_set_connection | gb2312 |
| character_set_database | utf8 |
| character_set_filesystem | binary |
| character_set_results | gb2312 |
| character_set_server | utf8 |
| character_set_system | utf8 |
| character_sets_dir | D:\MySQL\share\charsets\ |
+--------------------------+--------------------------+
mysql> use test; mysql> select * from messages;
+----+-------------------+
| id | message |
+----+-------------------+
| 1 | ????MySQL???????? |
| 2 | ??MySQL?? |
| 3 | 测试MySQL编码 |
+----+-------------------+
现在可以看到中文正常显示了（主要是因为utf8也支持中文），所以当我们使用命令行工具连接数据库的时候最好将客户端的编码改一下，如果使用GUI的话就不必了，同时修改客户端的编码之后程序依然能够正常显示（以上两点已经测试）
所以如果在程序中要显示中文的话我们可以选用utf8，gb2312，gbk这三种编码，但是如果想在命令行添加中文数据或者查看的话就需要将客户端的编码设置为gb2312或gbk了，还是那句，CMD的编码有关
实例二 mysql乱码处理总结:
(1)java中处理中文字符正常,在cmd client中显示乱码是字符集的问题.
(2)字段长度设置够长,但插入中文字符时提示com.mysql.jdbc.MysqlDataTruncation: Data truncation: Data too long for column错误是字符集的问题.
(3)乱码问题归根到底是字符集的问题,那就从字符集设置方面考虑,不外乎下面几个方面:server,client,database,connection,results.
-------------------------解决办法----------------------
(1)修改 mysql.ini/my.ini(MySQL Server Instance Configuration 文件)
# CLIENT SECTION
[client]
port=3306
[mysql]
default-character-set=gbk
# SERVER SECTION
[mysqld]
default-character-set=gbk
(2)修改data目录中相应数据库目录下的db.opt配置文件
default-character-set=gbk
default-collation=gbk_chinese_ci
(3)数据库连接串中指定字符集
URL=jdbc:mysql://yourIP/college?user=root&password=yourPassword&useUnicode=true&characterEncoding=gbk 注:在mysql4.1.0版 要加useUnicode=true&characterEncoding=gbk 如果是5.0版以上的，加不加都是没有关系的！
(4)在创建数据库时指定字符集
create database yourDB CHARACTER SET gbk;
(5)在dos命令上要设置
set names gbk
实例三 1366 (HY000): Incorrect string value: '\xD6\xD0\xCE\xC4' for column
出现的问题：
mysql> update users
-> set username='关羽'
-> where userid=2;
ERROR 1366 (HY000): Incorrect string value: '\xB9\xD8\xD3\xF0' for column 'usern
ame' at row 1
向表中插入中文字符时，出现错误。
mysql> select * from users;
+--------+----------+
| userid | username |
+--------+----------+
| 2 | ???? |
| 3 | ???? |
| 4 | ?í?ù |
+--------+----------+
3 rows in set (0.00 sec)
表中的中文字符位乱码。
解决办法：
使用命令：
mysql> status;
--------------
mysql Ver 14.12 Distrib 5.0.45, for Win32 (ia32)
Connection id: 8
Current database: test
Current user: root@localhost
SSL: Not in use
Using delimiter: ;
Server version: 5.0.45-community-nt MySQL Community Edition (GPL)
Protocol version: 10
Connection: localhost via TCP/IP
Server characterset: latin1
Db characterset: latin1
Client characterset: gbk
Conn. characterset: gbk
TCP port: 3306
Uptime: 7 hours 39 min 19 sec
Threads: 2 Questions: 174 Slow queries: 0 Opens: 57 Flush tables: 1 Open ta
bles: 1 Queries per second avg: 0.006
--------------
查看mysql发现Server characterset，Db characterset的字符集设成了latin1，所以出现中文乱码。
mysql> show tables;
+----------------+
| Tables_in_test |
+----------------+
| users |
+----------------+
1 row in set (0.00 sec)
更改表的字符集。
mysql> alter table users character set GBK;
Query OK, 3 rows affected (0.08 sec)
Records: 3 Duplicates: 0 Warnings: 0
查看表的结构：
mysql> show create users;
ERROR 1064 (42000): You have an error in your SQL syntax; check the manual that
corresponds to your MySQL server version for the right syntax to use near 'users
' at line 1
mysql> show create table users;
+-------+-----------------------------------------------------------------------
------------------------------------------------------------------------------+
| Table | Create Table
|
+-------+-----------------------------------------------------------------------
------------------------------------------------------------------------------+
| users | CREATE TABLE `users` (
`userid` int(11) default NULL,
`username` char(20) character set latin1 default NULL
) ENGINE=InnoDB DEFAULT CHARSET=gbk |
+-------+-----------------------------------------------------------------------
------------------------------------------------------------------------------+
1 row in set (0.00 sec)
mysql> desc users;
+----------+----------+------+-----+---------+-------+
| Field | Type | Null | Key | Default | Extra |
+----------+----------+------+-----+---------+-------+
| userid | int(11) | YES | | NULL | |
| username | char(20) | YES | | NULL | |
+----------+----------+------+-----+---------+-------+
2 rows in set (0.02 sec)
这时向表中插入中文然后有错误。
mysql> insert into users values(88,'中文');
ERROR 1366 (HY000): Incorrect string value: '\xD6\xD0\xCE\xC4' for column 'usern
ame' at row 1
mysql> insert into users values(88,'中文');
ERROR 1366 (HY000): Incorrect string value: '\xD6\xD0\xCE\xC4' for column 'usern
ame' at row 1
还要更改users表的username的字符集。
mysql> alter table users modify username char(20) character set gbk;
ERROR 1366 (HY000): Incorrect string value: '\xC0\xEE\xCB\xC4' for column 'usern
ame' at row 1
mysql> alter table users modify username char(20) character set gbk;
ERROR 1366 (HY000): Incorrect string value: '\xC0\xEE\xCB\xC4' for column 'usern
ame' at row 1
因为表中已经有数据，所以更改username字符集的操作没有成***
清空users表中的数据
mysql> truncate table users;
Query OK, 3 rows affected (0.01 sec)
从新更改user表中username的字符集
mysql> alter table users modify username char(20) character set gbk;
Query OK, 0 rows affected (0.06 sec)
Records: 0 Duplicates: 0 Warnings: 0
这时再插入中文字符，插入成***。
mysql> insert into users values(88,'中文');
Query OK, 1 row affected (0.01 sec)
mysql> select * from users;
+--------+----------+
| userid | username |
+--------+----------+
| 88 | 中文 |
+--------+----------+
1 row in set (0.00 sec)
mysql>
相关链接：