mysql指定collation_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是较好的选择；