EDUCBA

Home Data Science Data Science Tutorials MySQL Tutorial MySQL TEXT 

MySQL TEXT 

By Aanchal SharmaAanchal Sharma

MySQL TEXT 

Definition of MySQL TEXT

Normally, 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 that is responsible for storing text strings in long-form into the table records. Essentially, MySQL TEXT can be recognized as a family of table column type which is proposed to provide the higher capability of character storing in MySQL ranging between 1 byte to 4GB.

Apart from the character data types VARCHAR and CHAR, MySQL supports with 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 explanation of products in any e-commerce website.

Start Your Free Data Science Course

Hadoop, Data Science, Statistics & others

Syntax of MySQL TEXT

Syntax of mysql text is given below:

We have introduced a basic syntax code succeeding 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 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 that was created by the previous query while creating 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. On the ordering of its character set, the evaluations and sorting are built. In actual, this TEXT data type for a column is composed of four categories. They are TINYTEXT, TEXT, MEDIUMTEXT, LONGTEXT. These all types of TEXT are identical to each other but differ with a slight variation that is with the maximum capacity or volume of data values each one can hold.

This data type TEXT can be said just like the MySQL VARCHAR but the former column do not exist with DEFAULT values as succeeding ones. 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.

  • Here, 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 that supports to build up storage for small info strings resourcefully.
  • 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 string characters up to 16,777,215 which is helpful to store longer text strings such as books, code backup, and white papers.
  • LONGTEXT has size definition up to max 4,294,967,295 characters which is used to store text strings up to the extreme length. It is supported in 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, different from VARCHAR and CHAR, MySQL does not delete or swabspaces when we insert or fetch columns with TEXT data values. It should be noted that the MYSQL TEXT data is not kept in the memory of the database server. Thus, MySQL will have to narrate it from the disk every time we query or execute the TEXT data. This is then much leisurelier 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 needs scanning during the execution of queries. For this, we can use as a substitute of paging data stored in the server’s memory. If we enable strict SQL then, it will impose the character lengths to the maximum and removes any inserted data that surpasses those restrictions.

But you will find a huge difference between VARCHAR and TEXT that is the VARCHAR columns can be indexed but TEXT columns cannot. MySQL TEXT type helps to store computational data and also to 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 working of MySQL TEXT Data type in MySQL 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:

MySQL TEXT -1.1

Now, using the DESCRIBE Keyword we will view the table information as follows:

DESCRIBE Article_Demo;

Output:

MySQL TEXT -1.2

Example #2

Example to create 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:

MySQL TEXT -2.1

Also,

DESCRIBE MagazineDemo;

Output:

Output -2.2

Example #3

Example to add a new column with TEXT type using ALTER statement:

Let us suppose that we are adding 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 succeeding query in the server:

ALTER TABLE Article_Demo
ADD COLUMN Description TEXT NOT NULL AFTER Intro;

Output:

Output -3.1

Also,

DESCRIBE Article_Demo;

Output:

Output -3.2

Likewise, for large text storage like for books or more, we can implement MEDIUMTEXT and LONGTEXT respectively in MySQL.

Conclusion

In MySQL, every column defined in a table requires a unique name along with its data type that represents a guideline for 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

This is a guide to MySQL TEXT. Here we also discuss the definition and how does text data type work in mysql? done along with examples and its code implementation. You may also have a look at the following articles to learn more –

  1. MySQL Index
  2. MySQL Date Functions
  3. IF Statement in MySQL
  4. MySQL Trigger