MySQL字符集

字符集和校对

字符集是指一种从二进制编码到某类字符符号的映射，可以参考如何使用一个字节来表示英文字母。“校对”是指一组用于某个字符集的排序规则。MySQL4.1和之后的版本中，每一类编码字符都有其对应的字符集和校对规则。MySQL对各种字符集的支持非常完善，但是这也带来了一定的复杂性，某些场景下甚至会有一定的性能牺牲。

MySQL如何使用字符集

每种字符集都可能有多种校对规则，并且都有一个默认的校对规则。每个校对规则都是针对某个特定的字符集，和其他的字符集没有关系。校对规则和字符集总是一起使用的，所以后面我们将这样的组合也统称为一个字符集。

• MySQL的设置可以分为两类：创建对象时的默认值、在服务器和客户端通信时的设置

创建对象时的默认设置

• 创建数据库的时候，将根据服务器上的character_set_server设置来设定该数据库的默认字符集
• 创建表的时候，可以指定
• 创建列的时候，可以指定
• 默认字符集是继承关系，如果没有设置则按上一级的设置为准

服务器和客户端通信时的设置

当服务器和客户端通信的时候，它们可能使用不同的字符集。这时，服务器端将进行必要的翻译转换工作：

• 服务器总是假设客户端是按照character_set_client设置的字符来传输数据和SQL语句的
• 当服务器收到客户端的SQL语句时，它先将其转换成字符集character_set_connection。它还使用这个设置来决定如何将数据转换成字符串。
• 当服务器返回数据或者错误信息给客户端时，它会将其转换成character_set_result
  

MySQL如何比较两个字符串的大小

如果比较的两个字符串的字符集不同，MySQL会先将其转成同一个字符集再进行比较，如果两个字符集不兼容的话，则会抛出错误，例如“ERROR 1267(HY000):Illegal mix of collations” 这种情况下需要通过函数CONVERT()显式地将其中一个字符串的字符集转成一个兼容的字符集。

• 使用_uft8指定字符集，使用COLLATE指定校对规则

select _utf8 'hello' COLLATE  utf8_bin

查看命令

• show collation; 数据库支持的校对规则
• show characterset; 查看支持的字符集
• show full columns from t2; 查看表列以及设置的字符集和校对规则

字符集和校对规则如何影响查询

• 某些字符集和校对规则可能会需要更多的CPU操作
• 可能会消耗更多的内存和存储空间
• 影响索引的使用
• 排序查询，oder by title collate uft8_bin 和title的校对规则不一致，会导致索引失效
• UTF-8是一种多字节编码，它存储一个字符会使用变长的字节数(一到三个字节)。
  • MySQL内部，通常使用一个定长的空间来存储字符串，再进行相关操作，这样做的目的是希望总是保证缓存中有足够的空间来存储字符串。
  • 当它在存储在临时表中用来处理或者排序时，总是会分配最大可能的长度
  • 索引超过999字节会自动缩短到333个字符，变成前缀索引。
  • 使用英文占用一个字节，使用中文占用三个字节。