Difference between VARCHAR and TEXT in MySQL

Question

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

User · Answer

There is an important detail that has been omitted in the answer above.

MySQL imposes a limit of 65,535 bytes for the max size of each row. The size of a VARCHAR column is counted towards the maximum row size, while TEXT columns are assumed to be storing their data by reference so they only need 9-12 bytes. That means even if the "theoretical" max size of your VARCHAR field is 65,535 characters you won't be able to achieve that if you have more than one column in your table.

Also note that the actual number of bytes required by a VARCHAR field is dependent on the encoding of the column (and the content). MySQL counts the maximum possible bytes used toward the max row size, so if you use a multibyte encoding like utf8mb4 (which you almost certainly should) it will use up even more of your maximum row size.

Correction: Regardless of how MySQL computes the max row size, whether or not the VARCHAR/TEXT field data is ACTUALLY stored in the row or stored by reference depends on your underlying storage engine. For InnoDB the row format affects this behavior. (Thanks Bill-Karwin)

Reasons to use TEXT:

If you want to store a paragraph or more of text
If you don't need to index the column
If you have reached the row size limit for your table

Reasons to use VARCHAR:

If you want to store a few words or a sentence
If you want to index the (entire) column
If you want to use the column with foreign-key constraints

User · Answer

TL DR  TEXT   fixed max size of 65535 characters  you cannot limit the max size  takes 2   c bytes of disk space  where c is the length of the stored string  cannot be  fully  part of an index  One would need to specify a prefix length    VARCHAR M    variable max size of M characters M needs to be between 1 and 65535 takes 1   c bytes  for M  le  255  or 2   c  for 256  le  M  le  65535  bytes of disk space where c is the length of the stored string can be part of an index   More Details  TEXT has a fixed max size of 2  6-1   65535 characters  VARCHAR has a variable max size M up to M   2  6-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

[mysql] Difference between VARCHAR and TEXT in MySQL

Examples related to mysql

Examples related to text

Examples related to varchar