Menu
I have two fields: one to store an excerpt with a max size of 500 characters, and another to store a description with a max size of 10,000 characters.
What data types should I use, TEXT or VARCHAR? And why?
After MySQL 5.0.3 VARCHAR accepts ~65000 characters. But this does not tell why I should use one type and or the other.
I'm reasoning that I should use VARCHAR for the excerpt because I can assign a size limit, and TEXT for the description field as it's larger.
333
Some Differences Between VARCHAR and TEXTThe VAR in VARCHAR means that you can set the max size to anything between 1 and 65,535. TEXT fields have a fixed max size of 65,535 characters. A VARCHAR can be part of an index whereas a TEXT field requires you to specify a prefix length, which can be part of an index.
MySQL has many data types for storing string data in the table. VARCHAR and TEXT are two of them. Both can store a maximum of 65535 characters, but there are some differences between these data types described in this tutorial.
TEXT is the family of column type intended as high-capacity character storage. The actual TEXT column type is of four types-TINYTEXT, TEXT, MEDIUMTEXT and LONGTEXT. The four TEXT types are very similar to each other; the only difference is the maximum amount of data each can store.
A long VARCHAR is stored in the same manner as a TEXT/BLOB field in InnoDB (which I assume you're using for transactionality, referential integrity and crash recovery, right?) - that is, externally to the rest of the table on disk (which may require another disk read to retrieve).
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.
93
One difference between VARCHAR and TEXT is that you can declare a DEFAULT clause for a VARCHAR column, but not for a TEXT column.
@Andy is correct that InnoDB stores both VARCHAR and TEXT in the same way internally.
FULLTEXT indexes are supported on both VARCHAR and TEXT. Prior to 5.6, you must use MyISAM to get that type of index. In MySQL 5.6, it finally supports FULLTEXT in InnoDB. Though you should test it carefully, because it seems to return different results than the implementation in MyISAM.
However, Sphinx Search is faster and richer in features than either implementation in MySQL. See my overview in Full-Text Search Throwdown.
@Mohammed asked:
when does
VARCHARbecome consideredLONG VARCHAR? Is there a character threshold?
If you declare a length of up to 255 bytes, it can encode the length of a given string using one byte. If you declare the column max length over 255 bytes, it will use two bytes to encode the length.
You can declare a column as LONG VARCHAR, but this is really just an alias for MEDIUMTEXT.
mysql> create table test ( l long varchar); mysql> show create table test\G CREATE TABLE `test` ( `l` mediumtext ) ENGINE=InnoDB DEFAULT CHARSET=latin1 If you love us? You can donate to us via Paypal or buy me a coffee so we can maintain and grow! Thank you!
Donate Us With
