Difference Between VARCHAR and TEXT in MySQL

1. MySQL TEXT
2. MySQL VARCHAR(M)

We will discuss the differences between VARCHAR and TEXT in MySQL in this article. Some of the key differences are as follows.

MySQL TEXT

• TEXT possesses a set maximum size of 65535 characters. Here we cannot edit or manipulate this value.
• It takes x + 2 bits of space in the disk. Here x represents the size of the string under consideration.
• Another key feature of TEXT is that it cannot be entirely a part of any index. Meaning the user would have to define a length.

MySQL VARCHAR(M)

• VARCHAR has an editable size with a maximum of 65535 characters. Meaning, the user would have to specify a length between 1 and 65535 characters.
• Moreover, VARCHAR takes x + 1 bytes of space in the disk where the total number of characters is less than or equal to 255. And it takes x + 2 bytes of space in the disk where the total number of characters is greater than or equal to 256 characters and less than equal to 65535 characters.
• Lastly, VARCHAR can be a part of an index.

Some other differences can be understood as follows:

Another distinction is that you cannot create an index on a TEXT column (unless for a full-text index).

You must use VARCHAR if you want the column to have an index. But keep in mind that an index’s length is limited, so if your VARCHAR column is too big, you’ll have to use only the first few characters in your index.

However, if you know that the maximum length of the possible input string is only 65535, you should use VARCHAR. For instance, a phone number, a name, or something similar. Then, instead of TINYTEXT or TEXT, you can use VARCHAR(30), and if someone tries to store the text of the entire book of Sapiens in your database, only the first 30 characters will be saved.

Therefore, with the help of this tutorial, we can realize the key differences between VARCHAR and TEXT in the MySQL database.