mysq字符编码

目录

字符简介

常用字符集简介

Mysql中的字符集

字符集和比较字符集修改关联

mysql中字符集分类

客户端和服务器通信中的字符集

总结

---

字符简介

字符集：计算机中只能存储二进制数据，存储字符就需要建立字符与二进制数据的映射关系。将一个字符映射成一个二进制数据的过程也叫做编码，将一个二进制数据映射到一个字符的过程叫做解码。包含字符的范围和编码规则如 ：

比较规则：是比较两个字符大小的规则，二进制比较规则是简单，对于某一种字符集来说，比较 两个字符大小的规则可以制定出很多种。

常用字符集简介

林子大了什么鸟 都有，很不幸，世界很大，就像各国的语言，不同的人制定出了多种不不同的字符集，它们表示的字符范围和用到的编码规则可能都不一样，比较常用的字符集：

• ASCII字符集
  共收录128个字符，包括空格、标点符号、数字、大小写字母和一些不可见字符。由于总共才128个字符，所以可以使用1个字节来进行编码，我们看一些字符的编码方式:

• ISO 8859-1字符集
  共收录256个字符，是在ASCII字符集的基础上又扩充了128个西欧常用字符(包括德法两国的字母)，也可以使用1个字节来进行编码。这个 字符集也有一个别名latin1。

• GB2312字符集
  收录了汉字以及拉丁字母、希腊字母、日文平假名及片假名字母、俄语西里尔字母。其中收录汉字6763个，其他文字符号682个

• GBK字符集
  GBK字符集只是在收录字符范围上对GB2312字符集作了扩充，编码方式上兼容GB2312。

• utf8字符集
  收录地球上能想到的所有字符，而且还在不断扩充。这种字符集兼容ASCII字符集，采用变长编码方式，编码一个字符需要使用1~4个字 节

如果该字符在ASCII字符集中，则采用1字节编码，否则采用2字节编码。对于同一个字符，不同字符集也可能有不同的编码方式。

Mysql中的字符集

MySQL中的 utf8和 utf8mb4

正常utf8字符集表示一个字符需要使用1~4个字节，但是我们常用的一些字符使用1~3个字节就可以表示了。而在MySQL中字符集表示一 个字符所用最大字节长度在某些方面会影响系统的存储和性能，所以MySQL设计者做了特殊设定：

• utf8mb3:阉割过的utf8字符集，只使用1~3个字节表示字符。
• utf8mb4:正宗的utf8字符集，使用1~4个字节表示字符。

在MySQL中utf8是utf8mb3的别名，所以之后在MySQL中提到utf8就意味着使用1~3个字节来表示一个字符。

查看字符集

查看 myslq支持的字符集、默认总共41中。

mysql> SHOW CHARSET;       #查看支持的所有字符集

mysql>SHOW CHARSET  LIKE 'utf%';    #查看支持包含utf开头的字符集

查看比较字符集 

mysql> SHOW COLLATION ;       #查看支持的所有字符集

mysql>SHOW COLLATION  LIKE 'utf%';    #查看支持包含utf开头的字符集

比较规则的命名还挺有规律的，utf8_general_ci是一种通用的比较规则，utf8_polish_ci表示以波兰语的规则比较，不用过多记忆 ，了解即可，一般默认比较规则就能满足正常使用。

ai：不区分重音    as：区分重音    ci：不区分大小写    cs：区分大小写    bin：以二进制方式比较

字符集和比较字符集修改关联

由于字符集和比较规则是互相有联系的，如果我们只修改了字符集，比较规则也会跟着变化，如果只修改了比较规则，字符集也会跟着变化，具体规则如下：

• 只修改字符集，则比较规则将变为修改后的字符集默认的比较规则。
• 只修改比较规则，则字符集将变为修改后的比较规则对应的字符集。

不论哪个级别的字符集和比较规则，这两条规则都适用

mysql中字符集分类

主要有四个层次:服务器,数据库,表和列.字符集编码不仅影响数据存储,还影响客户端程序和数据库之间的交互。

