← Back to list

mysql 字符集

Published on: | Views: 90

mysql有很多字符集，搞得头晕，整理一下。

字符集和比较集

字符集 charset/character set， 表示字符编码 比较集collaction 表示两个字符串比较时的算法

系统字符集

用于指定存储标识符的字符集，由变量character_set_system指定， 一直是utf8 。

连接字符集

连接字符集可以是session特定的。 客户端发送命令时，字符集由变量character_set_client指定。 服务器在收到命令后，会对字符集进行转换，转换为 character_set_connection指定的字符集。 服务器执行完命令得到查询结果后，会对字符集进行转换，转换为character_set_results指定的字符集。 查看当前连接字符集：

select @@character_set_client,@@character_set_connection,@@character_set_results,@@collation_connection;

服务器字符集

用于指定默认情况下的字符集，由变量character_set_server指定。

数据库字符集

每个数据库都有字符集和比较字符集，由变量character_set_database 和collation_database指定，如果没有指定，就会取character_set_server和collation_server的值。

数据库字符集的作用： - 执行CREATE TABLE命令（创建表），如果未指定字符集，那么就使用数据库字符集； - 执行LOAD DATA 命令（导入数据），如果未指定字符集，那么表示文件的字符集为数据库字符集； - 用于存储过程和函数的字符集。

查询数据库字符集：

use db_name;
SELECT @@character_set_database, @@collation_database;

指定数据库字符集：

CREATE DATABASE db_name
    [[DEFAULT] CHARACTER SET charset_name]
    [[DEFAULT] COLLATE collation_name]

ALTER DATABASE db_name
    [[DEFAULT] CHARACTER SET charset_name]
    [[DEFAULT] COLLATE collation_name]

表和列字符集

创建表和列时，可以指定字符集和比较集，如果未指定，则使用数据库字符集。

总结

系统字符集是固定的，不用理会。 连接字符集会影响查询结果，例如一台终端查询结果正常显示，另外一台查询显示乱码，那么可能这方面有问题。 服务器、数据库、表、列字符集用于指定数据存储的字符集，生效优先级为：列字符集 > 表字符集 > 数据库字符集 > 服务器数据集。

故障案例

某天，有一个表数据插不进去，错误为

uncategorized SQLException; SQL state [HY000]; error code [1366]; Incorrect string value: '\xF0\x9F\x91\xBF' for column 'nick_name'

应该是表情符无法插入，检查一下列的字符集：

select * from  information_schema.columns where table_name='user';

发现nick_name的字符集确实是utf8mb4。 那么问题可能出在连接字符集上面，查看一下，发现连接字符集也全部是utf8mb4，那怎么会出问题呢，奇怪。 考虑一下连接字符集是session范围的，所以在代码中执行查询， 发现这次连接字符集变成了utf8，因为character_set_client=utf8，导致了无法将utf8以外的字符发送给数据库，所以即使数据库列为utf8mb4也不能写入。 解决办法就是修改character_set_client为utf8mb4，我使用的连接库是mysql-connector， 查看文档找到设置方法： https://dev.mysql.com/doc/connector-j/8.0/en/connector-j-reference-charsets.html

For Connector/J 8.0.12 and earlier: In order to use the utf8mb4 character set for the connection, the server MUST be configured with character_set_server=utf8mb4; if that is not the case, when UTF-8 is used for characterEncoding in the connection string, it will map to the MySQL character set name utf8, which is an alias for utf8mb3.

所以要配置服务器的character_set_server为utf8mb4，修改/etc/mysql/mysql.conf.d/mysqld.cnf：

[mysqld]
character-set-server = utf8mb4
collation-server = utf8mb4_unicode_ci