What is the type of 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 provides us with TEXT type that has more features which CHAR and VARCHAR cannot cover.

The TEXT is useful for storing long-form text strings that can take from 1 byte to 4 GB. We often find the TEXT data type for storing article body in news sites, product description in e-commerce sites.

Different from CHAR and VARCHAR, 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 retrieve or insert text data like CHAR and VARCHAR.

Note that the TEXT data is not stored in the database server’s memory, therefore, whenever 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:

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)

2, TEXT,

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)

4, and

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)

5.

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

 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)2 – 255 Bytes (255 characters)

The maximum characters that

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)

2 can store is 255 ( 2^8 = 256, 1 byte overhead).

You should use

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)

2 for the column that requires less than 255 characters, has inconsistent length, and does not require sorting such as the excerpt of a blog post and summary of an article.

See the following example:

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

In this example, we created a new table named TEXT0 that has a summary column with the data type is

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)

2.

 TEXT – 64KB (65,535 characters)

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

The TEXT can hold the body of an article. Consider the following example:

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

In this example, we added the TEXT6 column with TEXT datatype to the TEXT0 table using the TEXT9 statement.

 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)4 – 16MB (16,777,215 characters)

The

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)

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

The

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)

4 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)

 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)5 – 4GB (4,294,967,295 characters)

The

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)

5 can store text data up to 4 GB, which is a lot. It requires 4 bytes overhead.

In this tutorial, you have learned how to use various MySQL TEXT data types for storing text in database tables.

What is text data type SQL?

What are examples of text data?

What is type of data type?

What is short text data type?