面试官：MySQL中COLLATE是用来做什么的？

📢📢📢📣📣📣
哈喽！大家好，我是【IT邦德】，江湖人称jeames007，10年DBA工作经验
一位上进心十足的【大数据领域博主】！😜😜😜
中国DBA联盟(ACDU)成员，目前从事DBA及程序编程
擅长主流数据Oracle、MySQL、PG 运维开发，备份恢复，安装迁移，性能优化、故障应急处理等。
✨ 如果有对【数据库】感兴趣的【小可爱】，欢迎关注【IT邦德】💞💞💞
❤️❤️❤️感谢各位大可爱小可爱！❤️❤️❤️

前言

MySQL中凡是涉及到字符类型比较或排序的地方，都会和COLLATE有关

---

⛳️ 1.COLLATE介绍

COLLATE即使校验规则， 会影响到 ORDER BY 语句的顺序，
会影响到 WHERE 条件中大于小于号筛选出来的结果，
会影响DISTINCT、GROUP BY、HAVING语句的查询结果。
另外，mysql 建索引的时候，如果索引列是字符类型，
也会影响索引创建，只不过这种影响我们感知不到。
总之，凡是涉及到字符类型比较或排序的地方，都会和 COLLATE 有关

在mysql中执行show create table 指令，可以看到一张表的建表语句

所谓所谓utf8_unicode_ci，其实是用来排序的规则。
对于mysql中那些字符类型的列，
如VARCHAR，CHAR，TEXT类型的列，
都需要有一个COLLATE类型来告知mysql如何对该列进行排序和比较。

⛳️ 2.COLLATE的区别

COLLATE 通常是和数据编码（CHARSET ）相关的 ，
一般来说每种 CHARSET 都有多种它所支持的 COLLATE，
并且每种 CHARSET 都指定一种 COLLATE 为默认值。

1. latin1 编码的默认 COLLATE 为 latin1_swedish_ci
2. GBK 编码的默认 COLLATE 为 gbk_chinese_ci
3. utf8mb4 编码的默认值为 utf8mb4_general_ci
   另外需要注意的一点是，
   从 mysql 8.0 开始，
   mysql 默认的 CHARSET 已经不再是 Latin1 了，
   改为了 utf8mb4，并且默认的 COLLATE 也改为了 utf8mb4_0900_ai_ci

mysql> show charset where charset in (‘gbk’,‘utf8mb4’,‘latin1’);

⛳️ 3.COLLATE的影响

🌈 3.1 大小写影响

create table case_bin_test (word VARCHAR(10));
INSERT INTO case_bin_test VALUES (‘Frank’),(‘Google’),(‘froogle’),(‘flickr’),(‘FlicKr’);

mysql查询默认是不区分大小写的，如下所示

如果想区分大小写，则要加binary，如下所示

🌈 3.2 排序影响

MySQL默认的order by是不区分大小写的
##建表
create table test_collate
(id int NOT NULL AUTO_INCREMENT PRIMARY KEY,
text varchar(40) NOT NULL
)
##插入数据
insert into test_collate(text) values(‘aaa’),(‘bbb’),(‘AAA’),(‘BBB’);

现在，如果我们按照text排序：
mysql> select * from test_collate order by text;

结果表明，大写的AAA和小写的aaa被认为是一样的， 都排在BBB和bbb前面

现在，如果我们按照text排序,使用utf8mb4_bin：
select * from test_collate order by text collate utf8mb4_bin;

大家点赞、收藏、关注、评论啦 👇🏻👇🏻👇🏻微信公众号👇🏻👇🏻👇🏻