MySQL Text

By /
MySQL Text

In this tutorial, we will study how to store data in a database using MySQL Text.

In a MySQL database, TEXT data objects can be used to store lengthy text strings. The TEXT column type family is designed for high-capacity character storage.

In addition to the binary character set and collation, the Text also has other character sets.
Using its character set as a starting point, comparisons and sorting are performed.
No matter what SQL mode is being used, truncating extra trailing spaces from values that are going into TEXT columns always results in a warning.

In contrast to CHAR and VARCHAR, TEXT data is not stored in the database server’s memory, so anytime you query it, MySQL must read it from the disc, which is substantially slower.

Types

There are total 4 types of TEXT datatype:

  • TINYTEXT
  • TEXT
  • MEDIUMTEXT
  • LONGTEXT

TINYTEXT

The smallest TEXT type, TINYTEXT, defines a character length that is the same as the MySQL VARCHAR type. It can build up storage for short strings of information by holding strings up to 255 characters long.

Let us understand it below:

CREATE TABLE article (ID INT PRIMARY KEY AUTO_INCREMENT, Title VARCHAR(300) NOT NULL,
descrip TINYTEXT NOT NULL);Code language: PHP (php)

Now run the below command-

DESCRIBE article;

Output-

Article
Article

TEXT

In this, two bytes are used as overhead. This indicates that the text will take up 2 Bytes in the memory plus the number of characters utilized. For instance, TEXT will take up 102 bytes in memory if your text is 100 characters long.

CREATE TABLE Demo (ID INT PRIMARY KEY AUTO_INCREMENT, Title VARCHAR(300) NOT NULL,
descrip TEXT NOT NULL);
DESCRIBE Demo;Code language: PHP (php)

Output-

Demo
Demo

MEDIUMTEXT

Up to 16,777,215 characters, or 16,777,215 bytes, or 64MB of data, can be stored in MEDIUMTEXT. Larger text strings like those in books, research papers, and code backup are appropriate uses for it. It requires 3 overhead bytes.

 CREATE TABLE paper (ID INT PRIMARY KEY AUTO_INCREMENT, Title VARCHAR(300) NOT NULL,
 descrip MEDIUMTEXT NOT NULL);
 DESCRIBE paper;Code language: PHP (php)

Output-

Paper
Paper

LONGTEXT

Among the four, LONGTEXT can store the most characters, or 4,294,967,295 characters, 4,294,967,295 bytes, or 4GB. Any long-form text strings can fit in this storage space more than adequately. LONGTEXT, for instance, can be used to store a book that MEDIUMTEXT is unable to accommodate. LONGTEXT uses an overhead of 4 bytes.

CREATE TABLE books (ID INT PRIMARY KEY AUTO_INCREMENT, Title VARCHAR(300) NOT NULL,
descrip LONGTEXT NOT NULL);
DESCRIBE books;Code language: PHP (php)

Output-

Books
Books

Conclusion

In this tutorial, we learned about the MySQL TEXT data type. For more reference, check out the official documentation of MySQL.