MYSQL 中的 COLLATE 到底是什么？

一直使用建表语句

create database <db_name> default charset utf8 collate utf8_general_ci;

殊不知，这个语句建表竟然有个大坑。

mysql中有utf8和utf8mb4。

这是mysql的一个遗留问题，mysql中的utf8最多只能支持3bytes长度的字符编码，对于一些需要占据4bytes的文字，比如emoji表情 。网上一搜关键字多得是这类问题：如何把emoji存进MySQL中？关键原因就是字节长度问题。让我们来看下字符编码的解释。

       

        UTF-8:   Unicode Transformation Format-8bit，允许含BOM，但通常不含BOM. 是一种解决国际字符串的一种多字节编码，对英文使用8bit(一个字节)；中文使用24bit(三个字节)来进行编码。

        UTF8MB4:  mb4即most bytes 4，专门用来兼容四个字节即以上的unicode。

 

Mysql在5.53版本之后增加的utf8mb4编码除了可以存储中英文（英文1个字节，中文3个字节）， 还可以用来解决这种问题：存储特殊字符（4个字节）。所以，设计数据库时如果想要允许用户使用特殊符号，最好使用utf8mb4编码来存储，使得数据库有更好的兼容性，但是这样设计会导致耗费更多的存储空间。同时也要考虑到，与乙方对接数据时，对方是否接收utf8mb4编码？请大家酌情处理。如果使用utf8mb4请将建表语句改为：
 

create database <db_name> default charset utf8mb4 collate utf8mb4_general_ci;

跑题了，现在回到MYSQL中的collate到底是什么中吧。

其实就是排序规则，对于那些字符类型的列如 varchar char text需要告诉MySQL排序的规则，也就是collate，这会直接影响到order by排序的顺序，如果建立索引指定的是字符类型也会影响，总之，凡是涉及到字符类型比较或排序的地方，都会和COLLATE有关。

那么_ci是什么呢？很多COLLATE都带有_ci字样，这是Case Insensitive的缩写，即大小写不敏感，也就是说"A"和"a"在排序和比较的时候一样。当使用语句 select * from table where field="a" 同样会把 field为'A'的结果列出来。而那些后缀为_cs的collate是Case Sensitive的缩写，与大小写敏感。 

下面是查看所有的字符集和

使用       show collation   指令可以查看到mysql所支持的所有COLLATE

使用       show charset   指令可以查看到mysql所支持的所有COLLATE

 

COLLATE设置级别及其优先级

设置COLLATE可以在示例级别、库级别、表级别、列级别、以及SQL指定。

库级别 设置COLLATE的语句如下：

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

 

表级别 的COLLATE设置，则是在CREATE TABLE的时候加上相关设置语句，例如：

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

如果表级别没有设置 CHARSET 和 COLLATE ，则表级别会继承库级别的 CHARSET 与 COLLATE 。

 

列级别 的设置，则在CREATE TABLE中声明列的时候指定，例如：

CREATE TABLE db_TABLE2( 
    `field2` varchar(20) character set utf8mb4 collate utf8mb4_unicode_ci 
    ...
);

如果列级别没有设置CHARSET和COLATE，则列级别会继承表级别的CHARSET与COLLATE。

 

最后，你也可以在写SQL查询的时候显示声明COLLATE来覆盖任何库表列的COLLATE设置：

SELECT DISTINCT field1 COLLATE utf8mb4_general_ci FROM table1;

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

如果全都显示设置了，那么优先级顺序是 SQL语句 > 列级别设置 > 表级别设置 > 库级别设置 > 实例级别设置。