MySQL数据库字符集

目录

• 1.MySQL数据库字符集知识
  • 1.1.什么是字符集
  • 1.2.MySQL数据库字符集
  • 1.3.常用字符集介绍与选择建议
    • 1.3.1.常用字符集介绍
    • 1.3.2.MySQL如何选择合适的字符集
    • 1.3.3.查看MySQL数据库字符集和校对规则
• 2.MySQL数据库字符集配置
  • 2.1.Linux系统服务端字符集设置
  • 2.2.Linux系统客户端字符集设置
  • 2.3.MySQL服务端数据库字符集设置
  • 2.4.MySQL数据库中的库的字符集设置
  • 2.5.MySQL数据库表的字符集设置
  • 2.6.MySQL数据库客户端字符集设置
• 3.防止数据库的中文显示乱码
  • 彻底防止MySQL数据库内的数据中文乱码方法
• 4.更改MySQL数据库库表的字符集
  • 4.1.更改库的字符集
  • 4.2.更改表的字符集
  • 4.3.生产环境更改数据库（含数据）字符集的方法

1.MySQL数据库字符集知识

1.1.什么是字符集

计算机只能识别0和1这样的二进制数字，无论是处理计算机程序，还是进行科学运算，最终都要转换为二进制数据来完成操作；例如，我们输入一个数字“8”，计算机会将其识别成二进制数字“1000”。

但是，计算机要处理的数据不仅仅是数字，还会有字母，为了处理字母，就产生了ASCII码系统。英文字母共有26种变化，算上大小写也才52种变化，即使加上特殊的英文标点符号、特殊字符，变化也不多，而用8位二进制数字可以表达256种字符，也就是说，8位二进制数字就足以胜任英文字符的处理工作了。

但是，各个国家的语言文字大多不同，不仅仅是数字、字母以及特殊字符。例如中国的汉字数量就有数万之多，常用的有几千个。这时，使用ASCII编码就会无法满足需求，于是就有了GBK、BIG5、GB2312这类的字符编码，采用16位二进制数可以表达65535个汉字，这对于常用的汉字使用来说就足够用了。

现在，在简体中文环境下，常用的编码除了GB2312和GB18030之外，还会用到UTF-8。GBK是专门用作中文的字符编码规范，UTF是通用转换格式的缩写，又可称为万国码，理论上来说，UTF可以表达各种文字的编码格式。

字符编码其实就是将人类使用的英文字母、汉字、特殊符号等信息，通过预先设定的转换规则，将其转换为计算机可以识别的二进制数字的一种编码方式。

1.2.MySQL数据库字符集

字符集其实就是一套文字符号及编码，对应的文字及编码，可以将人类可以识别的内容与计算机可以识别的信息进行互相转换。

一个字母表使用了四个字母：A、B、a、b。每个字母赋予一个数值：A=0，B=1，a=2，b=3。字母A是一个符号，数字0是A的编码，这四个字母和它们的编码组合在一起就可以称为一个字符集。

MySQL数据库的字符集不仅包括字符集（CHARACTER），还包括校对规则（COLLATION）。其中，校对规则的作用是定义比较字符串的方式。

假设比较两个字符串的值：A和B。最简单的方法是查找编码：A为0，B为1。因为0小于1，所以可以说A小于B。所做的仅仅是在字符集上应用了一个校对规则。校对规则是一套规则，作用是对编码进行比较。

1.3.常用字符集介绍与选择建议

1.3.1.常用字符集介绍

在操作系统以及各类软件中都有字符集，MySQL也不例外。

常用字符集知识：

常用字符集	最大长度	说明
GB2312	2字节	早期制定的标准，不推荐使用
GB18030	4字节	受一些系统支持，数据库支持的不多，不推荐使用
GBK	2字节	不是国际标准，对中文环境支持的很好，不推荐使用
UTF8	3字节	中英文混合的环境，建议使用此字符集，目前使用的比较多，互联网场景的Linux/UNIX及MySQL都支持UTF8，重点推荐
latin1	1字节	MySQL系统的默认字符集，不推荐使用
utf8mb4	4字节	utf8mb4字符集主要从5.5开始被支持，兼容UTF8，且比UTF8能表示更多的字符，正在成为未来趋势字符集，重点推荐

1.3.2.MySQL如何选择合适的字符集

1、如果存储的是各种各样的语言文字，则可以选择UTF8，这是目前国内应用最为广泛的字符集，没有之一。
2、如果只需要支持中文，并且数据量很大，此外，还包含了大量的运算，则可以选择GBK，理论上其可以获得更高的性能，但不推荐使用。
3、对于新型的互联网以及移动互联网的混合业务，推荐使用utf8mb4字符集替代UTF8字符集。总之，如果没有极特别的需求，请选择UTF8或utf8mb4作为数据库的字符集。
4、如果使用开源程序，则可以根据上述说明进行选择，如果是公司开发人员自己开发产品，那么选择权就在开发人员手里，DBA只能提供建议。

1.3.3.查看MySQL数据库字符集和校对规则

查看当前MySQL系统支持的字符集

MySQL数据库支持的字符集有很多种，通过命令可以查看当前MySQL支持的字符集：

show character set;

2.MySQL数据库字符集配置

设置MySQL的字符集需要考虑到很多个层次，将这些需要考虑的层次大概分为7个级别。

1、操作系统级别。
2、操作系统客户端级别（SSH）。
3、MySQL实例级别。
4、数据库中的库级别。
5、表级别（含字段级别）。
6、MySQL客户端级别（连接及返回结果）。
7、程序代码级别。

2.1.Linux系统服务端字符集设置

