[mysql] Difference between VARCHAR and TEXT in MySQL

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?

This question is related to mysql text varchar

The answer is

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

Examples related to mysql

Implement specialization in ER diagram How to post query parameters with Axios? PHP with MySQL 8.0+ error: The server requested authentication method unknown to the client Loading class `com.mysql.jdbc.Driver'. This is deprecated. The new driver class is `com.mysql.cj.jdbc.Driver' phpMyAdmin - Error > Incorrect format parameter? Authentication plugin 'caching_sha2_password' is not supported How to resolve Unable to load authentication plugin 'caching_sha2_password' issue Connection Java-MySql : Public Key Retrieval is not allowed How to grant all privileges to root user in MySQL 8.0 MySQL 8.0 - Client does not support authentication protocol requested by server; consider upgrading MySQL client

Examples related to text

Difference between opening a file in binary vs text How do I center text vertically and horizontally in Flutter? How to `wget` a list of URLs in a text file? Convert txt to csv python script Reading local text file into a JavaScript array Python: How to increase/reduce the fontsize of x and y tick labels? How can I insert a line break into a <Text> component in React Native? How to split large text file in windows? Copy text from nano editor to shell Atom menu is missing. How do I re-enable

Examples related to varchar

SQL Server date format yyyymmdd What does it mean when the size of a VARCHAR2 in Oracle is declared as 1 byte? Difference between VARCHAR and TEXT in MySQL PostgreSQL: ERROR: operator does not exist: integer = character varying Can I use VARCHAR as the PRIMARY KEY? Is the LIKE operator case-sensitive with MSSQL Server? How to convert Varchar to Double in sql? SQL Server : error converting data type varchar to numeric What is the MySQL VARCHAR max size? SQL Server Convert Varchar to Datetime