When we create a table in MySQL with a VARCHAR
column, we have to set the length for it. But for TEXT
type we don't have to provide the length.
What are the differences between VARCHAR
and TEXT
?
TEXT has a fixed max size of 2¹⁶-1 = 65535 characters. VARCHAR has a variable max size M up to M = 2¹⁶-1 . So you cannot choose the size of TEXT but you can for a VARCHAR . The other difference is, that you cannot put an index (except for a fulltext index) on a TEXT column.
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.
TEXT: 65,535 characters - 64 KB The standard TEXT data object is sufficiently capable of handling typical long-form text content. TEXT data objects top out at 64 KB (expressed as 2^16 -1) or 65,535 characters and requires a 2 byte overhead.
TEXT
c
bytes of disk space, where c
is the length of the stored string.VARCHAR(M)
M
charactersM
needs to be between 1 and 65535c
bytes (for M
≤ 255) or 2 + c
(for 256 ≤ M
≤ 65535) bytes of disk space where c
is the length of the stored stringTEXT
has a fixed max size of 2¹⁶-1 = 65535
characters.VARCHAR
has a variable max size M
up to M = 2¹⁶-1
.
So you cannot choose the size of TEXT
but you can for a VARCHAR
.
The other difference is, that you cannot put an index (except for a fulltext index) on a TEXT
column.
So if you want to have an index on the column, you have to use VARCHAR
. But notice that the length of an index is also limited, so if your VARCHAR
column is too long you have to use only the first few characters of the VARCHAR
column in your index (See the documentation for CREATE INDEX
).
But you also want to use VARCHAR
, if you know that the maximum length of the possible input string is only M
, e.g. a phone number or a name or something like this. Then you can use VARCHAR(30)
instead of TINYTEXT
or TEXT
and if someone tries to save the text of all three "Lord of the Ring" books in your phone number column you only store the first 30 characters :)
Edit: If the text you want to store in the database is longer than 65535 characters, you have to choose MEDIUMTEXT
or LONGTEXT
, but be careful: MEDIUMTEXT
stores strings up to 16 MB, LONGTEXT
up to 4 GB. If you use LONGTEXT
and get the data via PHP (at least if you use mysqli
without store_result
), you maybe get a memory allocation error, because PHP tries to allocate 4 GB of memory to be sure the whole string can be buffered. This maybe also happens in other languages than PHP.
However, you should always check the input (Is it too long? Does it contain strange code?) before storing it in the database.
Notice: For both types, the required disk space depends only on the length of the stored string and not on the maximum length.
E.g. if you use the charset latin1 and store the text "Test" in VARCHAR(30)
, VARCHAR(100)
and TINYTEXT
, it always requires 5 bytes (1 byte to store the length of the string and 1 byte for each character). If you store the same text in a VARCHAR(2000)
or a TEXT
column, it would also require the same space, but, in this case, it would be 6 bytes (2 bytes to store the string length and 1 byte for each character).
For more information have a look at the documentation.
Finally, I want to add a notice, that both, TEXT
and VARCHAR
are variable length data types, and so they most likely minimize the space you need to store the data. But this comes with a trade-off for performance. If you need better performance, you have to use a fixed length type like CHAR
. You can read more about this here.
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