Appearance

字符集、排序规则与时区 ​

乱码、唯一键冲突和跨库 Join 报错，很多不是“数据坏了”，而是字符集或排序规则在连接、库、表、列之间不一致。

三个概念 ​

  • 字符集决定字符如何编码，例如 utf8mb4。
  • 排序规则决定字符串如何比较和排序，例如是否区分大小写、重音。
  • 连接字符集决定客户端发送的字节如何被服务端解释。

MySQL 历史上的 utf8 最多三字节，不是完整 UTF-8；现代系统通常使用 utf8mb4。

生效层级 ​

text
服务器默认 → 数据库默认 → 表默认 → 列定义
客户端连接 → character_set_client / connection / results

查看当前设置：

sql
show variables like 'character_set%';
show variables like 'collation%';
show create database app_db;
show create table users;

建库建表示例：

sql
create database app_db
  character set utf8mb4
  collate utf8mb4_0900_ai_ci;

create table users (
  id bigint primary key auto_increment,
  username varchar(64) character set utf8mb4 collate utf8mb4_0900_ai_ci not null,
  unique key uk_username (username)
) engine = InnoDB default character set utf8mb4 collate utf8mb4_0900_ai_ci;

排序规则命名怎么读 ​

以 utf8mb4_0900_ai_ci 为例：

  • utf8mb4：字符集。
  • 0900：基于 Unicode 9.0 规则。
  • ai：accent insensitive，不区分重音。
  • ci：case insensitive，不区分大小写。
  • as、cs 则常表示区分重音、区分大小写。
  • bin 或二进制规则按编码值比较，语义更严格。

如果用户名唯一索引使用不区分大小写的规则，Tom 与 tom 可能被视为相同；业务要求区分大小写时，应明确选择规则，而不是在应用层猜测。

为什么会出现 Illegal mix of collations ​

两列使用不同且无法自动协调的排序规则进行比较或 Join。排查：

sql
select table_schema, table_name, column_name,
       character_set_name, collation_name
from information_schema.columns
where table_schema = 'app_db'
  and data_type in ('char', 'varchar', 'text');

临时 COLLATE 可以救急，但会增加计算并可能影响索引。长期方案是统一模型和迁移数据。

字符集迁移为什么有风险 ​

sql
alter table users
  convert to character set utf8mb4
  collate utf8mb4_0900_ai_ci;

执行前检查：

  1. 是否重建整表、占用额外磁盘并长时间运行。
  2. 索引字节长度是否超限，排序语义是否改变。
  3. 唯一索引下原本不同的值是否会变成相同。
  4. 客户端驱动、连接串和导入导出编码是否一致。
  5. 大表是否需要在线变更工具、灰度和回滚方案。

时间类型与时区 ​

类型语义重点常见用途
date日历日期生日、结算日
time时间或时长，注意范围语义营业时间、持续时间
datetime保存给定日期时间，不自动按会话时区转换本地预约时间、业务日历时间
timestamp存储时间点并在读写时按会话时区转换，范围受版本定义限制创建时间、更新时间

查看设置：

sql
select @@system_time_zone, @@global.time_zone, @@session.time_zone;

生产建议：

  • API 传输使用带时区或 UTC 的明确格式。
  • Java 使用现代时间类型，区分 Instant、LocalDateTime、ZonedDateTime。
  • 不要同时依赖操作系统、JVM、连接池和数据库的隐式默认时区。
  • 夏令时地区的“每天 02:30”可能不存在或重复，调度与结算逻辑必须测试。

高频面试题 ​

utf8 与 utf8mb4 有什么区别 ​

MySQL 的历史 utf8 最多存三字节字符，无法覆盖部分 Emoji 和 Unicode 补充字符；utf8mb4 支持完整四字节 UTF-8。新系统通常选 utf8mb4，迁移旧系统要检查索引长度、数据转换和排序规则变化。

为什么字符串索引比较结果与 Java 不一致 ​

数据库比较由列的 collation 决定，Java 比较由具体 API 和 Locale 决定。大小写、重音、尾随空格和规范化形式都可能不同。唯一性规则应该由数据库约束明确保证，并让应用使用一致语义。