Mysql升级后字符编码引起的血泪教训

描述

现在大部分企业所使用的MySQL数据库相信都已经从5.7升级到了8，性能也得到了大幅度的提升

MySQL 8.0对于数据管理带来了很多改变，使得MySQL成为一个更强大、更灵活和更易于使用的数据库管理系统。

1. 
   
2. 
   

   MySQL 8.0提供了更好的JSON支持，包括更快的JSON函数和表达式，以及新的JSON数据类型和索引。

   
   
   
3. 
   

   MySQL 8.0引入了窗口函数，这些函数可以用来计算分析函数的结果并根据指定的排序规则进行分组。

   
   
   
4. 
   

   MySQL 8.0提供了更好的空间数据支持，包括新的空间数据类型和函数，例如ST_Distance_Sphere函数，它可以计算两个点之间的球面距离。

   
   
   
5. 
   

   MySQL 8.0提供了更好的安全性，包括更安全的默认配置、更严格的密码策略、更多的SSL/TLS选项等。

   
   
   
6. 
   

   MySQL 8.0提供了更好的性能，包括新的索引算法、更好的查询优化器、更好的并发控制等。

   
   
   

MySQL5.7

查看版本号

查看编码格式

从结果可以看出，MySQL8默认字符编码为utf8mb4

查看排序规则

从结果可以看出，MySQL8默认排序规则为utf8mb4_general_ci

总结

MySQL5.7 默认字符编码是utf8mb4，默认排序规则是utf8mb4_general_ci

MySQL8

查看版本号

查看编码格式

“character_set_client” 表示客户端字符集

“character_set_connection” 表示连接字符集

“character_set_server” 表示服务器字符集

从结果可以看出，MySQL8默认字符编码为utf8mb4

查看排序规则

从结果可以看出，MySQL8默认排序规则为utf8mb4_0900_ai_ci

总结

MySQL8 默认字符编码是utf8mb4，默认排序规则是utf8mb4_0900_ai_ci

utf8 与 utf8mb4 区别

1. 
   
2. 
   

   存储字符范围不同：

   
   
   • 
     
   • utf8 编码最多能存储 3 个字节的 Unicode 字符，支持的 Unicode 范围较窄，无法存储一些辅助平面字符（如 emoji 表情）。
     
   • utf8mb4 编码最多能存储 4 个字节的 Unicode 字符，支持更广泛的 Unicode 范围，包括了 utf8 所不支持的一些特殊字符和 emoji 表情等。
     
   
   
   
3. 
   

   存储空间不同：

   
   
   • 
     
   • utf8 编码时，字符长度可以是最多 3 个字节。
     
   • utf8mb4 编码时，字符长度可以是最多 4 个字节。
     
   
   
   
4. 
   

   对于存储 Emoji 和特殊字符的支持：

   
   
   • 
     
   • utf8mb4 能够存储和处理来自辅助平面的字符，包括emoji表情，这些字符需要使用 4 个字节来编码。而 utf8 不支持这些字符。
     
   
   
   

utf8mb4_general_ci 与 utf8mb4_0900_ai_ci 区别

1. 
   
2. 
   

   utf8mb4_general_ci：

   
   
   • 
     
   • 这是MySQL中较为通用的字符集和校对规则。
     
   • utf8mb4 是一种用于存储 Unicode 字符的编码方式，支持更广泛的字符范围，包括 emoji 等。
     
   • general_ci 是一种排序规则，对字符进行比较和排序时不区分大小写，对于大多数情况来说是足够通用的。
     
   
   
   
3. 
   

   utf8mb4_0900_ai_ci：

   
   
   • 
     
   • 这是MySQL 8.0.0 版本后引入的校对规则。
     
   • 0900 表示MySQL 8.0.0 版本。
     
   • ai_ci 是指采用 accent-insensitive 方式，即对于一些有重音符号的字符，排序时会忽略重音的存在。
     
   
   
   

主要区别在于排序规则的不同。utf8mb4_0900_ai_ci 在排序时会对重音符号进行忽略，所以某些含有重音符号的字符在排序时可能会与 utf8mb4_general_ci 有所不同。

索引不生效问题

表结构

CREATE TABLE `user` (

 `id` bigint NOT NULL COMMENT '主键',

 `username` varchar(50) NOT NULL DEFAULT '' COMMENT '名称',

 `password` varchar(50) NOT NULL DEFAULT '' COMMENT '密码',

 `store_id` bigint NOT NULL DEFAULT 0 COMMENT '门店id',

 `is_delete` int NOT NULL DEFAULT '0' COMMENT '是否删除',
PRIMARY KEY (`id`),

 KEY `idx_store_id` (`store_id`)

) ENGINE=InnoDB CHARSET=utf8mb4 COLLATE=utf8mb4_general_ci COMMENT='用户表';


CREATE TABLE `user_role` (

 `id` bigint NOT NULL COMMENT '主键',

 `user_id` bigint NOT NULL DEFAULT 0 COMMENT '用户id',

 `role_id` bigint NOT NULL DEFAULT 0 COMMENT '角色id',

 `is_delete` int NOT NULL DEFAULT '0' COMMENT '是否删除',
PRIMARY KEY (`id`),

 KEY `idx_user_id` (`user_id`),

 KEY `idx_role_id` (`role_id`)

) ENGINE=InnoDB CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci COMMENT='用户角色关系表';

查询

SELECT DISTINCT

	t1.id,

	t1.username 
FROM
user t1
JOIN user_role t2 ON t2.user_id = t1.id 
WHERE

	t1.is_delete = 0
and t2.is_delete = 0
and t1.store_id = 2 
AND t2.role_id NOT IN (9, 6)

执行计划

通过执行计划发现明明字段上加了索引，为什么索引没有生效

explain format = tree 命令

问题找到了

(convert(t2.user_id using utf8mb4) = t1.id))

在回头看看表结构

为什么会不一致呢？

mysql5.7 升级之前 两个表都是 CHARSET=utf8mb4 COLLATE=utf8mb4_general_ci

mysql5.7 升级到 mysql8 后，user_role 更新过表结构

修改表排序规则

ALTER TABLE user CHARACTER COLLATE = utf8mb4_0900_ai_ci;

再次查看执行计划

总结

开发一般都不太注意表结构的字符编码和排序规则，数据库升级一定要先统一字符编码和排序规则

查询的问题

由于先发布应用，后执行的脚步，没有通知测试所以没有生产验证，导致第二天一大早疯狂报警

一看就是两个表字段排序规则不一致导致的

只能修改表结构排序规则 快速解决

总结

升级MySQL是一个常见的操作，但在升级过程中可能会遇到各种问题。本文主要介绍排序规则不一致导致的问题，希望能对大家在升级MySQL时有所帮助。在进行任何升级操作之前，务必备份数据库，以防数据丢失。同时，建议定期对数据库进行性能优化，以提高系统的高可用。