MySQL character set、collation（整理

1 查看当前设置

Character: 字符集
Collation：在同一字符集内字符之间的比较规则、排序

collation查看：
SHOW VARIABLES LIKE 'collation_%';
+----------------------+--------------------+
| Variable_name        | Value              |
+----------------------+--------------------+
| collation_connection | utf8_general_ci    |
| collation_database   | utf8mb4_general_ci |
| collation_server     | utf8mb4_general_ci |
+----------------------+--------------------+

character set查看：
SHOW VARIABLES LIKE 'character_set_%';
+--------------------------+----------------------------------+
| Variable_name            | Value                            |
+--------------------------+----------------------------------+
| character_set_client     | utf8                             |
| character_set_connection | utf8                             |
| character_set_database   | utf8mb4                          |
| character_set_filesystem | binary                           |
| character_set_results    | utf8                             |
| character_set_server     | utf8mb4                          |
| character_set_system     | utf8                             |
| character_sets_dir       | /usr/local/mysql/share/charsets/ |
+--------------------------+----------------------------------+

2 设置

mysql 命令：

SET  Variable_name  = Value

1 服务器级

(/etc/mysql/my.cnf）里设置： 
[mysqld] 
    character_set_server=utf8 
    collation_server=utf8_general_ci 

2 数据库级

• 若没有显式设置，则自动使用服务器级的配置
• 显式设置，在创建库时指定

3 表级
查看：show create table �table_name;

• 没有显式设置，则自动使用数据库级的配置
• 显式设置，在创建表时指定

CREATE TABLE `b` (
  `id` int(11) unsigned NOT NULL AUTO_INCREMENT,
  `sid` int(11) unsigned NOT NULL DEFAULT '0',
  PRIMARY KEY (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

4 列级

• 若没有显式设置，则自动使用表级的配置
• 显式设置：在创建时指定

5 连接级别

# 服务端使用这个编码来理解客户端发来的
character_set_client;

# 连接层字符集 character_set_client 转 character_set_connection
show variables like 'character_set_connection' ; 

# 服务端使用这个编码回送结果集和错误信息
show variables like 'character_set_results'; 

3 常见问题

• 向默认字符集为utf8的数据表插入utf8编码的数据前没有设置连接字符集，查询时设置连接字符集为utf8

• 插入时根据MySQL服务器的默认设置，character_set_client、character_set_connection和character_set_results均为latin1
• 插入操作的数据将经过latin1=>latin1=>utf8的字符集转换过程，这一过程中每个插入的汉字都会从原始的3个字节变成6个字节保存

• 向默认字符集为latin1的数据表插入utf8编码的数据前设置了连接字符集为utf8

• 插入时根据连接字符集设置，character_set_client、character_set_connection和character_set_results均为utf8
• 插入数据将经过utf8=>utf8=>latin1的字符集转换，若原始数据中含有\u0000~\u00ff范围以外的Unicode字符，会因为无法在latin1字符集中表示而被转换为“?”(0×3F)符号，以后查询时不管连接字符集设置如何都无法恢复其内容了。转换过程如下图：

4 建议

• 建立数据库/表和进行数据库操作时尽量显式指出使用的字符集，而不是依赖于MySQL的默认设置，否则MySQL升级时可能带来很大困扰；
• 一般情况下将数据库和连接字符集都置为utf8、utf8mb4是较好的选择；