律所网站建设建议,网上商城网站系统,柯桥建设集团网站,php网站建设与维护一、背景知识
字符集COLLATE
1、字符集是一套符号和编码#xff0c;COLLATE是在字符集内用于比较字符的一套规则。通常的字符集都是utf8mb4#xff08;8.0默认#xff09;。
2、在mysql中#xff0c;字符类型的列#xff0c;比如#xff1a;char、text、varchar等类型…一、背景知识
字符集COLLATE
1、字符集是一套符号和编码COLLATE是在字符集内用于比较字符的一套规则。通常的字符集都是utf8mb48.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 cloumna同样可以把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 唯一索引
) ENGINEInnoDB AUTO_INCREMENT1 DEFAULT CHARSETutf8mb4 COLLATEutf8mb4_unicode_ci COMMENT这里的建表语句使用的是COLLATEutf8mb4_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
默认表的创建还是使用ENGINEInnoDB AUTO_INCREMENT1265 DEFAULT CHARSETutf8mb4 COLLATEutf8mb4_unicode_ci COMMENT‘xxxx’
但是当业务需要某个字段区分大小写。即大小写不同有不同的含义时则需要将字段设置为大小写敏感即utf8mb4_bin
补充
更多的时候还是要考虑是否要区分大小写比如用于存用户登陆的账号、密码。密码就必须大小写敏感。需要根据具体的业务确定
