重点--MySQL校对规则(COLLATION)的具体使用

简介

本文介绍MySQL的校对规则。

MySQL的校对规则

• 两个不同的字符集不能有相同的校对规则。

• 每个字符集有一个默认校对规则。例如：

• utf8mb4默认校对规则：utf8mb4_general_ci。

• utf8默认校对规则：utf8_general_ci。

• 存在校对规则命名约定：它们以其相关的字符集名开始，通常包括一个语言名，并且以_ci（大小写不敏感）、_cs（大小写敏感）或_bin（二进制，大小写敏感）结束。

校对规则列举

• utf8mb4_general_ci

• utf8mb4_unicode_ci

• utf8mb4_general_cs

• utf8mb4_bin

//不存在utf8_unicode_cs

查看校对规则

查看支持的校验规则
mysql> SHOW COLLATION like 'utf8%';
+————————–+———+—–+———+———-+———+
| Collation | Charset | Id | Default | Compiled | Sortlen |
+————————–+———+—–+———+———-+———+
| utf8_general_ci | utf8 | 33 | Yes | Yes | 1 |
| utf8_bin | utf8 | 83 | | Yes | 1 |
| utf8_unicode_ci | utf8 | 192 | | Yes | 8 |
…
| utf8mb4_general_ci | utf8mb4 | 45 | Yes | Yes | 1 |
| utf8mb4_bin | utf8mb4 | 46 | | Yes | 1 |
| utf8mb4_unicode_ci | utf8mb4 | 224 | | Yes | 8 |
| utf8mb4_icelandic_ci | utf8mb4 | 225 | | Yes | 8 |

查看本地的校验规则

mysql> show global variables like '%coll%';
+———————-+——————–+
| Variable_name | Value |
+———————-+——————–+
| collation_connection | utf8mb4_unicode_ci |
| collation_database | utf8mb4_unicode_ci |
| collation_server | utf8mb4_unicode_ci |
+———————-+——————–+

修改校对规则

修改数据库配置后，不会对已经存在的表造成影响，如要生效需要修改特定列的排序规则。优先级：列>表>数据库>服务器。

对已存在的表修改

# 修改数据库:  

ALTER DATABASE database_name CHARACTER SET = utf8mb4 COLLATE = utf8mb4_bin;  

# 修改表:  

ALTER TABLE table_name CONVERT TO CHARACTER SET utf8mb4 COLLATE utf8mb4_bin;  

# 修改表字段:  

ALTER TABLE table_name CHANGE column_name column_name VARCHAR(191) CHARACTER SET utf8mb4 COLLATE utf8mb4_bin NOT NULL ; 

创建表时指定

CREATE TABLE `T` (

  `name` varchar(20) COLLATE utf8mb4_unicode_ci DEFAULT NULL

) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci

utf8_general_ci 与 utf8_unicode_ci区别

| 项 | utf8mb4_unicode_ci | utf8mb4_general_ci | 总结 |
| 准确性 | 基于标准的Unicode来排序和比较，能够在各种语言之间精确排序 | 没有实现Unicode排序规则，在遇到某些特殊语言或字符时，排序结果可能不是所期望的。 | 绝大多数情况下，特殊字符的顺序不需要那么精确。 |
| 性能 | 在特殊情况下，Unicode排序规则为了能够处理特殊字符的情况，实现了略微复杂的排序算法。 | 在比较和排序的时候更快 |

绝大多数情况下，不会发生此类复杂比较。

|

总结：

utf8_unicode_ci比较准确，utf8_general_ci速度比较快。
通常情况下 utf8_general_ci 的准确性就够我们用的了，在我看过很多程序源码后，发现它们大多数也用的是utf8_general_ci，所以新建数据 库时一般选用utf8_general_ci就可以了。
如果是utf8mb4那么对应的就是 utf8mb4_general_ci utf8mb4_unicode_ci

修改编码集解决错误
[HY000][1267] Illegal mix of collations (utf8mb4_unicode_ci,IMPLICIT) and (utf8mb4_general_ci,IMPLICIT) for operation '='