很多人在使用MySQL时经常会被中文乱码所困扰，其中Linux系统和连接Linux系统客户端的字符集设置可能就是问题之一，对此，要尽量将系统的字符集和系统中软件的字符集进行统一，设置和生效的方法：

vim /etc/sysconfig/il8n  #配置到配置文件里可以永久生效。
LANG="zh_CN.UTF-8"  #LANG为系统字符集环境变量，设置为中文UTF8.
SYSFONT="latarcyrheb-sun16"

source /etc/sysconfig/il8n  #使得修改生效。
echo $LANG  #检查生效情况。

在Linux服务器里使用MySQL登录到数据库，要注意系统字符集的使用。

2.2.Linux系统客户端字符集设置

常见的连接Linux的客户端为SecureCRT、XShell。

SecureCRT：会话选项-终端-外观-字符编码
XShell：地球-下拉箭头

2.3.MySQL服务端数据库字符集设置

设置服务器的字符集有很多种方法，常用的三种方法：

方法一：在编译安装MySQL的时候指定服务器端字符集。

cmake .
-DDEFAULT_CHARSET=utf8 \
-DDEFAULT_COLLATION=utf8_general_ci \
-DWITH_EXTRA_CHARSETS=all \

方法二：编译时没指定字符集，或者指定了不合适的字符集，也还可以在安装后修改配置文件。

[mysqld]
character-set-server=utf8

方法三：可以在启动数据库时，增加选项指定的字符集。

mysqld --character-set-server=utf8

2.4.MySQL数据库中的库的字符集设置

在MySQL中，库的字符集设置一般是在建库的时候指定的，如果在建库的时候未指定，则库的字符集与MySQL数据库实例的字符集一致。可通过命令查看当前实例的字符集：

show variables like 'character_set_database%';
show variables like 'collation_database%';

创建数据库test，并查看建库的字符集：

create database test;
show create database test\G

在编译MySQL时，若指定了正确的字符集或者修改配置文件调整过的服务器的字符集，那么，在以后建库的时候就可以直接执行简化的命令“create database test;”。

也可以在建库的时候指定字符集和校对规则来建库;

create database oldboy default character set utf8 default collate = utf8_general_ci;

"CHARACTER SET UTF8"即为数据库字符集，而“utf8_general_ci”则为校对规则。

2.5.MySQL数据库表的字符集设置

建表的字符集与库的字符集应一致，设置表字符集的命令：

use test;
create table test(id int(4));
show create table test\G;

2.6.MySQL数据库客户端字符集设置

对MySQL数据库客户端字符集进行设置，对于防止MySQL更新时，出现中文乱码有极大的影响，设置方法也有几种。

方法一：临时生效单条命令法。

set names utf8;

“set names utf8”也可以用三个命令来替代。
set character_set_client = utf8;
set character_set_results = utf8;
set character_set_connection = utf8;

方法二：登录数据库时指定字符集。

mysql --default-character-set=utf8;

方法三：通过修改my.cnf实现修改MySQL客户端的字符集，配置方法。

[client]
default-character-set=utf8

人工登录数据库执行“set names UTF8”，以及使用MySQL命令指定字符集登录操作，或者更改my.cnf配置文件客户端模块的参数，来实现更改客户端字符集，都是改变了MySQL客户端的client、connection、results3个参数的字符集。

3.防止数据库的中文显示乱码

管理员在配置MySQL数据库字符集时，需要尽可能地确保7大项字符集统一，对于管理员来说，查看数据库字符集的基本方法：

show variables like 'character_set%';
character_set_client  #客户端字符集
character_set_connection  #客户端连接字符集
character_set_database  #数据库字符集，配置文件时指定或建库建表时指定
character_set_filesystem  #文件系统字符集
character_set_results  #客户端返回结果字符集
character_set_server  #服务器字符集，配置文件时指定或建库建表时指定
character_set_system  #系统字符集

更改Linux系统字符集变量之后，可以查看MySQL中字符集的变化。

彻底防止MySQL数据库内的数据中文乱码方法

字符集的不一致是数据库乱码的罪魁祸首，要想避免MySQL数据库内的数据中文乱码方法，就要遵循7大项字符集设置规则，即Linux系统服务端与Linux系统客户端字符集、MySQL服务端数据库实例与MySQL数据库客户端字符集、MySQL数据库中的库和表的字符集、程序代码的字符集要一致。如果是利用文件还原数据，还要注意文件的编码问题。

4.更改MySQL数据库库表的字符集

4.1.更改库的字符集

管理员可以使用alter命令对数据库的字符集进行更改：

show create database oldboy\G
alter database oldboy character set latin1 collate = latin1_swedish_ci;
show create database oldboy\G
alter database oldboy character set utf8 collate utf8_general_ci;
show create database oldboy\G

4.2.更改表的字符集

管理员也可以使用alter命令对数据库的表的字符集进行更改：

use test;
show create table t1\G
alter table t1 character set latin1;
show create table t1\G

4.3.生产环境更改数据库（含数据）字符集的方法

对于已经包含了数据的库表，若要对字符集进行调整，就需要将数据先导出，然后更改数据库环境，更改建库和表的字符集之后，重新导入数据，这样才能实现相应的调整。

1、确保数据库不要更新，然后导出所有数据为SQL的文件。
2、针对导出的数据进行字符集替换（替换表和库），例如把GBK改为UTF8。
3、修改my.cnf配置文件，更改MySQL客户端及服务端的字符集，重启生效。
4、导入更改过新字符集的库表的数据，包括表结构语句，然后提供服务。
5、将操作系统、SSH客户端，以及程序更改为对应的新字符集。

更改字符集时，要将小的字符集集合更改为大的字符集集合，不然可能会丢失数据。