服务器级别

MySQL提供了两个系统变量来表示服务器级别的字符集和比较规则

SHOW VARIABLES LIKE 'character_set_server';

SHOW VARIABLES LIKE 'collation_server';

root@localhost:mysql.sock  15:45:27 [mysql]>SHOW VARIABLES LIKE 'character_set_server';
+----------------------+-------+
| Variable_name        | Value |
+----------------------+-------+
| character_set_server | gbk   |
+----------------------+-------+
1 row in set (0.00 sec)

root@localhost:mysql.sock  15:55:13 [mysql]>SHOW VARIABLES LIKE 'collation_server';
+------------------+----------------+
| Variable_name    | Value          |
+------------------+----------------+
| collation_server | gbk_chinese_ci |
+------------------+----------------+
1 row in set (0.00 sec)

root@localhost:mysql.sock  15:55:27 [mysql]>

可以看到在我的计算机中服务器级别默认的字符集是gbk，默认的比较规则是gbk_general_ci。

修改服务器级别编码：

修改mysql的配置文件/etc/my.cnf，然后重启，当服务器启动的时候读取这个配置文件后这两个系统变量的值便修改了。

[server] 
character_set_server=utf8 
collation_server=utf8_general_ci 

数据库级别

查看数据库级别字符集和比较字符集 

root@localhost:mysql.sock  16:01:10 [mysql]>use nacos
Database changed
root@localhost:mysql.sock  16:01:16 [nacos]>SHOW VARIABLES LIKE 'character_set_database';
+------------------------+-------+
| Variable_name          | Value |
+------------------------+-------+
| character_set_database | utf8  |
+------------------------+-------+
1 row in set (0.00 sec)

root@localhost:mysql.sock  16:01:19 [nacos]> SHOW VARIABLES LIKE 'collation_database';
+--------------------+-----------------+
| Variable_name      | Value           |
+--------------------+-----------------+
| collation_database | utf8_general_ci |
+--------------------+-----------------+
1 row in set (0.00 sec)

root@localhost:mysql.sock  16:01:31 [nacos]>

character_set_database 和collation_database 这两个系统变量是只读的，我们不能通过修改这两个变量的值而改变当前数据库的字符集和比较规则。

如果创建数据库的时候不指定字符集和比较规则，将使用服务器级别的字符集和比较规则作为数据库的字符集和比较规则。

表级别

如果创建和修改表的语句中没有指明字符集和比较规则，将使用该表所在数据库的字符集和比较规则作为该表的字符集和比较规则

我们也可以在创建和修改表的时候指定表的字符集和比较规则

CREATE TABLE 表名 (列的信息)
    [[DEFAULT] CHARACTER SET 字符集名称]
    [COLLATE 比较规则名称]]

ALTER TABLE 表名
    [[DEFAULT] CHARACTER SET 字符集名称]
    [COLLATE 比较规则名称]

CREATE TABLE  user (
  id int(11),
  name varchar(255) 
) CHARACTER SET utf8 COLLATE utf8_general_ci;

列级别

如果在创建和修改的语句中没有指明字符集和比较规则，将使用该列所在表的字符集和比较规则作为该列的字符集和比较规则

CREATE TABLE 表名(
    列名 字符串类型 [CHARACTER SET 字符集名称] [COLLATE 比较规则名称],
    其他列...
);

ALTER TABLE 表名 MODIFY

列名 字符串类型 [CHARACTER SET 字符集名称] [COLLATE 比较规则名称];

mysql> ALTER TABLE t MODIFY 
col VARCHAR(10) CHARACTER SET gbk COLLATE gbk_chinese_ci;
Query OK, 0 rows affected (0.04 sec)
Records: 0  Duplicates: 0  Warnings: 0

各级别字符集和比较规则小结

我们介绍的这4个级别字符集和比较规则的联系如下：

• 如果创建或修改列时没有显式的指定字符集和比较规则，则该列默认用表的字符集和比较规则
• 如果创建或修改表时没有显式的指定字符集和比较规则，则该表默认用数据库的字符集和比较规则
• 如果创建或修改数据库时没有显式的指定字符集和比较规则，则该数据库默认用服务器的字符集和比较规则

