MySQL字符集大小写不敏感导致的主键冲突问题记录

前言

数据入库的时候报了个主键冲突的error，很是纳闷于是乎开始排查摸索起来，发现是字符集导致字段值大小写不敏感导致的问题。记录一下加深映像

我们常用的MySQL的数据库创建数据库时可以指定它的字符集和排序规则如下：

CREATE DATABASE mydatabase CHARACTER SET utf8mb4 COLLATE utf8mb4_general_ci;

又或者如navicat创建时显示指定。平时我并没有太在意，只知道MySQL的utf8是个假的utf8 ，mysql中的utf8最多只能支持3bytes长度的字符编码，对于一些需要占据4bytes的文字，mysql的utf8就不支持了，要使用utf8mb4才行。对于排序规则我一般都用 utf8mb4_general_ci 脑子里只知道它对大小写不敏感，此次遇到的问题就和排序规则有关。

问题复原

（一）数据库

CREATE DATABASE devstore CHARACTER SET utf8mb4 COLLATE utf8mb4_general_ci;

（二）表

CREATE TABLE `t_main_area` (
  `id` varchar(128) COLLATE utf8mb4_general_ci NOT NULL,
  `alise_name` varchar(255) COLLATE utf8mb4_general_ci DEFAULT NULL,
  `bpa_id` varchar(255) COLLATE utf8mb4_general_ci DEFAULT NULL,
  `d5000id` varchar(255) COLLATE utf8mb4_general_ci DEFAULT NULL,
  `name` varchar(255) COLLATE utf8mb4_general_ci DEFAULT NULL,
  `rdfid` varchar(255) COLLATE utf8mb4_general_ci DEFAULT NULL,
  `short_name` varchar(255) COLLATE utf8mb4_general_ci DEFAULT NULL,
  `systemd5000id` varchar(255) COLLATE utf8mb4_general_ci DEFAULT NULL,
  `systemid` varchar(255) COLLATE utf8mb4_general_ci DEFAULT NULL,
  `up_level_zone_id` varchar(255) COLLATE utf8mb4_general_ci DEFAULT NULL,
  PRIMARY KEY (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_general_ci;

（三）插入语句

insert into t_main_area (id) VALUES ('Zg');
insert into t_main_area (id) VALUES ('ZG');

执行这2条语句就会报主键冲突的错误如下

（四） 解决

首先我们要知道：表字段中的 COLLATE(排序规则) 会影响到ORDER BY语句的顺序，会影响到WHERE条件中大于小于号筛选出来的结果，会影响**DISTINCT、GROUP BY、HAVING**语句的查询结果。另外，MySQL建索引的时候，如果索引列是字符类型，也会影响索引创建，只不过这种影响我们感知不到。总之，凡是涉及到字符类型比较或排序的地方，都会和COLLATE有关。

很多COLLATE都带有_ci字样，这是Case Insensitive的缩写，即大小写无关也就是说"A"和"a"在排序和比较的时候是一视同仁的。selection * from table1 where field1="a"同样可以把field1为"A"的值选出来。与此同时，对于那些_cs后缀的COLLATE，则是Case Sensitive，即大小写敏感的。

常见的使用排序规则有utf8mb4_0900_ai_ci,utf8mb4_0900_ai_ci, utf8mb4_bin 前2者_ci 为后缀即大小写不敏感，utf8mb4_bin是二进制的对比大小写敏感。需要注意 从MySQL 8.0开始，默认的CHARSET是 utf8bmb4 默认的COLLATE是 utf8mb4_0900_ai_ci

综上所述，我此次遇到的问题就是字段的排序规则问题导致，只需要修改下表的字段的 COLLATE即解决。

执行以下sql 修改表的字段排序规则：

alter table t_main_area MODIFY COLUMN id varchar(128) CHARACTER SET utf8mb4 COLLATE utf8mb4_bin not null ;

参考资料

• MySQL 之 COLLATE 关键字