JZTXT

mysql设置字段的排序规则对大小写敏感

发布时间 2023-05-30 11:55:35作者: FightingDeng2022

在开发中遇到一个问题：在插入一张表中提示主键冲突了，对数据分析了很久，没有发现问题。后面发现是数据库设计的时候设定的排序规则指定的是 COLLATE=utf8_general_ci,而不是用 COLLATE=utf8_bin,这两个规则的区别是什么呢？

  1. utf8_general_ci：这个排序规则是不区分大小写的，也就是说，在比较和排序字符串时，不考虑字符的大小写差异。例如，"A"和"a"会被认为是相同的。
  2. utf8_bin：这个排序规则是区分大小写的，也就是说，在比较和排序字符串时，会考虑字符的大小写差异。例如，"A"和"a"会被认为是不同的。它直接按照字符的二进制值进行比较和排序，不考虑语言特定的规则。
    实际测试一下：
    先建立一张指定排序规则为 utf8_general_ci 的test表：
    CREATE TABLE test (
    RESOURCE_ID bigint(20) NOT NULL COMMENT '资源ID',
    RESOURCE_NAME varchar(128) NOT NULL COMMENT '资源名称',
    CHANGED_TIME varchar(20) NOT NULL COMMENT '数据变化时间（insert和update)',
    TEACHER_NAME varchar(128) NOT NULL COMMENT '老师名称',
    STUDENT_NAME varchar(128) NOT NULL COMMENT '学生名称',
    UNIQUE KEY RESOURCE_ID (RESOURCE_ID,TEACHER_NAME,STUDENT_NAME)
    ) ENGINE=InnoDB DEFAULT CHARSET=UTF8 COLLATE=utf8_general_ci;

插入数据
INSERT INTO test (RESOURCE_ID, RESOURCE_NAME, CHANGED_TIME, TEACHER_NAME, STUDENT_NAME) VALUES(1, 'dd-dd', '2022-12-15 16:49:25', 'dd', 'zz');
再次插入一个大写的 DD 老师名称
INSERT INTO test (RESOURCE_ID, RESOURCE_NAME, CHANGED_TIME, TEACHER_NAME, STUDENT_NAME) VALUES(1, 'dd-dd', '2022-12-15 16:49:25', 'DD', 'zz');
此时就会提示报错,主键冲突，SQL 错误 [1062] [23000]: Duplicate entry '1-DD-zz' for key 'RESOURCE_ID'
修改 TEACHER_NAME 的字段编码为 utf8_bin，执行sql： alter table test modify TEACHER_NAME varchar(128) collate utf8_bin not null COMMENT '老师名称';
再次执行插入sql：
INSERT INTO test (RESOURCE_ID, RESOURCE_NAME, CHANGED_TIME, TEACHER_NAME, STUDENT_NAME) VALUES(1, 'dd-dd', '2022-12-15 16:49:25', 'DD', 'zz');
数据写入成功，结果如下：