VARCHAR vs. TEXT in MySQL: What are the key differences?

The difference between VARCHAR and TEXT in MySQL

When creating a table in MySQL, the length of the VARCHAR type column should be set clearly. However, for the TEXT type, the length does not have to be specified. So what is the difference between VARCHAR and TEXT?

TL;DR

TEXT

• Fixed maximum length 65535 characters (cannot limit maximum length)
• occupied Disk space 2 c bytes, where c To store the length of a string
• cannot be (fully) indexed, you need to specify the prefix length

VARCHAR(M)

• Variable maximum length M characters
• M should be between 1 and 65535
• occupies disk space of 1 c bytes (M ≤ 255) or 2 c bytes (256 ≤ M ≤ 65535), where c is the length of the stored string
• indexable

Details

The maximum length of TEXT is fixed to 2¹⁶-1 = 65535 characters, whereas the maximum length of VARCHAR is variable, up to 2¹⁶-1. Therefore, the size of TEXT is not selectable, but that of VARCHAR is.

Another difference is that indexes are not allowed on TEXT columns (except for full-text indexes). Therefore, to index this column, you must use VARCHAR. But please note that the length of the index is also limited. If the VARCHAR column is too long, the index can only use the first few characters of the VARCHAR column (see CREATE INDEX documentation).

VARCHAR should also be used if you know that the maximum length of the possible input string is only M, such as a phone number or name. Then, you can use VARCHAR(30) instead of TINYTEXT or TEXT, so that even if someone tries to save the full Lord of the Rings text in the column you use to store phone numbers, you only have to store the first 30 characters :)

Edit:If the text to be stored in the database is longer than 65535 characters, you must select MEDIUMTEXT or LONGTEXT, but be careful: the maximum string length stored is 16 MB for MEDIUMTEXT and 4 GB for LONGTEXT. If you use LONGTEXT and get the data through PHP (at least if you are using mysqli and not using the store_result function), you may get a memory allocation error because PHP will try to allocate 4 GB of memory to ensure that the entire string can be buffered. This can happen in other languages ​​besides PHP as well.

However, you should always check the input (is it too long? Does it contain weird code?) before storing the data in the database .

Please note: for both types, the disk space required depends only on the length of the stored string, not the maximum length. For example, if you use character set latin1 and store the text "Test" in VARCHAR(30), VARCHAR(100), and TINYTEXT, it will always require 5 bytes (1 byte to store the string length, each character 1 byte). If you store the same text in a VARCHAR(2000) or TEXT column, it will also require the same space, but in this case it will take up 6 bytes (2 bytes to store the string length, 1 word per character Festival).

See the documentation for more information.

Finally, I would like to add that both TEXT and VARCHAR are variable length data types, so they will most likely minimize the space required to store the data

The above is the detailed content of VARCHAR vs. TEXT in MySQL: What are the key differences?. For more information, please follow other related articles on the PHP Chinese website!