MySQL 字段类型 varchartext 的差异

一次数据库表结构调整,引起了我对 MySQL 字段类型 TEXTVARCHAR 的思考。

我之前的认知是:TEXT = VARCHAR(65535)
我看到过一些文章说过 VARCHAR 可以做索引,而 TEXT 不可以,但其实也无所谓,把很长的字符串放到索引里面去,估计是猪头。

顺手 SOStackOverflow 和官方文档走一波,发现还有很多细节,我都不了解。
PS:其实家里还有一本《MySQL 技术内幕》,很少翻动...

参考 MySQL 5.7 官方文档:

TEXT 可以创建索引,普通索引(关键字索引)或唯一索引必须指定前缀长度,FULLTEXT 不需要。没有尝试过主键索引,应该没这个实验必要吧!

Prefix support and lengths of prefixes (where supported) are storage engine dependent.
For example, a prefix can be up to 767 bytes long for InnoDB tables or 3072 bytes if the innodb_large_prefix option is enabled.
For MyISAM tables, the prefix length limit is 1000 bytes.

字段 长度    
TINYTEXT 28 - 1 255
TEXT 216 - 1 65535 64K
MEDIUMTEXT 224 - 1 16M
LONGTEXT 232 - 1 4G

更糊涂了,那么这两个字段相对而言的优势都能体现在什么地方呢?

  1. 有人提示多字节字符可能带来的 3 - 12 倍空间占用
  2. 有人说 TEXT 类型似乎在插入时效率更高
  3. 有人说 VARCHAR 存储在表记录中,而 TEXT 是存储在别的地方,而表记录中只记录一个引用
  4. 有人提示说,要注意不同版本之间、不同存储引擎之间,具体实现有很大差异,其他人的答案不能尽信
  5. 有人说这两种类型没有实质差异

最后,参考了各处资料,我的理解是:VARCHAR 是 SQL 标准,TEXT 不是,仅此而已(似乎真的没有在文档中找到他们二者差异的描述)。
PS:微软的 MSDN 上说 SQLServer 计划移除 TEXT 类型,建议使用 VARCHAR 替代。

  1. VARCHAR 的最大长度取决于存储引擎,而存储引擎默认最大支持 65536 字节
  2. VARCHAR 长度是字符数量,不是字节数
    所以用满 65535,在处理多字节问题时,往往会遇到问题。

参考资料与拓展阅读

  1. https://dev.mysql.com/doc/refman/5.7/en/create-index.html
  2. StackOverflow,MySQL: Large VARCHAR vs. TEXT?
  3. StackOverflow,varchar(255) vs tinytext/tinyblob and varchar(65535) vs blob/text
  4. StackOverflow,Difference between VARCHAR and TEXT in mysql
  5. MSDN,ntext, text, and image (Transact-SQL)