不可忽视的MySQL字符集

2021-03-30 15:59:31 浏览数 (1)

墨墨导读：字符集是一组符号和编码。collation是一组用于比较字符集中的字符的规则。

MySQL的字符集从latin1经过utf8 到utf8mb4 ，算是经历曲折的路线。特别是从使用一个字符集变更另一个字符集时，实践当中都非常无奈,不是没办法，而是麻烦。到了MySQL8.0多出了一个字符集utf8mb4_0900_*的字符集，有必要了解一下。

1. 字符集基础

先了解下MySQL字符集都有哪些地方使用，进入数据库VARIABLES参数一目了然：

代码语言：javascript复制
mysql> SHOW VARIABLES  WHERE variable_name LIKE '%character%'  OR  variable_name  LIKE '%collation%'  ;
 ------------------------------- -------------------------------------- 
| Variable_name                 | Value                                |
 ------------------------------- -------------------------------------- 
| character_set_client          | utf8mb4                              |
| character_set_connection      | utf8mb4                              |
| character_set_database        | utf8mb4                              |
| character_set_filesystem      | binary                               |
| character_set_results         | utf8mb4                              |
| character_set_server          | utf8mb4                              |
| character_set_system          | utf8                                 |
| character_sets_dir            | /opt/idc/mysql8.0.23/share/charsets/ |
| collation_connection          | utf8mb4_bin                          |
| collation_database            | utf8mb4_bin                          |
| collation_server              | utf8mb4_bin                          |
| default_collation_for_utf8mb4 | utf8mb4_0900_ai_ci                   |
 ------------------------------- --------------------------------------
代码语言：javascript复制

从上面client，connection，database，results层层环节扣着，任何一个环节的字符集不兼容都会出现乱码问题。

MySQL8.0 utf8mb4 Unicode字符集一个新的通用排序归类，被命名为utf8mb4_0900_ai_ci。MySQL 8.0默认的是utf8mb4_0900_ai_ci，属于 utf8mb4_unicode_ci中的一种。

这些字段每个意义，代表着什么含义：

  • uft8mb4 表示用 UTF-8 编码方案，每个字符最多占4个字节。 utf8mb3也是Unicode字符集的UTF-8编码，每个字符使用一到三个字节。(utf8: utf8mb3的别名)
  • 0900 就是Unicode 校对算法版本。（Unicode归类算法是用于比较符合Unicode标准要求的两个Unicode字符串的方法）。
  • ai指的是口音不敏感。也就是说，排序时e，è，é，ê和ë之间没有区别，不区分重音。
  • ci表示不区分大小写。排序时p和P之间没有区别。

再了解一下一些场景下 utf8 和 utf8mb4 的问题点：

utf8编码最多支持3字节的数据，而emoji表情符， 偏生字是4个字节的utf8无法存储的，致辞延伸出utf8mb4字符集解决这个问题。

日常常用的字符集：

  • utf8mb4_bin：将字符串每个字符用二进制数据编译存储，区分大小写，而且可以存二进制的内
  • utf8mb4_general_ci：ci即case insensitive，不区分大小写。没有实现Unicode排序规则，在遇到某些特殊语言或字符集，排序结果可能不一致。但在绝大多数情况下，这些特殊字符的顺序并不需要那么精确。

utf8mb4_unicode_ci：是基于标准的Unicode来排序和比较，能够在各种语言之间精确排序，Unicode排序规则为了能够处理特殊字符的情况，实现了略微复杂的排序算法。

2. 字符集配置

mysql字符集如何设置，更改操作： 1)my.cnf配置文件信息，建议初始化时就设置好。 [mysqld] character-set-server = utf8mb4 collation-server = utf8mb4_unicode_ci character-set-client-handshake = FALSE #此处是忽略客户端的字符集，使用服务器的设置

2)init_connect=‘SET NAMES utf8mb4’ #服务器为每个连接的客户端执行的字符串,对于一些超级管理源就不生效的

3)字符集变更，包含库，表，column的变更。都可以完全的拥有自己的字符集。

代码语言：javascript复制
##更改DATABASE 
ALTER DATABASE `db1` DEFAULT CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci 

##更改TABLE 
ALTER TABLE `t1` DEFAULT CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci 

