Updated May 23, 2023
Definition of MySQL TEXT
Usually, in MySQL, the data type defines the category of value a table column can embrace. It can be an integer, float, date, varchar, time, character, money, binary, etc. Here, we can explain MySQL TEXT Data type as a Database object responsible for storing text strings in long form in the table records. Essentially, MySQL TEXT is a family of table column types designed to provide extensive character storage capabilities in MySQL, ranging from 1 byte to 4GB in size.
Apart from the character data types VARCHAR and CHAR, MySQL supports type TEXT which adds more structures that cannot be covered by the previous ones mentioned. This is also useful for keeping the article body in a broadcast-related website or explaining products on any e-commerce website.
Syntax of MySQL TEXT
Syntax of mysql text is given below:
We introduced a basic syntax code to illustrate the TEXT data type in MySQL. We can use TEXT while creating a table using the following query structure:
CREATE TABLE TableName (ID INT PRIMARY KEY AUTO_INCREMENT, Title VARCHAR (255) NOT NULL, Description TEXT[forms]NOT NULL);Here, we have added some columns with TEXT and other data types where ’ forms’ says you can add other forms of TEXT that are explained later in the table with the name as TableName(provide table name as desired).
Again, the use of TEXT with ALTER keyword by the below query syntax:
ALTER TABLE TableName
ADD COLUMN ColName TEXT NOT NULL AFTER Description;In the above code, when executed, it will add a new column ColName (specify any column name as desired), with data type as TEXT, but after the column named Description created by the previous query while creating the table.
How does TEXT Data Type Work in MySQL?
MySQL TEXT is a standard data type for storing special character strings with maximum sizes or as defined. The evaluations and sorting are built on the ordering of its character set. This TEXT data type for a column is composed of four categories. They are TINYTEXT, TEXT, MEDIUMTEXT, LONGTEXT. These types of TEXT are identical but differ slightly, with the maximum capacity or volume of data values each one can hold.
The TEXT data type in MySQL bears similarity to VARCHAR, except that columns of the TEXT type do not support DEFAULT values. For info, CHAR contains a fixed length of strings defined from 0 to 255, and VARCHAR has a flexible length string defined from 0 to 65535.
- TINYTEXT is the smallest TYPE OF TEXT kind and defines the identical character length as MySQL VARCHAR type. It holds max 255 characters length of string, which supports resourcefully building up storage for small info strings.
- The TEXT data type has a size of string characters upto 65,535 bytes to manage classic long-form contents of the text.
- MEDIUMTEXT has a size of 16,777,215 string characters, which helps store longer text strings such as books, code backup, and white papers.
- LONGTEXT has a size definition that can accommodate up to a maximum of 4,294,967,295 characters. Designed to store text strings of significant length, MySQL TEXT finds support in a wide range of applications and computer programs.
Unlike VARCHAR and CHAR types, for a column having TEXT data type, you do not need to state a string character length for using it. Also, unlike VARCHAR and CHAR, MySQL does not delete or swap spaces when we insert or fetch columns with TEXT data values. TEXT data is not stored in the memory of the database server. Thus, MySQL must narrate it from the disk whenever we query or execute the TEXT data. This is then much more leisurely in the evaluation process.
You will sustain performance hits while using TEXT columns for search and select queries in MySQL because the objects will be called individually by the server and need scanning during the execution of queries. We can use this as a substitute for paging data stored in the server’s memory. If we enable strict SQL, it will impose the character lengths to the maximum and remove any inserted data that surpasses those restrictions.
But you will find a huge difference between VARCHAR and TEXT. The VARCHAR columns can be indexed, but the TEXT columns cannot. MySQL TEXT type helps store computational data and display a considerable quantity of material for tasks like searching columns, sorting, and maybe for managing minor configuration-based selections for a large development.
Examples of MySQL TEXT
Let us demonstrate some of the work using effective examples explained below:
Example #1
Example to show a declaration of a column in the database table as TEXT:
We have implemented the following CREATE query statement having a TEXT data type column:
CREATE TABLE Article_Demo (ID INT PRIMARY KEY AUTO_INCREMENT, Title VARCHAR (25) NOT NULL, Intro TEXT NOT NULL);Output:
Now, using the DESCRIBE Keyword, we will view the table information as follows:
DESCRIBE Article_Demo;Output:
Example #2
Example of creating a table column having TINYTEXT type:
CREATE TABLE MagazineDemo (ID INT PRIMARY KEY AUTO_INCREMENT, Title VARCHAR (255) NOT NULL, BriefTINY TEXT NOT NULL);Output:
Also,
DESCRIBE MagazineDemo;Output:
Example #3
Example to add a new column with TEXT type using ALTER statement:
Suppose we add long content as a body to the above-created table Article_Demo. So, we need to supplement a column with a TEXT data type named Description by applying the following query in the server:
ALTER TABLE Article_Demo
ADD COLUMN Description TEXT NOT NULL AFTER Intro;Output:
Also,
DESCRIBE Article_Demo;Output:
Likewise, for large text storage like books or more, we can implement MEDIUMTEXT and LONGTEXT in MySQL.
Conclusion
In MySQL, every column defined in a table requires a unique name and data type that represents a guideline for the Database to understand what type of data is to be specified inside a column to perform any query command or action. So, here we have learned about an important data type MySQL TEXT which holds a string with a defined capacity or length accordingly.
Recommended Articles
We hope that this EDUCBA information on “MySQL TEXT ” was beneficial to you. You can view EDUCBA’s recommended articles for more information.