客户端和服务器通信中的字符集

编码和解码使用的字符集不一致会导致中文乱码问题

MySQL中字符集的转换

我们知道从客户端发往服务器的请求本质上就是一个字符串，服务器向客户端返回的结果本质上也是一个字符串，而字符串其实是使用某种字 符集编码的二进制数据。这个字符串可不是使用一种字符集的编码方式一条道走到黑的，从发送请求到返回结果这个过程中伴随着多次字符集 的转换
character_set_client:服务器解码请求时使用的字符集

character_set_connection：服务器处理请求时会把请求字符串从character_set_client转为character_set_connection

character_set_results：服务器向客户端返回数据时使用的字符集

mysql> SHOW VARIABLES LIKE 'character_set_client';
mysql> SHOW VARIABLES LIKE 'character_set_connection';
mysql> SHOW VARIABLES LIKE 'character_set_results';

设置字符集 

客户端这时候一般需要指定utf8方式连接才能避免乱码。也就是传说总的set  names命令。事实上，set names 字符集 ；命令对应的是服务器端以下几个命令：

SET character_set_client = 字符集名;
SET character_set_connection = 字符集名;

SET character_set_results = 字符集名;

mysql> SET character_set_client = utf8;
mysql> SET character_set_results = utf8;
mysql> SET character_set_connection = utf8;

但这三个参数是不能写在配置文件my.cnf里的。只能通过set命令来动态修改。我们需要的是在配置文件里写好一劳永逸的办法。那么这时候，是否有在服务端解决问题的办法呢，可行的思路是在init_connect里设置。这个命令在每个普通用户连接上来的时候都会触发执行，可以在[mysqld]部分增加以下一行设置连接字符集：
在[mysqld]下添加：
　　init_connect = 'SET NAMES utf8'

总结

1.在[mysqld]下添加，控制客户端
　　default-character-set=utf8 
2.在[client]下添加，控制服务端
　　default-character-set=utf8

3、在[mysqld]下添加，控制客户端、连接、服务端
　　init_connect = 'SET NAMES utf8'
4、常用命令

#更改客户端、服务器、数据库字符集的命令
 mysql> set character_set_client=utf8;
 mysql> set character_set_connection=utf8;
 mysql> set character_set_database=utf8;
 mysql> set character_set_results=utf8;
 mysql> set character_set_server=utf8;
 mysql> set character_set_system=utf8;

 mysql> set collation_connection=utf8;
 mysql> set collation_database=utf8;
 mysql> set collation_server=utf8;

#查看字符集 
show variables like 'collation_%';
show variables like 'character_set_%';

#设置数据库字符集
alter database mini default character set = gb2312; 
create database mydb character set gb2312;

#修改单个表字符集
alter table pub_logs default character set = gb2312; 
alter table pub_logs convert to character set gb2312;

#生成批量更新字符集 
SELECT a.TABLE_TYPE,CONCAT('alter TABLE ',A.TABLE_NAME,' default character set = gb2312;')  FROM INFORMATION_SCHEMA.TABLES A
WHERE A.TABLE_SCHEMA='MINI'
AND a.TABLE_TYPE='BASE TABLE';

#生成批量更新列字符集
SELECT CONCAT(CONCAT(CONCAT('alter TABLE ',c.TABLE_NAME,' modify'),CONCAT(' ',C.COLUMN_NAME,' '),C.COLUMN_TYPE),' ',' character set gb2312 COLLATE gb2312_chinese_ci;') 
 AS CLOU
 FROM 
 INFORMATION_SCHEMA.COLUMNS C,INFORMATION_SCHEMA.TABLES A
 WHERE c.TABLE_SCHEMA='MINI'
 AND A.TABLE_NAME=c.TABLE_NAME
 AND A.TABLE_TYPE='BASE TABLE'
 AND c.DATA_TYPE='varchar' ;