mysql 5.1 字符集_MySQL5.1字符集设置

--------------------------------------------------------------------------------------------------------------------------------------------------

查看MySQL能够支持的多种字符集：

show character set;

查看字符集的校对规则：

show collation;

show collation like 'gb%';

每个字符集有一个默认校对规则。

--------------------------------------------------------------------------------------------------------------------------------------------------

创建数据库时设置字符集和字符校对规则：

create database db_name default character set utf8 default collate

utf8_general_ci;

修改数据库的设置字符集和字符校对规则：

alter database db_name default character set gb2312 default collate

gb2312_chinese_ci;

--------------------------------------------------------------------------------------------------------------------------------------------------

创建表时指定表和列的字符集与表和列字符校对规则：

create table table_name(

id int primary key,

name varchar(100) character

set gb2312 collate gb2312_chinese_ci

)default character set gb2312 default collate

gb2312_chinese_ci;

修改表的字符集与表的字符校对规则：

alter table table_name default character set utf8 default collate

utf8_general_ci;

修改表的列的字符集与表的列的字符校对规则：

alter table table_name modify name varchar(100) character set utf8

collate utf8_general_ci;

查看表的字符集与字符校对规则：

show create table table_name;

--------------------------------------------------------------------------------------------------------------------------------------------------

字符集和校对规则的系统变量

查看字符集系统变量：

show variables like 'character_set_%';

查看校对规则系统变量：

show variables like 'collation_%';

--------------------------------------------------------------------------------------------------------------------------------------------------

字符集系统变量介绍：

character_set_server：默认的内部操作字符集

character_set_client：客户端来源数据使用的字符集

character_set_connection：连接层字符集

character_set_results：查询结果字符集

character_set_database：当前选中数据库的默认字符集

character_set_system：系统元数据(字段名等)字符集

字符集转换过程：

1. MySQL

Server收到请求时将请求数据从character_set_client转换为character_set_connection

2. 进行内部操作前将请求数据从character_set_connection转换为内部操作字符集

--------------------------------------------------------------------------------------------------------------------------------------------------

修改连接字符集：

set names utf8;

等价于：

set character_set_client = utf8;

set character_set_results = utf8;

set character_set_connection = utf8;

注：MySQL服务重新启动后失效。

--------------------------------------------------------------------------------------------------------------------------------------------------

window下配置字符集

%MySQL5.1_HOME%\my.ini

my.ini文件中修改

客户端字符集修改，找到：

------------------------------------------------------------------------------

[client]

port=3306

[mysql]

default-character-set=GBK

------------------------------------------------------------------------------

修改为：

------------------------------------------------------------------------------

[client]

port=3306

[mysql]

default-character-set=GB2312

------------------------------------------------------------------------------

服务器端字符集修改，找到：

------------------------------------------------------------------------------

[mysqld]

port=3306

default-character-set=GBK

------------------------------------------------------------------------------

修改为：

------------------------------------------------------------------------------

[mysqld]

port=3306

default-character-set=GB2312

------------------------------------------------------------------------------

重新启动服务

再查看字符集系统变量：show variables like 'character_set_%';

--------------------------------------------------------------------------------------------------------------------------------------------------

liunx下配置字符集

找到文件：

/usr/share/mysql/my-medium.cnf

复制该文件到/etc目录下：

cp /usr/share/mysql/my-medium.cnf

/etc/my.cnf

my.cnf文件中修改

客户端字符集修改，找到：

------------------------------------------------------------------------------

[client]

------------------------------------------------------------------------------

增加default-character-set,变为:

------------------------------------------------------------------------------

[client]

default-character-set=UTF8

------------------------------------------------------------------------------

服务器端字符集修改，找到：

------------------------------------------------------------------------------

[mysqld]

------------------------------------------------------------------------------

增加default-character-set,变为:

------------------------------------------------------------------------------

[mysqld]

default-character-set=UTF8

------------------------------------------------------------------------------

重新启动mysql: /ect/init.d/mysql restart

再查看字符集系统变量：show variables like 'character_set_%';

--------------------------------------------------------------------------------------------------------------------------------------------------

MySQL的JDBC连接URL：

jdbc:mysql://localhost:3306/test?user=root&password=root&useUnicode=true&characterEncoding=utf8&autoReconnect=true&failOverReadOnly=false

在使用数据库连接池的情况下，最好设置如下两个参数：

autoReconnect=true&failOverReadOnly=false

--------------------------------------------------------------------------------------------------------------------------------------------------