The Basics Of 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 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 descriptions 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 retrieving or inserting 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: TINYTEXT
, TEXT
, MEDIUMTEXT
, and LONGTEXT
.
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
TINYTEXT
– 255 Bytes (255 characters)
The maximum number of characters that TINYTEXT
can store are 255 ( 2^8 = 256, 1 byte overhead).
You should use TINYTEXT
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
);
In this example, we created a new table named articles
that has a summary column with the data type is TINYTEXT
.
TEXT
– 64KB (65,535 characters)
The TEXT
the data type can hold up to 64 KB 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;
In this example, we added the body
column with TEXT
datatype to the articles
table using the ALTER TABLE
statement.
MEDIUMTEXT
– 16MB (16,777,215 characters)
The MEDIUMTEXT
can hold up to 16MB of text data that 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
);
LONGTEXT
– 4GB (4,294,967,295 characters)
The LONGTEXT
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.
0 Comments
CAN FEEDBACK
Emoji