Post

Mysql 字符集整理

Posted
By Ryan
14 min read
Mysql 字符集整理

结论：

  1. 当连接和字段使用不同的utf8和utf8mb4字符集时，会出现隐式转换并降低性能，并可能导致索引失效或乱码。如果需要使用utf8mb4字符集，请统一使用该字符集，并统一排序规则。
  2. 根据业务需求选择合适的排序规则：对于大小写敏感的情况，使用bin排序规则；对于大小写不敏感的情况，使用general_ci排序规则。
  3. 字符集转换会锁定表，并根据表中字符串数据量的大小来决定处理时间，可能会耗时较长。
  4. 转换字符集可能会增加字节占用，请确保转换后的字段类型长度足够容纳数据。
  5. 通常建议为表设置字符集，而字段则默认继承表的字符集，从而实现统一字符集的目的（标准SQL只有表和字段两个级别）。

阅读以下链接获取更多信息：

字符集和排序规则

字符集（charset）：字符的编码规则，如utf8、latin1。混用不同字符集可能导致乱码。

排序规则（collation）：用于字符集内比较字符串的规则。由三部分组成：{charset}_{lang}_{suffixes}。

lang

  • bin：基于字符集的二进制数值比较，没有其他后缀。
  • general：简化的排序规则，采用快捷方式以提高速度，准确性较差（utf8/utf8mb4默认）。
  • unicode：可以在多种语言中准确排序，但性能较general低。支持扩展，将一个字符视为多个字符组合。

suffix

  • _ai：不区分重音，仅部分字符集支持（unicode不支持）。
  • _as：区分重音，仅部分字符集支持（unicode不支持）。
  • _ci：不区分大小写。
  • _cs：区分大小写，一般包含区分重音，仅部分字符集支持（unicode不支持）。
  • _bin：基于字符集的二进制数值比较，区分大小写和重音。

MySQL中的字符集配置

MySQL中可以为服务器、数据库、表、字符串字段（char、varchar、text）以及连接设置字符集和排序规则。

  • 服务器：编译时或启动时通过配置文件指定，默认为系统默认字符集。客户端连接未指定字符集时使用服务器默认字符集（MySQL特有）。
  • 数据库：创建时指定字符集，未指定则使用服务器默认字符集。LOAD DATA语句和存储过程未指定字符集时使用数据库默认字符集（MySQL特有）。
  • 表：创建时指定字符集，未指定则使用所属数据库的字符集。存储过程涉及字符集比较时会使用表的字符集（SQL标准）。
  • 字段：创建时指定字符集，未指定则使用所属表的字符集。存储数据时使用字段的字符编码，进行字符集比较时使用字段的排序规则（SQL标准）。
  • 连接：客户端在建立连接时或连接后可指定字符集。服务器根据客户端指定的字符集接收数据，并将按指定的字符集返回数据给客户端。

通过SHOW CHARACTER SET命令可以查看支持的字符集及其默认排序规则。

各级字符集和排序规则策略
  • 两个都指定了则使用指定的
  • 只指定字符集，则使用指定字符集的默认排序规则：utf8mb4 -> utf8mb4, utf8mb4_general_ci
  • 只指定排序规则，则使用排序规则关联的字符集：utf8mb4_general_ci -> utf8mb4, utf8mb4_general_ci
  • 都没有指定，使用上级的字符集、排序规则

使用ALTER TABLE转换字符集时需注意兼容性，如utf8mb4是utf8的超集，则utf8mb4转utf8会丢数据，若编码格式不同会乱码

在数据库连接中指定字符集
1
2
3

1. 在jdbc连接中添加参数即可指定字符集 ` ?useUnicode=true&characterEncoding=utf8mb4`
2. 各数据库连接池库支持配置字符集，在建立jdbc连接后会发送` SET NAMES `指令指定字符集
3. MySQL服务端的my.cnf中设置如下内容（字符集和排序规则按需修改）

1
2
3
4
5
6
7

[client]
default-character-set=utf8mb4
[mysql]
default-character-set=utf8mb4
[mysqld]
character-set-server=utf8mb4
collation-server=utf8mb4_bin
查看session字符集
1
2

SHOW SESSION VARIABLES LIKE 'character\_set\_%';
SHOW SESSION VARIABLES LIKE 'collation\_%';

  • Character_set_client 客户端发送语句的字符集

  • Character_set_connection 服务器从客户端接收语句后将语句转换后的字符集

  • Character_set_results 服务器将查询结果返回给客户端的字符集

字符集冲突解决策略

当出现不同的排序规则（字符集）时，MySQL会尝试转换其中一种排序规则（字符集）来解决冲突。

