Author: Admin 05/13/2021
In this tutorial, you will learn how to use MySQL TEXT for storing text data in the database table.
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.
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
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
MySQL provides four
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 characters that
TINYTEXT can store is 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
TEXT– 64KB (65,535 characters)
TEXT data type can hold up to 64 KB that is equivalent to 65535 (2^16 – 1) characters.
TEXT also requires 2 bytes overhead.
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)
MEDIUMTEXT can hold up to 16MB text data that is equivalent to 16,777,215 characters. It requires 3 bytes overhead.
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)
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.
See Original Post:
The Basics Of MySQL TEXT Data Type By Examples (mysqltutorial.org)