MySQL知识树 字符类型

字符类型

首先我们会讲到字符集的概念，字符集是指一些字符的集合，而字符编码是指一个字符的编码格式，因此两者的关系可以这样描述，例如：“使用Unicode编码的字符集”，意思就是该字符集中的所有字符都是使用Unicode进行编码的。

 

接着说一下Unicode，它是一种字符编码，不是字符集，它为每种语言的每个字符设定了统一且唯一的二进制编码。在MySQL5.5及以上版本中支持ucs2、utf8、utf8mb4、utf16、utf32五种Unicode编码的字符集，而在MySQL5.5版本之前，仅支持ucs2和utf8两种Unicode编码的字符集。

 

MySQL可以为每个列单独指定字符集。我们在创建表时若没有给列单独指定字符集，那么会默认采用创建表时指定的字符集；若创建表时没有指定字符集，那么会默认采用创建库时指定的字符集；若创建库时也没有指定字符集，那么会默认采用MySQL配置文件中指定的字符集。目前MySQL5.5版本配置文件中默认的字符集是latin1。

 

接下来我们要说一说排序规则，它是指对字符集中不同字符的比较规则。它的命名规则以_ci结尾表示对大小写不敏感，以_cs结尾表示对大小写敏感，以_bin结尾表示二进制的比较。可以对列单独指定排序规则。排序规则不仅影响大小写的比较问题还会影响索引。

 

char和varchar

两者在声明时都可以指定能存储的字符数，char(N)、varchar(N)，这里的N是指字符数而不是字节数，例如将N设置为4，则可以存储4个中文或字母或数字。两者的主要区别是char列的长度固定为创建表时声明的长度，而varchar列的长度则是可变长的。

char(N)长度的列最多可占用的字节数，为该字符集单字符最大占用字节数*N。

 

上图验证了前面说到的char(N)、varchar(N)的N是指字符数而不是字节数。由于这张表是utf8编码格式的表，这种编码格式下一个中文字符是占用3个字节的，如果N是指代字节，则很明显“测试数据”这四个字符是无法插入的，因为它们需要12个字节才能存储。

 

另外在插入数据时若插入的字符串尾部包含空格，char会删除掉尾部的这些空格而varchar会保留尾部的这些空格。详见上图，可以看到char对于头部和中间的空格是不做处理的。

 

为什么char会删除掉尾部空格呢？

这是因为在指定了能存储的字符数后，若插入的数据字符数未达到指定能存储的字符数，则会对这些数据进行右空格填充（也可以理解为进行尾部空格填充），使其长度达到指定能存储的字符数。举个例子，例如插入“测试 ”这个字符串，其长度为3个字符未达到上面定义的4个字符存储长度，MySQL会删除原数据的尾部空格，因为MySQL本身就要进行右空格填充操作，而这个原数据尾部却带有空格，明显会造成冲突。

 

有人会说，那MySQL只需要填充还缺少的空格数就行了啊。不，MySQL并不会对数据尾部你带几个空格，我需要填充几个空格这种行为做记录，因为没必要设计的这么复杂，因此直接将原数据尾部的空格都干掉，然后看距离达到指定存储的字符还差几个空格，再进行相应数量的右空格填充即可。

 

在对char列的数据进行检索时，MySQL会自动删除掉原先填充的右空格。这个行为也是可以理解的，因为如果不把自动填充的右空格删除，你所想检索的数据和实际存储的数据根本就不匹配，会导致无法检索出数据的情况。

 

鉴于此如果我们需要存储的数据尾部本身就带有空格，那么建议使用varchar类型。

 

附MySQL官方手册对这一块儿的说明：

“The length of a CHAR column is fixed to the length that you declare when you create the table. The length can be any value from 0 to 255. When CHAR values are stored, they are right-padded with spaces to the specified length. When CHAR values are retrieved, trailing spaces are removed unless the PAD_CHAR_TO_FULL_LENGTH SQL mode is enabled. ”

 

大意为：

“CHAR列的长度固定为创建表时声明的长度。 其长度可以是0到255之间的任意值。当存储CHAR类型的值时，MySQL会用指定长度的空格进行右填充。当检索CHAR类型值时，除非启用 PAD_CHAR_TO_FULL_LENGTH 的SQL模式，否则将会删除掉尾部空格。”

 

varchar最多能存储多少个字符？

这个问题千万不要想当然，不然结果会出乎你意料。

虽然我们在定义varchar时能够指定其能存储多少字符，但实际varchar能存储的字符数量是受限于其能存储的字节数的，这个限制字节数为65535，就是说被定义为varchar类型的列，最多能存储65535个字节。

 

这里详细说明下：

①如果是非空的varchar，则实际最多能存储65533个字节，因为还有两个字节是用来标记列长度的（当存储的数据大于255个字节，就需要用2个字节来标记列长度，若小于255个字节，则需要用1个字节来标记列长度）；

②如果是可为空的varchar则实际最多能存储65532个字节，因为还需要一个字节用来标记是否为空。

 

最多能存储字符数的计算：

在utf8编码格式下，将varchar定义为非空，再利用公式：(65535-2)/3=21844余1，就能得到其最多能存储的字符数为21844个。若需要存储的字符串超过了这个部分，则应该根据实际情况来选择用tinytext、mediumtext、text或longtext作为该列的数据类型。

 

binary和varbinary

两者是用来存储二进制的字符串的。它们与char和varchar的区别如下：

①binary(N)和varbinary(N)中N代表字节而不是字符；

②binary和varbinary的值排序和比较都是按照二进制值来进行的；

③binary的填充字符是0x00，char的填充字符是0x20；

 

blob和text

两者和varbinary和varchar的区别主要为：

①在blob和text列上创建索引时，必须制定索引的前缀长度；

②blob和text列没有默认值；

③在排序时只使用列的前max_sort_length个字节；