定义

collate 是一种排序规则，用于在比较text、varchar等文本字段时，确定顺序的方案

如下所示，可以位于字段（name、nick）、表（employee）的定义上

CREATE TABLE `employee` (
    `id` int(11) NOT NULL AUTO_INCREMENT,
    `name` varchar(256) COLLATE utf8mb4_bin COMMENT '名字',
    `nick` varchar(256) COLLATE utf8mb4_bin COMMENT '绰号',
    PRIMARY KEY (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_bin;

也可以用于库上

create database test default character set utf8mb4 collate utf8mb4_general_ci;

或者在查询语句中直接使用

select id from employee order by name collate utf8mb4_general_ci

各处的collate按照以下顺序生效：

1. SQL语句
2. 列级
3. 表级
4. 库级
5. 实例级

utf8与utfmb4关系

• utf8 最长3字节，utfmb4 最长4字节
• utfmb4 完全兼容utf8，所以尽量使用utfmb4
• 有些表情时用4字节表示的，只能用utfmb4 表示

区别

最关键的点在于尾缀 _ci，case sensitive 大小写是否敏感

• utf8mb4_bin：大小写敏感，二进制数据存储
• utf8mb4_general_ci：不区分大小写，在对特殊字符排序时可能会不一致，速度快
• utf8mb4_unicode_ci：不区分大小写，精准比较，速度较慢

坑

由于数据库默认的排序规则是 utf8mb4_general_ci （不区分大小写），所以如果不特别指定的话，在用字符串作为主键的情况下，就会出现不一致的key（大小不一致）却产生冲突的现象，如下所示

MySQL [test]> CREATE TABLE `business` (
	`business_id` varchar(32) COMMENT '业务唯一标识符',    
	PRIMARY KEY (`business_id`) 
) ENGINE=InnoDB;
Query OK, 0 rows affected (0.02 sec)

MySQL [test]> insert into business values("key");
Query OK, 1 row affected (0.00 sec)

MySQL [test]> insert into business values("KEY");
ERROR 1062 (23000): Duplicate entry 'KEY' for key 'PRIMARY'

解决方案

可以通过修改字段的排序方式，可以避免该问题

MySQL [test]> alter table business modify column business_id varchar(32) collate utf8mb4_bin;GINE=InnoDB;
Query OK, 0 rows affected (0.02 sec)

MySQL [test]> insert into business values("KEY");
Query OK, 1 row affected (0.00 sec)

可见在修改字段的排序方式后，就能插入进去了

在出现该问题后，只能从字段上修改排序方式了，实例、库、表的修改都不能影响该字段排序了；因为在创建该字段时，已经隐形继承了上一级的排序方式，当上级（实例、库、表）修改时，不会影响该字段的排序方式了，只能显示指定该字段的排序方式

MySQL [test]> ALTER TABLE `business` CHARACTER SET utf8mb4 collate utf8mb4_bin;
Query OK, 0 rows affected (0.02 sec)

MySQL [test]> insert into business values("KEY");
ERROR 1062 (23000): Duplicate entry 'KEY' for key 'PRIMARY'

如上所示，即使我们修改了表的排序方式，大小不一致的key仍有碰撞，即排序方式没变

其实我们通过表的创建语句也可以看出，当我们更新表的排序方式时，字段的不一致排序方式会显示展示出来：

MySQL [tms_test]> show create table business;
+----------+---------------+
| Table    | Create Table                
+----------+---------------+
| business | CREATE TABLE `business` (
  `business_id` varchar(32) CHARACTER SET utf8mb4 COLLATE utf8mb4_general_ci NOT NULL COMMENT '业务唯一标识符',
  PRIMARY KEY (`business_id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_bin 
+----------+---------------+

参考

Mysql的utf8与utf8mb4区别，utf8mb4_bin、utf8mb4_general_ci、utf8mb4_unicode_ci区别

MYSQL中的COLLATE是什么？