MySQL TEXT Data Type

Summary: in this tutorial, you will learn how to use MySQL TEXT for storing text data in the database table.

Introduction to MySQL TEXT data type

Besides CHAR and VARCHAR character types, MySQL supports the TEXT type that provides more features.

The TEXT is useful for storing long-form text strings that can take from 1 byte to 4GB. In practice, you often use the TEXT data type for storing articles in news sites, and product descriptions in e-commerce sites.

Unlike CHAR and VARCHAR type, you don’t have to specify a storage length when you use a TEXT type for a column.

Also, MySQL does not remove or pad spaces when retrieving or inserting text data like CHAR and VARCHAR.

Note that the TEXT data is not stored in the database server’s memory. Therefore, when you query TEXT data, MySQL has to read from it from the disk, which is much slower in comparison with CHAR and VARCHAR.

MySQL provides four TEXT types:

• TINYTEXT
• TEXT
• MEDIUMTEXT
• LONGTEXT

The following shows the size of each TEXT type with the assumption that you are using a character set that takes one byte to store a character

TINYTEXT – 255 Bytes (255 characters)

The maximum number of characters that TINYTEXT can store is 255 ( 2^8 = 256, 1 byte overhead).

In practice, you use TINYTEXT for the column that requires less than 255 characters, has inconsistent length, and does not require sorting. For example, you can use the TINYTEXT to store the excerpt of a blog post.

The following example creates a new table called articles that has a summary column with the data type is TINYTEXT:

CREATE TABLE articles (
    id INT AUTO_INCREMENT PRIMARY KEY,
    title VARCHAR(255),
    summary TINYTEXT
);Code language: SQL (Structured Query Language) (sql)

TEXT – 64KB (65,535 characters)

The TEXT data type can hold up to 64 KB which is equivalent to 65535 (2^16 – 1) characters. The TEXT datatype also requires 2 bytes overhead.

The following example adds the column body with the TEXT type to the articles table using the ALTER TABLE statement:

ALTER TABLE articles 
ADD COLUMN body TEXT NOT NULL
AFTER summary;Code language: SQL (Structured Query Language) (sql)

MEDIUMTEXT – 16MB (16,777,215 characters)

The MEDIUMTEXT can hold up to 16MB text data which is equivalent to 16,777,215 characters. It requires 3 bytes overhead.

The MEDIUMTEXT is useful for storing quite large text data like the text of a book, white papers, etc. For example:

CREATE TABLE whitepapers (
    id INT AUTO_INCREMENT PRIMARY KEY,
    body MEDIUMTEXT NOT NULL,
    published_on DATE NOT NULL
); Code language: SQL (Structured Query Language) (sql)

LONGTEXT – 4GB (4,294,967,295 characters)

The LONGTEXT can store text data up to 4GB, which is quite big in common scenarios. It has 4 bytes overhead.

Summary

• Use the TEXT data type to store long texts in the database.