MYSQL中varchar和TEXT的相关问题详析

起因

引发原因：门店需求新增自提门店，自提门店需要加自提点图片，在渠道店上引入了图片地址img_url 的字段，字段值定义为text not null 但是因为text字段不能设置默认值，所以未设置默认值；

上线时候先上线了数据库字段，代码还未上线，线上新增渠道店的时候导致该字段为null所以不能新增渠道店报错。

解决方案：后紧急工单更改了img_url字段设置字段类型为varchar(2000) 默认值为'' 线上可以新增渠道店；

问题分析：text和varchar

根据阿里巴巴开发手册嵩山版 MySQL 数据库篇

建表规约第八条：

【强制】 varchar 是可变长字符串，不预先分配存储空间，长度不要超过 5000，如果存储长度

大于此值，定义字段类型为 text ，独立出来一张表，用主键来对应，避免影响其它字段索引效

率

第十三条：

 【推荐】字段允许适当冗余，以提高查询性能，但必须考虑数据一致。冗余字段应遵循：

1） 不是频繁修改的字段。

2） 不是唯一索引的字段。

3） 不是 varchar 超长字段，更不能是 text 字段。

正例：各业务线经常冗余存储商品名称，避免查询时需要调用 IC 服务获取。

ORM 映射

第一条：

【强制】在表查询中，一律不要使用 * 作为查询的字段列表，需要哪些字段必须明确写明。

说明：1）增加查询分析器解析成本。2）增减字段容易与 resultMap 配置不一致。3）无用字段增加网络

消耗，尤其是 text 类型的字段。

mysql的官方文档

mysql的官方文档上写了varchar类型最长可以指定为65535字节，

但我们建表的时候，实际上因为指定了字符集的问题，如果是在utf8的字符编码下，实际上最大的长度只能为 21845，因为utf8一个字符占3个字节

utf8编码下
Column length too big for column 'varchar1' (max = 21845); use BLOB or TEXT instead
 
utf8-mb4编码下
 Column length too big for column 'varchar1' (max = 16383); use BLOB or TEXT instead
 
 但是其实65535的限制是在一行数据里面限制的总数为65535，即：
 如果我们有门店表：货号指定为21845长度，品名指定为21845长度，还是不能建表成功，因为65535的限制是一行的所有字段值加起来的限制。
 （此处不仅限制varchar，是除了BLOBs的类型都会被限制）
 会出现如下报错：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

TEXT数据类型

可以存储1-4GB字节长度，MySQL并不把TEXT数据存储在内存中，而是存储与磁盘中，所以MySQL每次必须从磁盘读取，导致它比varchar要慢

TINYTEXT 255字节 (2KB)

TEXT 65,535字节 (64KB)

MEDIUMTEXT 16,777,215字节(16M)

LONGTEXT 4,294,967,295字节(4GB)

附：MYSQL中varchar和TEXT差异点

text 字段，MySQL不允许有默认值。建立索引必须给出前缀索引长度。

varchar 允许有默认值，对索引长度没限制。

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

总结：

varchar字段可以设置，但不要超过5000长度

text字段可以使用，但如果非必要应尽量避免，可以采用独立表对应