何时在mysql中使用TEXT而不是VARCHAR

本文介绍了何时在mysql中使用TEXT而不是VARCHAR的处理方法，对大家解决问题具有一定的参考价值，需要的朋友们下面随着小编来一起学习吧！ 问题描述

可能重复: MySQL:大型VARCHAR与文本?

由于VARCHAR现在可以有65k字节，那么什么时候应该使用TEXT代替VARCHAR?

解决方案

长VARCHAR的存储方式与InnoDB中的TEXT/BLOB字段相同.

从存储预期的BLOB中，TEXT为 以及长VARCHAR的处理方式相同 Innodb的方法.这就是为什么Innodb 手册称它为长列"，而不是 比BLOB多.

源

除非您需要为这些列建立索引(在这种情况下，VARCHAR要快得多)，否则没有理由对长字段使用VARCHAR而不是TEXT-在MySQL中有一些特定于引擎的优化可以进行调整根据长度检索数据，您应该使用正确的列类型以利用这些优势.

如果您正在使用MyISAM，则对该主题的深入讨论是这里.

TEXT和BLOB存储在表外，该表仅具有指向实际存储位置的指针.

VARCHAR与表内联存储.尺寸合理时VARCHAR更快.

根据此测试，VARCHAR大约是文本的三倍. /p>

Possible Duplicate: MySQL: Large VARCHAR vs. TEXT?

Since VARCHAR can have 65k bytes now, when then should TEXT be used instead of VARCHAR?

解决方案

A long VARCHAR is stored in the same manner as a TEXT/BLOB field in InnoDB.

From storage prospective BLOB, TEXT as well as long VARCHAR are handled same way by Innodb. This is why Innodb manual calls it "long columns" rather than BLOBs.

source

Unless you need to index these columns (in which case VARCHAR is much faster) there is no reason to use VARCHAR over TEXT for long fields - there are some engine specific optimisations in MySQL to tune the data retrieval according to length, and you should use the correct column type to take advantage of these.

In case you're using MyISAM an in-depth discussion on the topic is here.

TEXT and BLOB are stored off the table with the table just having a pointer to the location of the actual storage.

VARCHAR is stored inline with the table. VARCHAR is faster when the size is reasonable.

According to this test, VARCHAR is about thrice as fast as text.