If we look at for the first time, then it seems like TEXT and VARCHAR can store the same information. But, there are fundamental differences between the two. The way TEXT fields and VARCHAR fields work are imperative to take into consideration.

VARCHAR is actually the part of the ISO SQL: 2003 standard whereas The TEXT data types, includes the TINYTEXT which are non-standardized. As storage is concerned TEXT data types are kept as separate objects from the tables and result sets that contain them. The storage is transparent i.e. there is no difference in how a query involving a TEXT field is written and one involving a VARCHAR field. As the TEXT is not stored as part of a row, retrieval of TEXT fields demands for extra memory heads.

The row length of VARCHAR is restricted by maximum row length of the table. It is around 65,535 bytes for most of the storage engines. It is important to note that NBD has different row value. In theoretical terms the maximum length of the VARCHAR is 65,536 bytes. One even needs to keep in mind that the x represents the number of characters in VARCHAR and not the number of bytes. Therefore one may have difficulties defining the table with VARCHAR if the character ser uses the multi byte character for example the UTF-8
If we talk of storage length of VARCHAR then the field takes 1 byte if the VARCHAR Has maximum length of 255 bytes.

If the storage field is greater than 255 bytes then the overhead to store maximum length is 2 bytes. In case where VARCHAR is the only row in table and not allowing the NULL values then the maximum length for the VARCHAR is 65532 bytes. If we define a VARCHAR value that is longer than the allowed one, one may run into an error for example 1118 or 1074.

The size of the TEXT data depends upon the type of TEXT data type that has been used. As the TEXT is stored as objects, then the row overhead in the object table is the pointer i.e. 8 or 16 bytes. Below is the list of the Maximum TEXT length and the overhead in the TEXT object

  • MEDIUMTEXT – up to 16 MB, 3 bytes overhead
  • LONGTEXT – up to 4 GB, 4 bytes overhead
  • TINYTEXT – up to 255 bytes, 1 byte overhead
  • TEXT – up to 64 KB, 2 bytes overhead

MySQL doesn’t even allow the TEXT data types to attain a default value than that of the NULL whereas the VARCHAR fields are allowed to be originated with the DEFAULT value.

It is preferable to use VARCHAR instead of TINYTEXT due to the storage implications. You must use VARCHAR (or CHAR) if you need to have a DEFAULT value that is not NULL. Use MEDIUMTEXT or LONGTEXT to store strings longer than approximately 64 Kb. VARCHAR Does not support storing values that large. Keep in mind the effects of the multi-byte character set.