mysql数据库、表、列的字符集和校对规则

记一次工作中sql的经历.
sql简化下来大概是这样的:

 select a.id,b.id from A as a left join B as b on a.tenant_id = b.tenant_id 
1

就是这么一个简单的sql,在执行的时候报了一个错:

然后我就和大家一样,用错误信息去百度了一下,其实静下心来看错误提示还是能看出来的.大概意思说的是非法的混合collations用来做了=的操作.并且小括号里说了一个是utf8mb4_general_ci,另一个是utf8mb4_unicode_ci.
也就是说校验规则不相同的列是不能用来做比较的.这是为什么呢?什么又是校对规则呢?如果遇到了这种情况,该如何处理呢?

字符集

说校对规则之前就不得不讲到字符集,字符集是多个字符(英文字符,汉字字符,或者其他国家语言字符)的集合,字符集种类很多,每种包含的字符个数不同.
常见的字符集:
ASCII字符集:基于罗马字母表的一套字符集,采用1个字节的低7位表示字符,高位始终为0.
LATIN1字符集:相比较于ASCII,启用了高位,扩展了字符集的表示范围
GBK字符集:支持中文,字符有一字节编码和两字节编码方式.
UTF8字符集:Unicode字符集的一种,支持所有国家的文字字符,utf8采用1-4字节表示字符.

数据库、表和列上都可以直接设置对应的字符集,并且可以进行修改.
优先级:
如果都显示设置了,那么优先级顺序是:sql语句 > 列级别设置 > 表级别设置 > 哭级别设置 > 服务器级别设置
utf-8和utf-8mb4的区别:
utf-8是变化长度的编码,储存需要1-4个细节.
mysql的utf-8只存储最多3个字节.所以有些字符存不进去,像emoji表情等.
为了兼容4字节,MySQL在5.5.3之后新增加了utf8mb4的编码.

校验规则

是在字符集内用于字符比较和排序的一套规则.

以命名惯例:
1、以对应的字符集名称开头
2、以_ci(表示大小写不敏感)、_cs(表示大小写敏感)、_bin(表示按编码值比较)结尾.
例如:在字符序:“utf8-general-ci”下,字符a和A是等价的.
这也就解释了为什么不同的校验规则不能直接进行比较.

mysql字符集和校验规则的对应关系:
每个校验规则唯一对应一种字符集,但一个字符集可以对应多种校验规则,其中就有一个默认的.

当遇到校验规则不同的查询的时候,该怎么解决呢?

• DDL语句修改表结构
• 在sql上转换列的校验规则:select a.id,b.id from A as a left join B as b on a.tenant_id = b.tenant_id COLLATE utf8mb4_general_ci 使其保持一致.
  但是需要注意第二种方式,最好加上之后,自己用explain试一下,有没有导致索引失效,因为我从百度上看到说,需要被驱动的表的校验规则的优先级要高.
  我特地百度了一下校验规则的优先级,没有百度到,如果哪位大神知道,可以留言评论,互相学习.