Mysql表设计之Varchar(N)、TEXT和Char如何选择

本文介绍适用于MySQL5.5之后，且引擎为InnoDB。

首先要明白varchar(n)的n是什么意思，在MySQL5.0.3以前版本varchar(n)中的n表示字节数，MySQL5.0.3以后版本varchar(n)中的n表示字符数；在默认n不选的情况下，varchar的长度为255，如果长度大于此值，则需要编辑长度n。

varchar 和 text 的区别

varchar 和 text 的最大长度都是65535字符，所不同的是varchar是变长的，因此我们可以将varchar(n)的n设置为1~65535之间的任意值，而text是固定长度的。并且varchar可以是索引的一部分，而text则要求我们指定前缀长度，才能将此长度看作是索引的一部分。除此之外，text还有几种变体，分别是MEDIUMTEXT或LONGTEXT，MEDIUMTEXT最多可存储16 MB的字符串，而LONGTEXT最多可存储4GB的字符串！

更新：此处有小伙伴在评论区回复：varchar的最大长度是65535字节而不是字符，这是不准确的，虽然实际只能存储21844或32766字符（具体取决于字符的编码），但这并不是说varchar的最大长度的单位是字节，而导致其只能存储少于65535字符的原因是因为MySQL的行默认最大65535字节，并且是所有列共享的，所以VARCHAR的最大值受此限制。下面我将详细说明，力求通俗易懂！

因为数据是存储在数据行中的，因此为了理清varchar到底能存储多少字符，我们首先需要知道MySQL的行最大长度是怎么得到的，在MySQL中，行模式主要有REDUNDANT(冗余行格式)、COMPACT、DYNAMIC(动态行格式) 和COMPRESSED(压缩行格式)，我们可以通过下面的命令来查看数据库使用的行模式：

show table status FROM db_name like 'table_name';

我们现在一般用的就是DYNAMIC和COMPRESSED，不同的行模式存储数据的方式不同，而为了说清楚数据具体是怎么存储的，又不得不回顾一下数据的组织：

• 首先，InnoDB是以 聚簇索引 组织数据的，也就是说，数据都存在叶子节点上。那么最大的行长度是 65535(64KB)，也就意味着，叶子节点上，会存在 64KB 的数据。
• 但是，**InnoDB 是以页(page) 为最小单位存储数据的，默认为16KB。**此时如果一个行数据，超过了一页的一半，那么一个页只能容纳一条记录，这样B+Tree在不理想的情况下就变成了双向链表。
• 所以 MySQL 会存在限制，不会让行数据超过页的一半。(也就是说一个页最少有两条数据)

为了避免B+树叶子节点之间的组织变成了双向链表，MySQL进行数据存储的时候，会将数据分为两部分：

1. 可变长度的类型字段 (VARBINARY, VARCHAR, BLOB and TEXT)， 这部分数据会根据行格式，进行页溢出存储。
2. 定长字段的占用空间大小，这些数据会存放在叶子节点上，数据大小不超过页大小的一半(innodb_page_size)。

也就是说，MySQL 使用了页溢出来存储可变长类型的数据，那此时再来看不同的行模式是如何存储的。

• REDUNDANT和COMPACT，它的规定就是如果可变长度字段<=768字节，那不会发生行溢出，此时数据都在一个数据页中；但如果可变字段长度大于768则前768数据仍然在那个数据页，但剩下的数据则会放在溢出页里面，如下图：

这种存储方式对于数据长度小于768的很好用，但一旦大于则会耗费较多的CPU资源。

• DYNAMIC和COMPRESSED，对可变字段完全采用行溢出，即聚集索引记录的数据页只保留20字节的指针，用来指向真实存放它的溢出段地址，如下图：
  
  通过上述了解我们应该明白了数据行并不是全部放在聚簇索引中的，而是根据行格式的不同/可变长的不同，会额外的存储在其他页中(页溢出)。

因此在上述的行模式（COMPACT、DYNAMIC等）下，行的长度受下面几个因素影响：

