mysql数据库字符集学习总结

MYSQL数据库字符集包括字符集（CHARACTER）和校对规则（COLLATION）两个概念。

MYSQL 支持的字符集和校对规则可以通过命令showcharacter set;查看。

和字符集有关的变量

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                                      |
| character_sets_dir       |/application/mysql-5.5.32/share/charsets/ |
+--------------------------+-------------------------------------------+
8 rows in set (0.00 sec)

这几个字符集含义如下：

character_set_client       #客户端字符集

character_set_connectio    #链接字符集

character_set_database     #数据库字符集，配置文件指定或建库建表指定。

character_set_filesystem    #文件系统字符集

character_set_results       #返回结果字符集

character_set_server       #服务器字符集，配置文件指定货建库建表指定。

character_set_system       #系统字符集

要解决MYSQL 乱码问题 需要做到字符集6个统一

1、统一客户端字符集 可以通过 set names 命令修改 也可以单独修改以下几个变量

方法1：set names gbk  控制客户端的字符集

方法2：单独修改下面三个参数

set character_set_client gbk;

set character_set_connectio gbk;

set character_set_results gbk;

方法3：也可以在进入MYSQL的时候加 --default-character-set=gbk 参数。

mysql-uroot -p123456 -S /data/3306/mysql.sock --default-character-set=gbk;

方法4：永久生效需要修改my.cnf 配置文件在[client]模块中添加下面参数

[client]

default-character-set=gbk   如果是多实例配置需要修改/etc/my.cnf

2、统一MYSQL 服务端字符集

方法1：按如下要求更改my.cnf参数

[mysqld]

default-character-set=latin1  适合5.1之前的版本

character-set-server=latin1   适合5.5

 对应变量中的以下两个参数：

character_set_database

character_set_server

方法2：编译的时候指定服务器端字符集：

-DDEFAULT_CHARSET=utf8 \

-DDEFAULT_COLLATION=utf8_general_ci \

-DEXTRA_CHARSETS=gbk,gb2312,utf8,ascii \

3、字段、建表、建库字符集也要统一

指定字符集建库：

create database oldboy_utf8 DEFAULT CHARACTER SETUTF8 COLLATE utf8_general_ci;

修改数据库字符集命令：

alter database oldboy character set utf8 collate utf8_general_ci;

指定字符集建表：

CREATE TABLE `test` (
  `id` int(4)NOT NULL AUTO_INCREMENT,
  `name`char(20) NOT NULL,
  PRIMARY KEY(`id`),
  KEY`index_name` (`name`)
) ENGINE=InnoDB AUTO_INCREMENT=6 DEFAULT CHARSET=utf8

修改数据表字符集命令：

把表默认的字符集和所有字符列（CHAR,VARCHAR,TEXT）改为新的字符集

alter table test CONVERT TO character set utf8 collate utf8_general_ci;

仅修表的默认字符集

alter table test DEFAULT character set utf8 collate utf8_general_ci;

修改字段的字符集

alter table test CHANGE name name varchar(16) character set utf8 collate utf8_general_ci;

相关查看命令

show create database oldboy;

show create table test;

show FULL COLUMNS from test;

4、程序所使用的字符集要和数据库 表 字段 的统一

5、LINUX系统字符集也要统一

cat /etc/sysconfig/i18n
LANG="en_US.UTF-8"
SYSFONT="latarcyrheb-sun16"

6、LINUX客户端 如 CRT  XSHELL 等的字符集也要统一 可以在软件属性里设置。

如果数据库有乱码 可按以上6点 逐一进行排查

如何选择适合的字符集？

1、如果是处理各种各样的文字，发布到不同语言国家地区，应选UNICODE字符集，对MYSQL来说就是UTF-8 （每个汉字三字节），如果应用需处理英文，仅有少量汉字UTF-8更好。

2、如只需支持中文，并且数据量很大，性能要求也很高，可选GBK（定长每个汉字占双字节，英文也占双字节），如果需大量运算，比较排序等，定长字符集，更快，性能更高。

3、处理移动互联网业务，可能需要使用utf8mb4字符集。

建议：没有特别需求，请选择UTF8.

很多开源程序都会有多种字符集版本的程序。

在数据库中执行sql语句的方法

1）操作习惯：尽量不在MYSQL命令行插入数据（SSH客户端影响）

2）sql文件的格式统一用“utf8没签名”。

3）导入文件的方式

a、可在MYSQL命令行中用source 执行sql文件。

b、命令方式导入数据 mysql -uroot -p123456oldboy<test.sql

4）sql文件里 set names utf8,或mysql -uroot -p123456 oldboy -default-character-set=utf8<test.sql。

更改数据库字符集的方法：

分两种情况：

1、空表无数据

直接通过以上命令修改即可

2、如果表里已经有数据了

此时不能直接修改表的字符集了，因为修改后的字符集只会对新插入的数据生效，之前库的数据并不起作用。此时我们可以按以下思路操作修改数据库字符集

1）导出表结构

mysqldump-uroot -p --default-character-set =latin -d dbname>alltable.sql

2）编辑表结构语句alltable.sql 通过sed替换命令将所有原字符集的字符串改成新的字符集的。

3）锁表确保数据库不再更新，导出所有数据（不带表结构）

mysqldump-uroot -p --quick --no-create-info --extended-insert --default-character-set=latin dbname>alltable.sql

 --no-create-info 不导出表结构 

4）修改my.cnf 配置调整客户端及服务端字符集，重启生效

5）通过新字符集建库（可选）

删除原库，然后create database dbname default charset utf8;

6）导入表结构（更改过字符集的表结构）

mysql-uroot -p dbname <alltable.sql

7）导入数据

mysql-uroot -p dbname <alldata.sql