MySQL Tinytext

In a programming language, the data type is a construct that holds variables of specific types. It indicates what types of data can remain in the variable.

The data types utility includes validation over the data that gets put inside the variable and keeping consistency across the data. Depending on the various programming languages, there are different data types available.

Like in Java language, the datatypes include int, float, double, char to hold integer, rational, irrational, and character values. These data types vary in names but the same in meaning.

The types names are int, number, float, double, varchar, and many more based on the size limits that the variables can hold. Here the keywords convey what data type they will store.

Different text Data Types in the MySQL Language

The article explains the various types of text data types and the timings to use the correct one. There are four data types under the text category in MySQL language.

• TinyText
• Text
• MediumText
• LongText

Let’s understand the text category data types and the differences with other datatypes thoroughly.

The TinyText datatype is the one that holds 255 bytes only. It stores two raised to power eight minus one (2⁸-1) or 255.

The data type is the smaller unit of datatype available in MySQL to hold character values. The utility of the datatype is to store the small and valuable one-liner information or a short description of the table.

Additionally, using tiny text over varchar does not allow the user to sort the column in any way. The example of the datatype is as below:

CREATE TABLE student (stu_id INT PRIMARY KEY, stu_name VARCHAR(255), stu_ambition TINYTEXT);

The above example creates the student table with id, name, and ambition. The field stores the integer value of the id field, the name is a varchar field, and the one-liner ambition field with the tiny text datatype.

The query terminates with the ; colon symbol that says, above statement is over. The Text datatype can hold 65 kilobytes of data.

It stores two raise to power 16 minus 1 (2¹⁶-1) or 65535 bytes characters. The field is the generic and widely used field to store text or character data in the database field.

It is as big as a paragraph to keep multiple lines of data. But cannot store some large chunk of books data to it.

Let’s quickly manipulate the previously created student table. The example of the datatype is as below:

ALTER TABLE student ADD COLUMN stu_description TEXT AFTER stu_ambition;

When the above query executes, it will add a new column in table student with a new column name as stu_description. The column is of text datatype and can hold 65 KB of data.

The MediumText datatype can hold 16 megabytes of data. It stores two raise to power 24 minus 1 (2²⁴-1) or 16,777,215 bytes characters.

The field is a widely used field to store large chunks of data. These large chunks can be books, articles, etc.

The example of the datatype is as below:

ALTER TABLE student ADD COLUMN stu_body MEDIUMTEXT AFTER stu_description;

The above query will add a new column in table student with a new column name as stu_description.

The column is of MEDIUMTEXT datatype and can hold 16 MB of data. The body field of a student table can store results, publications, history, notes, meetings fields related to a student.

The LargeText datatype can hold 4 gigabytes of data. It stores two raises to power 32 minus 1 (2³²-1) or 4,294,967,295 bytes characters.

The field is a widely used attribute to store large chunks where medium text size isn’t capable of small data.

ALTER TABLE student ADD COLUMN stu_report LARGETEXT AFTER stu_body;

The above query will add a new column in table student with a new column name as stu_report.

The column is of LARGETEXT datatype and can hold 4 GB of data. The report field for a student can store results, medical reports history, and a large amount of data.

The size for each data type is there in bytes. And how these bytes get consumed depends on what character encoding gets used.