mysql数据库字符集学习总结

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

成都创新互联是一家以重庆网站建设公司、网页设计、品牌设计、软件运维、营销推广、小程序App开发等移动开发为一体互联网公司。已累计为成都公路钻孔机等众行业中小客户提供优质的互联网建站和软件开发服务。

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

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

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

分两种情况：

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

7）导入数据

mysql-uroot -p dbname