MYSQL collation 选好还能换吗

Collation 主要的作用是什么，排序。 数据库中的字符众多，而在这里很多的查询中都对这些符号进行一些比对的工作，如 A = a , B > BA , c < v 等等在查询中进行的条件输入的工作，而字符和字符之间如何进行比对，这个就全部依靠我们的collation 了，如我们规定了 A = 0 B = 1 则， B > A 是成立的，所有collation是一套字符的编码集合，collation会影响到order by的语句顺序，会影响到where 条件比对后的结果，同时也会影响distinct, group by , having 等语句查询的结果，不光如此，还会影响字符型的字段建立索引后的顺序等。

以下我们以 MYSQL 8.030版本作为操作的对象

show character set;

请注意charset 与 collation 之间的对应关系（默认值）

其中utf8mb4中就有众多的collation可以被支持

基于以上的问题，我们已经了解到collation的重要性，他是一个规则，满足数据库中表的数据进行比较和排序的重要标记属性。这些设定与字母大小写是否敏感或者一些特殊国家的语言的重音符号等都有关系。

下面我们带着几个问题，来看MYSQL 的COLLATION 的问题

问题1 ，怎么能让我的数据库在比对英文大小写的时候，能产生差异

create table change_letter_i (id int primary key,letters varchar(20)) engine=innodb default charset=utf8mb4 collate=utf8mb4_0900_ai_ci; create table change_letter_c (id int primary key,letters varchar(20))engine=innodb default charset=utf8mb4 collate=utf8mb4_0900_as_cs;

从下图我们可以清晰的分析出，两个表在使用了不同的collation 后的在对比字符的比对情况

在我们使用了 utf8mb4_0900_ai_ci 后，我们的字符比对中，大小写英文是等同的，而在使用了utf8mb4_0900_as_cs 后，我们大小写之间的比对是敏感的。

问题 2 在MYSQL 中两个不同 collation的表对比字符会产生什么结果？

从下面的截图可以清晰的看到，两个同样的字符集都是 utf8mb4的情况下，但是他们的collation 是不同的情况下，是无法进行比对的。

mysql> select * from change_letter_c as c inner join change_letter_i as i on c.letters = i.letters; ERROR 1267 (HY000): Illegal mix of collations (utf8mb4_0900_as_cs,IMPLICIT) and (utf8mb4_0900_ai_ci,IMPLICIT) for operation '='

两个表虽然字符集相同，但是collation不同，在这样的情况下，两个表是不能关联查询的，如果查询会报错误提示。

问题 3 collation可以更换吗？

这是一个好的问题，因为collation的是一个在不同层级都存在的部分，在问可以不可以更换的前提条件是，你要更换哪个层次的collation

这里我们的collation 分为以下几个等级

1 实例级别

2 库级别 3 表级别 4 列级别 5 SQL 级别 那么我们带着以上的层次的问题，来对这些collation进行更替

1 确认当前的数据库服务器的instance 中的collation

show variables like 'collation_connection';

set collation_connection = 'utf8mb4_0900_as_cs';

这里可以在配置文件或者动态的方式来对数据库实例来进行设置，但是在其他的层级都设置了自己的collation的情况下，这个collation 是不起作用的。

2 库级别

在MYSQL的数据库级别中，是可以对数据库本身进行collation的设置的 create database test default character set utf8mb4 collate utf8mb4_0900_ai_ci;

在表级别没有设置默认的collation的情况下，则按照库的级别来进行collation的设置。

3 表级别

上面的实验我们做过了，略过

4 列级别

这个列级别的实用性，仅仅次于表级别

举例

我们针对不同的列设置不同的collation

上面的例子给我们一个很好的解释，collation 是可以随意设定的，级别越小越具有自己对自己管辖的部分具有collation的决定权。

上面的列子也说明在一个表中，如果有不同的对于大小写敏感度的不同需要，可以一个表中的不同字段具有不同的collation 。

但是需要注意的，如果有多表连接，则对应的collation必须一致。

5 SQL 级别

我们来看看SQL的级别的 COLLATION到底是要做什么

select distinct letters from change_letter_ci; select distinct letters collate utf8mb4_0900_as_cs from change_letter_ci; 以上两个SQL 可以看出，虽然我们的原有列大小写是不敏感的，但是在我们针对查询中，指出，这个列要大小写敏感的情况下，查询的结果是根据SQL 最终的collation 设定进行查询结果的输出的。

通过以上的介绍，我们可以联想出，在一些中文字符的比较中，可以针对中午的特殊的列进行特殊的 collation的设置。

简单对collation的后缀进行一个介绍，ci 结尾的是代表大小写不敏感，而cs结尾的说明大小写敏感。具体一些更多的不同点请参照MYSQL的官方文档部分。