我们在执行查询操作的时候，一定要注意MySQL是否区分大小写。不然的话，可能就会查询出我们不想要的结果或者是查漏了记录。

查询数据表是否区分大小写

mysql> show create table t_blog;
+--------+------------------------------------- --------------------------+
| Table  | Create Table                                                   |
+--------+----------------------------------------------------------------+
| t_blog | CREATE TABLE `t_blog` (
  ......
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci |
+--------+----------------------------------------------------------------+
1 row in set (0.00 sec)

我们可以看到查询出的建表语句后面有一个COLLATE=utf8mb4_0900_ai_ci。这个就是本片文章要讲的内容。

COLLATE的作用

COLLATE是用来指定排序规则的，上面的utf8mb4_0900_ai_ci就是排序的规则。在MySQL中字符类型的列(如：VARCHAR、CHAR、TEXT)，都需要一个COLLATE告诉它们如何进行排序和比较。

也就是说，COLLATE会影响WHERE条件中大于小于号筛选出来的结果，会影响DISTINCT、GROUP BY、HAVING语句的查询结果。总之，只要是涉及字符比较和排序的地方，COLLATE都会影响。

COLLATE和CHARSET

COLLATE是用来指定排序规则，而CHARSET是用于指定字符集的，它们之间是相关的。每一种字符集都会有多种排序规则，都会有一种默认规则。例如：Latin1字符集的默认排序规则就是Latin1_swedish_ci, GBK的默认排序规则是gbk_chinese_ci, utf8mb4的默认排序规则是utf8mb4_general_ci。

要注意的是：从MySQL 8.0开始，默认的字符集不再是Latin1，而是utf8mb4，而且默认的排序规则也变为了utf8mb4_0900_ai_ci。

_ci: case insensitive,大小写不敏感，即不区分大小写
_cs: case sensitive,大小写敏感，即区分大小写
_bin: binary,二进制, 区分大小写

我们可以通过show collation命令查看mysql支持的所有COLLATE。

mysql> show collation;
+----------------------------+----------+-----+---------+----------+---------+---------------+
| Collation                  | Charset  | Id  | Default | Compiled | Sortlen | Pad_attribute |
+----------------------------+----------+-----+---------+----------+---------+---------------+
.....
| utf8mb4_0900_ai_ci         | utf8mb4  | 255 | Yes     | Yes      |       0 | NO PAD        |
| utf8mb4_0900_as_ci         | utf8mb4  | 305 |         | Yes      |       0 | NO PAD        |
| utf8mb4_0900_as_cs         | utf8mb4  | 278 |         | Yes      |       0 | NO PAD        |
| utf8mb4_0900_bin           | utf8mb4  | 309 |         | Yes      |       
......
+----------------------------+----------+-----+---------+----------+---------+---------------+
272 rows in set (0.00 sec)

COLLATE设置级别和优先级

COLLATE可以在实例级别、库级别、表级别、列级别，以及SQL指定。

实例级别就是MySQL配置文件或启动指令中collation_connection系统变量。在MySQL8.0以下的版本，默认使用的CHARSET是Latin1，默认的排序规则是latin1_swedish_ci。在MySQL8.0及以上，默认使用的CHARSET是utf8mb4,默认的排序规则是utf8mb4_0900_ai_ci。
库级别设置COLLATE语句如下：

CREATE DATABASE <db_name> DEFAULT CHARACTER SET utf8mb4 COLLATE utf8mb4_0900_ai_ci;

如果库级别没有设置CHARSET和COLLATE，那么默认使用实例级别的设置。

表级别设置COLLATE语句如下：

CREATE TABLE tablename (
......
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

如果表级别没有设置CHARSET和COLLATE,那么默认使用库级别的设置

列级别设置COLLATE语句如下：

CREATE TABLE tablename (
	tid int,
    tname varchar(64) CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci NOT NULL DEFAULT ''
);

如果列级别没有设置CHARSET和COLLATE，那么默认使用列级别的设置

SQL查询设置我们在写SQL查询的时候，可以设置COLLATE覆盖任何库表列的设置。

SELECT DISTINCT field1 COLLATE utf8mb4_unicode_ci FROM tablename;

SELECT field1,field2 FROM tablename ORDER BY field2 COLLATE utf8mb4_unicode_ci;

如果我们所有级别都设置了CHARSET和COLLATE。那么优先级顺序如下： SQL语句 > 列级别设置 > 表级别设置 > 库级别设置 > 实例级别

也就是说，假如我们同时设置了表级别和库级别，那么生效的是表级别设置。

MySQL如何设置是否区分大小写

查询数据表是否区分大小写

COLLATE的作用

COLLATE和CHARSET

COLLATE设置级别和优先级