##更改column字段 
ALTER TABLE `t1`  modify `name` varchar(80) CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci COMMENT '昵称';
代码语言：javascript复制

4）日常字符集检查工作：

代码语言：javascript复制
SELECT b.SCHEMA_NAME, b.DEFAULT_CHARACTER_SET_NAME, b.DEFAULT_COLLATION_NAME ,a.TABLE_NAME,
a.TABLE_COLLATION 
FROM  information_schema.SCHEMATA b   left join information_schema.TABLES   a 
on  b.SCHEMA_NAME =a. TABLE_SCHEMA 
WHERE  b.SCHEMA_NAME not in ('information_schema' ,'mysql','performance_schema', 'sys') 
ORDER BY TABLE_SCHEMA,TABLE_NAME ;
代码语言：javascript复制

通过多方便设置，更改操作，检查。字符集不再是隐藏问题。

3. 字符集对于数据库的影响

字符集对整个数据库影响面还是比较可观的。库更改 对于原先存在的表字段 都不影响 依次类推。所以数据库>表>字段 都可以单独设置字符集。

常见问题1：有索引 没有走 因为进行了 字符集隐式转换
常见问题2：在尾随空格方面不同

字符串值(CHAR、VARCHAR和TEXT)的比较与其他排序规则在尾随空格方面不同。For example, ‘a’ and 'a ’ 作为不同的字符串比较，而不是相同的字符串。

对于字符集排序来说，字符串末尾的空格也有对应的处理。

代码语言：javascript复制
mysql> SELECT COLLATION_NAME, PAD_ATTRIBUTE  FROM INFORMATION_SCHEMA.COLLATIONS  
WHERE CHARACTER_SET_NAME = 'utf8mb4';
 ---------------------------- --------------- 
| COLLATION_NAME             | PAD_ATTRIBUTE |
 ---------------------------- --------------- 
| utf8mb4_general_ci         | PAD SPACE     |
| utf8mb4_bin                | PAD SPACE     |
| utf8mb4_unicode_ci         | PAD SPACE     |
| utf8mb4_icelandic_ci       | PAD SPACE     |
| utf8mb4_latvian_ci         | PAD SPACE     |
| .........
| utf8mb4_0900_ai_ci         | NO PAD        |
| utf8mb4_de_pb_0900_ai_ci   | NO PAD        |
| utf8mb4_0900_bin           | NO PAD        |
 ---------------------------- ---------------
代码语言：javascript复制

注意：在选择使用utf8mb4_0900 字符集之后空格 就需要处理。

常见问题3：对于数据的大小写敏感

除了lower_case_table_names之外，怎样有效使用大小写字符集设置，采用ut8mb4_bin字符集 既可，查询和数据插入解决。

常见问题4：表情符，偏生字

常见错误代码:1366 Incorrect string value: ‘xF0x9Fx99x82’ for column ‘name’ at row 2 指定Utf8mb4字符集，再配合character-set-client-handshake属性

备注：对于jdbc来说没有utf8mb4这样的字符集说法。

4. 总结

从初期安装开始，就需要配置好字符集，8.0版本的utf8mb4_0900目前为止也没有类似的bug之类的，但还需要谨慎使用。选择utf8mb4_unicode_ci即可。

一件件小事，积累起来就是变成大事。

作者 崔虎龙：云和恩墨MySQL技术顾问，长期服务于金融、游戏、物流等行业的数据中心，设计数据存储架构，并熟悉数据中心运营管理的流程及规范，自动化运维等。擅长MySQL、Redis、MongoDB数据库高可用设计和运维故障处理、备份恢复、升级迁移、性能优化。自学通过了MySQL OCP 5.6和MySQL OCP 5.7认证。2年多开发经验，10年数据库运维工作经验，其中专职做MySQL工作8年；曾经担任过项目经理、数据库经理、数据仓库架构师、MySQL技术专家、DBA等职务；涉及行业：金融（银行、理财）、物流、游戏、医疗、重工业等。

墨天轮原文链接：https://www.modb.pro/db/45313（复制到浏览器中打开或者点击“阅读原文”立即查看）

云数据库SQLServer unicode 数据库 sql 编程算法

0 人点赞