• 行存储的最大字节数。在MySQL种默认最大为65535字节，当我们申请varchar(65536)时会报错。
• 数据之外的存储开销，官方定义中包括：NULL标识、长度标识。下面分别介绍这两个存储开销：
  • NULL标识：在COMPACT、DYNAMIC行格式下，行大小除了数据列长度，还包括可空列标识，即NULL标识位。如果有一个列允许为空，则需要1 bit来标识（不足8也会使用8比特），每8 bits的标识会组成一个字段，该字段会存放在每行最开始的位置。并且这个标识位不是放在每列，而是每行共享。即假设一张表中存在N个可空字段，NULL标识位需要⌈N / 8 ⌉ （向上取整）个字节。此时整行可用于数据存储的空间只有65535 − ⌈ N / 8 ⌉个字节。这其实很好验证，我们可以创建数据库指定字符集为Latin，之后创建表设定一个字段类型为varchar，长度为65533（这是因为还有长度标识），且可为空，会发现无法创建，但换成65532则可以成功。
  • 长度标识：需要记录当前的varchar的字节数，因为最大字节数为65535，则需要字节为2字节（$2^{16}=65536$）。但如果存储小于255字节则只需要一个字节存储。字符长度的获取是通过计算可能的字节数来确定的，如VARCHAR(100)，字符集为UTF8，可能的字节数为300，长度标识则为2字节。
• 存储字符的字符集。常见的三个字符集：
  • GBK：单字符最大可占用2个字节。
  • UTF8：单字符最大可占用3个字节。
  • UTF8MB4：单字符最大占4个字节。

算法如下：

最大长度(字符数) = （行存储最大字节数 - NULL标识列占用字节数 - 长度标识字节数） / 字符集单字符最大字节数。有余数时向下取整。

那此时再来看：

• VARCHAR(4)，最多存储4个字符，有几个字符存储几个。在限定字段非空下存储字节数 = 数据值的字节和 + 1字节。允许为空则还需要再加上实际空的1字节。
• CHAR(4)，最多存储4个字符，不足4个尾部用空格填满。存储字节数 = 数据值的字节和 + 补位空格数。

相信你还是能很快理解的。到此你是否可以明白为什么只能存储21844或32766字符呢？如果有问题欢迎留言指教，谢谢~

在此附上char 和 varchar的特性区别：

特性	CHAR	VARCHAR
长度	定长，固定字符数，最大为255字符，当数据长度不足声明时会自动在尾部填充空格	长度可变，可设置最大存储字符数，但不可超过行大小（此处代表的是65535字节）
前缀长度	无	1~2字节，看列长度是否可能超过255字节
是否尾部空格	长度不足会默认用空格填满，检索和获取时会自动去除	不会自动填充空格，输入包含空格输出原样输出
超长处理	超长部分如果是空格自动截断，如果是字符则会报错	超出部分如果是空格自动截断，如果是字符则报错
存储开销	数据值的字节和 + 补位空格数	数据值字节和 + 长度标识字节数 （？+ Null标识字节）

查询速度

在查询速度方面，varchar的速度是优于text的，当然char是优于varchar的，但是char也是定长的，适合存储定长的数据，例如电话号码，身份证等。 此处介绍一下char，char(n)中的n表示字符数，最大长度是255个字符； 如果是utf8编码方式， 那么char类型占255 * 3个字节。（utf8下一个字符占用1至3个字节）

内存中存放

在内存中，text是按照最长长度（65535）存放的，而varchar也是按照最长的方式在内存中进行操作的。比如说要进行排序的时候，varcahr(100)是按照100这个长度来进行的，不合理的长度会浪费内存空间。

假设现在要存储90个字符的数据，此时分配两种：varchar(100)和varchar(200)，虽然他们用来存储90个字符的数据，其存储空间相同。但是对于内存的消耗是不同的。对于VARCHAR数据类型来说，硬盘上的存储空间虽然都是根据实际字符长度来分配存储空间的，但是对于内存来说，则不是。其时使用固定大小的内存块来保存值。简单的说，就是使用字符类型中定义的长度，即200个字符空间。显然，这对于排序或者临时表(这些内容都需要通过内存来实现)作业会产生比较大的不利影响。解释可以参见这里。如果不想看解释，我这里大概说下：假设VARCHAR(100)与VARCHAR(200)类型，实际存90个字符，它不会对存储端产生影响（就是实际占用硬盘是一样的）。但是，它确实会对查询产生影响，因为当MySql创建临时表（SORT，ORDER等）时，VARCHAR会转换为CHAR，转换后的CHAR的长度就是varchar的长度，在内存中的空间就变大了，在排序、统计时候需要扫描的就越多，时间就越久。

CHAR(1)与VARCHAR(1)两这个定义，会有什么区别呢?虽然这两个都只能够用来保存单个的字符，但是VARCHAR要比CHAR多占用一个存储位置。这主要是因为使用VARCHAR数据类型时，会多用1个字节用来存储长度信息。这个管理上的开销char字符类型是没有的。

参考文章： 一篇文章看懂mysql中varchar能存多少汉字、数字，以及varchar(100)和varchar(10)的区别 。