字符集冲突解决策略

  • 使用具有最低强制转换值（coercibility value）的排序规则。

  • 如果两者的强制转换值相同：

    • 若两者都是Unicode或都不是Unicode，则报错。

    • 若一方是Unicode字符集，另一方是非Unicode字符集，则以Unicode字符集为准，并对非Unicode一方进行自动的字符集转换。例如，以下语句不会报错：

      1

      SELECT CONCAT(utf8_column, latin1_column) FROM t1;

      它返回的结果具有utf8字符集和与utf8_column相同的排序规则。在连接之前，latin1_column的值会自动转换为utf8。

    • 若操作中混合了具有_bin排序规则和具有_ci或_cs排序规则的字段，则将使用_bin排序规则进行比较。

各规则的coercibility value
  • An explicit COLLATE clause has a coercibility of 0 (not coercible at all).
  • The concatenation of two strings with different collations has a coercibility of 1.
  • The collation of a column or a stored routine parameter or local variable has a coercibility of 2.
  • A “system constant” (the string returned by functions such as USER() or VERSION()) has a coercibility of 3.
  • The collation of a literal has a coercibility of 4.
  • The collation of a numeric or temporal value has a coercibility of 5.
  • NULL or an expression that is derived from NULL has a coercibility of 6.

字符集和排序规则的注意事项

隐式转换和性能影响

当连接和字段使用不同的字符集时，MySQL会自动进行隐式转换以解决冲突，这种转换可能会导致性能下降。

例如，如果一个表中的字段使用utf8mb4字符集，而另一个表中的字段使用utf8字符集，当这两个表进行JOIN操作时，MySQL会自动将utf8字符集的字段转换为utf8mb4字符集，可能会导致性能问题。

为了避免隐式转换和提高性能，建议在整个数据库中统一使用相同的字符集和排序规则。

选择合适的排序规则

在选择排序规则时，要根据具体业务需求考虑大小写敏感性。

  • 对于大小写敏感的情况，使用基于字符集的二进制排序规则（例如，utf8_bin，utf8mb4_bin）。这样可以确保准确的大小写比较和排序。
  • 对于大小写不敏感的情况，使用基于字符集的不区分大小写排序规则（例如，utf8_general_ci，utf8mb4_general_ci）。这样可以简化比较和排序的过程。

如何从utf8转换到utf8mb4

MySQL官方文档：unicode转换

字符集转换注意事项

在某些情况下，可能需要将数据从一个字符集转换为另一个字符集。这时需要注意以下注意事项：

  • 字符集转换会锁定表，在转换过程中可能会导致其他查询被阻塞。
  • 转换字符集的时间取决于表中字符串数据的大小，较大的表可能需要较长的时间。
  • 在转换字符集之前，请确保备份数据以防止意外情况发生。
  • 转换字符集可能会增加存储空间的需求，特别是当将数据从单字节字符集（如latin1）转换为多字节字符集（如utf8mb4）时。请确保转换后的字段类型长度足够容纳数据。

文本字段长度限制

由于char、varchar、text等指定的是字段的字符数(MySQL5+)，而每种类型最长字节数有限制，所以需要考虑转换后字段长度是否会超出最大字节数

type max len(byte) utf8 len utf8mb4 len remark
char 255 255 255  
varchar 65533 21,844   255byte内1个byte标记长度，超过255使用2个字节标记长度
binary 255      
varbinary 65533     255byte内1个byte标记长度，超过2552个字节标记长度
tinytext 255      
text 65535      
mediumtext 16777215      
longtext 4294967295      

所有文本类型若是可为空则再减少1byte用于存储是否为空

索引同理，innodb最大索引长度为768字节，所以转utfmb4后最大索引长度为191字符

转换操作流程

  1. 修改字段的字符集或排序规则会锁表，锁表时间根据表中记录的字符数量而定，时间较长，谨慎操作，避免业务期间操作
  2. 修改字符集前需确认所有表的字符长度不会超出文本类型的最大字节数，如果可能超出的需要修改字段类型
  3. 执行以下sql：需将 “数据库名” 改为对应数据库，排序规则 utf8mb4_bin 根据需要修改
1
2
3
4
5
6

SELECT
	CONCAT( 'ALTER TABLE ', table_name, ' CONVERT TO CHARACTER SET utf8mb4 COLLATE utf8mb4_bin;' ) AS alterStatement 
FROM
	information_schema.TABLES 
WHERE
	table_schema = '数据库名';

​ 该脚本将为每一个表生成一条修改语句，并将其作为alterStatement返回

  1. 将生成的修改语句复制到数据库中执行，这些语句将逐个修改每个表的字符集
This post is licensed under CC BY 4.0 by the author.