一、背景知识

字符集COLLATE

1、字符集是一套符号和编码，COLLATE是在字符集内用于比较字符的一套规则。通常的字符集都是utf8mb4（8.0默认）。

2、在mysql中，字符类型的列，比如：char、text、varchar等类型的列，都需要有collate和mysql进行交互，告诉mysql这些列该如何进行排序。

因此，COLLATE和orderby、distinct、group by、having语句息息相关，同时也会影响where条件中大于小于等于的查询。

3、不同的COLLATE下会影响mysql的索引使用

4、utf8mb4编码的默认值为utf8mb4_general_ci

utf8mb4_bin：将字符串每个字符用二进制数据编译存储，其数据比较方法其实就是直接将所有字符看作二进制串，然后从最高位往最低位比对。所以很显然它是区分大小写的。

utf8mb4_general_ci：没有实现Unicode排序规则，在遇到某些特殊语言或者字符集，排序结果可能不一致。但是，在绝大多数情况下，这些特殊字符的顺序并不需要那么精确。

utf8_general_ci校对规则进行的比较速度很快，但是与使用 utf8mb4_unicode_ci的校对规则相比，比较正确性较差

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

5、总结：general_ci 更快，unicode_ci 更准确、utf8mb4_bin对字符大小写敏感。（补充：现在的CPU来说，它远远不足以成为考虑性能的因素，索引涉及、SQL设计才是。使用者更应该关心字符集与排序规则在db里需要统一。）

二、分析：

默认字符比较规则（utf8mb4_general_ci）是不区分大小写的（utf8mb4_unicode_ci也不区分）

2.1 ci即case insensitive，不区分大小写即大小写不敏感。"A"和"a"在排序和比较的时候是一视同仁。

selection * from test where cloumn="a"同样可以把cloumn为"A"的值选出来。对于mysql来说，'a'和‘A’没有区别

2.2 大小写敏感（case sensitivity）是指使用大写字母、小写字母造成不同效果的情况。

eg：使用单据号作为唯一键： 数据001A落表的时候会变成001a，因为mysql默认是不区分大小写的。

这样，当表里面有001a的时候 & 其是唯一键，再存001A就会导致唯一键冲突。

2.3 情景复现：

  • 表里面已经有name = "aaaa"的数据
  • name字段是唯一键
  • name为字符串，且为ci格式
  • 再次插入数据name = 'AAAA’时，就会报错。唯一键冲突
    复现

2.4 解决：utf8mb4_unicode_ci或utf8mb4_general_ci ==> utf8mb4_bin

  • 方式一：改变表的属性，区分表中字符串的列，使所有字符列都区分大小写
ALTER TABLE my_table CONVERT TO CHARACTER SET utf8mb4 COLLATE utf8mb4_bin;
CREATE TABLE `user` (
  `order_no` varchar(64) COLLATE utf8mb4_unicode_ci NOT NULL COMMENT '单号',
  UNIQ_KEY `order_no` (`order_no`) COMMENT '唯一索引'
) ENGINE=InnoDB AUTO_INCREMENT=1 DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci COMMENT=''
  
  这里的建表语句，使用的是COLLATE=utf8mb4_unicode_ci，这种方式创建的表中的字符串字段，都是不区分大小写的。

默认方式创建的，单据号字段：order_no：是不区分大小写的，即大小写最终都是小写，这样就会造成唯一键冲突。

  • 方式二：也可以针对某一字符的字段属性，utf8mb4_bin
alter table zebra_mybatis_test modify column `name` varchar(128) COLLATE utf8mb4_bin NOT NULL DEFAULT '' COMMENT '姓名';

2.5、sop

默认表的创建，还是使用：ENGINE=InnoDB AUTO_INCREMENT=1265 DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci COMMENT=‘xxxx’

但是，当业务需要某个字段，区分大小写。即大小写不同有不同的含义时，则需要将字段设置为大小写敏感，即utf8mb4_bin

补充：

更多的时候，还是要考虑是否要区分大小写，比如：用于存用户登陆的账号、密码。密码就必须大小写敏感。需要根据具体的业务确定