https://blog.csdn.net/qq_34369588/article/details/103523541

MySQL 的 varchar 与 text 对比

varchar 和 text 是 MySQL 字符存储争议比较多的领域,究竟大字段用那个比较好，我们来对比一下，然后自行选择。

1 大小对比

1.1 VARCHAR

varchar 在 MySQL 中必须满足最大行宽度限制，也就是 65535(64k) 字节，而 varchar 本身是按字符串个数来定义的，在 MySQL 中使用 uft-8 字符集一个字符占用三个字节，所以单表 varchar 实际占用最大长度如下：
1）使用 utf-8 字符编码集 varchar 最大长度是 (65535-2)/3 = 21844 个字符（超过 255 个字节会有 2 字节的额外占用空间开销，所以减 2，如果是 255 以下，则减 1）。
2）使用 utf-8mb4 字符集，MySQL 中使用 utf-8mb4 字符集一个字符占用 4 个字节，所以 varchar 最大长度是 (65535-2)/4 = 16383 个字符（超过 255 个字节会有 2 字节的额外占用空间开销，所以减 2，如果是 255 以下，则减 1）。
注：如果使用 utf-8mb4 字符集时，有些需要存储 utf-8 字符的时候,还是会只占 3 字节，所以有时会比这个计算值能存更多个字符，因为 utf-8mb4 是 utf-8 的超集。

1.2 TEXT

最大限制也是 64k 个字节，但是本质是溢出存储，InnoDB 默认只会存放前 768 字节在数据页中，而剩余的数据则会存储在溢出段中,虽然也受单表 65535 最大行宽度限制，但 MySQL 表中每个 BLOB 和 TEXT 列实际只占其中的 5 至 9 个字节，其他部分将进行溢出存储。所以实际占用表最大行宽度为 9+2 字节，外加的是额外开销，跟表的实际宽度没有关系：
1）如果使用 utf-8 字符集，那么单字段占用最大长度也是 21844 个字符。
2）不过单表可以设置多个 text 字段，这就突破了单表最大行宽度 65535 的限制。
注：如果采用了新的行格式类型 Barracuda (梭子鱼)，该文件格式拥有新的两种行格式（compressed 和 dynamic），两种格式对 blob/text 字段采用完全溢出的方式，数据页中只存放 20 字，,其余的都存放在溢出段中。

1.3 其他 TEXT

text 字段是分长中短类型，不像 varchar 只有一种，除了上面的 text，还有下面三个：
1.3.1 TinyText
最大长度 255 个字节，实际上是个没什么意义的类型了。
1.3.2 MEDIUMTEXT
最大长度限制 16M 个字节。和普通 text 一样也支持溢出存储，所以实际占用表最大行宽度为 9+3 字节，外加的是额外开销。
1.3.3 LONGTEXT
最大长度限制 4G 个字节。和普通 text 一样也支持溢出存储，所以实际占用表最大行宽度为 9+4 字节，外加的是额外开销。

1.4 示例

  1. # VARCHAR 单表单字段最长不能超过 21844 
  2.     CREATE TABLE test(   
  3.           va VARCHAR(21845) 
  4.   )DEFAULT CHARSET=utf8; 
  5.   [Err] 1118 - Row size too large. The maximum row size for the used table type, not counting BLOBs, is 65535. This includes storage overhead, check the manual. You have to change some columns to TEXT or BLOBs 
  7. # 这样就可以了 
  8.     CREATE TABLE test(   
  9.       va VARCHAR(21844) 
  10.     )DEFAULT CHARSET=utf8;
  11. 受影响的行: 0 
  12. 时间: 0.155s 
  14. # 虽然每个BLOB和TEXT列 账户只占其中的5至9个字节。但是还不够 
  15.     CREATE TABLE test(     
  16.         va VARCHAR(21841),    
  17.          tx text 
  18.     )DEFAULT CHARSET=utf8; 
  19. [Err] 1118 - Row size too large. The maximum row size for the used table type, not counting BLOBs, is 65535. This includes storage overhead, check the manual. You have to change some columns to TEXT or BLOBs 
  21. #然后9+2就可以了 
  22.     CREATE TABLE test(     
  23.         va VARCHAR(21840),     
  24.         tx text 
  25.     )DEFAULT CHARSET=utf8; 
  26. 受影响的行: 0 
  27. 时间: 0.170s

1.5 额外占用空间开销说明

  • varchar 小于 255byte 1byte overhead
  • varchar 大于 255byte 2byte overhead
  • tinytext 0-255 1 byte overhead
  • text 0-65535 byte 2 byte overhead
  • mediumtext 0-16M 3 byte overhead
  • longtext 0-4Gb 4byte overhead

注：虽然 text 字段会把超过 768 字节的大部分数据溢出存放到硬盘其他空间，看上去是会更加增加磁盘压力。但从处理形态上来讲 varchar 大于 768 字节后，实质上存储和 text 差别不是太大了。因为超长的 varchar 也是会用到溢出存储，读取该行也是要去读硬盘然后加载到内存，基本认为是一样的。
另外从 8000byte 这个点说明一下，MySQL 的 InnoDB data page 默认一个数据页是 16K，要存两行数据，所以对于 varcahr,，text 如果一行数据不超过 8000byte，overflow 不会存到别的 page 中。

2 差异点

text 字段，MySQL不允许有默认值。建立索引必须给出前缀索引长度。
varchar 允许有默认值，对索引长度没限制。
注：InnoDB 引擎单一字段索引的默认长度最大为 767 字节，MyISAM 为 1000 字节。例如字符编码是 utf8，那么 varchar 的索引最大长度是 256 个字符。超出限制会导致索引创建不成功，转而需要创建前缀索引。设置InnoDB_large_prefix = 1 可以增大限制，允许索引使用动态压缩，但是表的 row_format 必须是 compressed 或者 dynamic。可以使索引列长度大于767bytes，但是总长度不能大于 3072 bytes。

3 总结

  • 根据存储的实现：可以考虑用 varchar 替代 text，因为 varchar 存储更弹性，存储数据少的话性能更高。
  • 如果存储的数据大于 64K，就必须使用到 mediumtext，longtext，因为 varchar 已经存不下了。
  • 如果 varchar(255+) 之后，和 text 在存储机制是一样的，性能也相差无几。
  • 需要特别注意 varchar(255) 不只是 255byte，实质上有可能占用的更多。