VARCHAR vs. TEXT:
There are many types of differences between the VARCHAR and TEXT data types. The differences between these data types are described below.
Features of VARCHAR and TEXT
The VARCHAR and TEXT data types store data differently in the database table. Different features of these data types are described below.
| VARCHAR | TEXT |
| It is mainly used to store smaller string data. | It is mainly used to store larger string data. |
| It is used to store variable-length of string data. | It is used to store fixed-length of string data. |
| The maximum length of the string can be defined. | The length of the string can’t be defined. |
| Index can’t be applied in this data type. | The index can be applied in this data type. |
| It takes length + 1 byte of space if the length value is less than or equal to 255 characters, and it takes length + 2 bytes of space if the length is greater than or equal to 256 characters. | It takes the length of +2 bytes of disk space. |
| It works slower. | It works faster. |
Declaration of VARCHAR and TEXT
The ways of declaring the VARCHAR and TEXT data types have been explained below by creating two tables with the field of VARCHAR and TEXT data types. It is mentioned in the previous part of this tutorial that the length is required to define the VARCHAR data type, and the length is not required to define the TEXT data type.
Run the following CREATE DATABASE statement to create the database named test_db.
Run the following query statement to select the test_db database before creating the table.
Run the following CREATE TABLE statement to create a customer table containing five fields. Here, the data type of id field is an integer and, the data type of name, email, address, and contact_no fields are varchar that contains length value.
id INTNOT NULL PRIMARY KEY,
name VARCHAR(30) NOT NULL,
email VARCHAR(50),
address VARCHAR(300),
contact_no VARCHAR(15));
Run the following DESCRIBE statement to check the structure of the customers table.
The following output will appear after executing the above statement.
Run the following INSERT query to insert three records into the customers table.
('02', 'Akash Chowdhury', '[email protected]', 'house # 25b, road # 1, dhanmondi r/a,Dhaka-1205, Bangladesh', '01855342357'),
('03', 'Babor Ali', '[email protected]', 'north bhasantek(north side of cmh), p.s. # kafrul, dhaka cantonment, 1206, Bangladesh', NULL);
Run the following SELECT statement to read all records of the customers table.
The following output will appear after executing the above statement.
Run the following CREATE TABLE statement to create an employees table containing five fields. The data type of the id field is an integer. The data type of name, email, and contact_no fields are varchar. The data type of address field is text. Here, no length value is declared for the address field because of the text data type.
id INTNOT NULL PRIMARY KEY,
name VARCHAR(30) NOT NULL,
email VARCHAR(50),
address TEXT,
contact_no VARCHAR(15));
Run the following DESCRIBE statement to check the structure of the employees table.
The following output will appear after executing the above statement.
Run the following INSERT query to insert three records with the same content of the customers table to the employees table.
('02', 'Akash Chowdhury', '[email protected]', 'house # 25b, road # 1, dhanmondi r/a,Dhaka-1205, Bangladesh', '01855342357'),
('03', 'Babor Ali', '[email protected]', 'north bhasantek(north side of cmh), p.s. # kafrul, dhaka cantonment, 1206, Bangladesh', NULL);
Run the following SELECT statement to read all records of the employees table.
The following output will appear after executing the above statement.
Performance of VARCHAR and TEXT
It has been mentioned before that the TEXT data type works faster than the VARCHAR data type. You have to select the database containing the tables with the VARCHAR and TEXT data type field and enable the profiling of the current MySQL SESSION to check which data type is faster between VARCHAR and TEXT.
The database has been selected here at the time of table creation. So, we don’t need to select it again. Run the following SET statement to enable SESSION Profiling.
Run the following SELECT query to read all records of the customers table.
Run the following SELECT query to read all records of the employees table.
Run the following command to check the performance of the executed above two SELECT queries.
The following output will appear after executing the SHOW PROFILES command. According to the output, the SELECT query for the customers table contains the address field of VARCHAR data type required 0.00101000 seconds and the SELECT query for the employees table containing the address field of TEXT data type required 0.00078125 seconds. It proves that the TEXT data type works faster than the VARCHAR data type for the same data.
You can compare the output of the following two queries to check the performance of VARCHAR and TEXT data types in detail.
SELECT * FROM INFORMATION_SCHEMA.PROFILING WHERE QUERY_ID=2;
Conclusion:
Both VARCHAR and TEXT data types are important for creating tables in the MySQL database. The differences between these data types have been appropriately explained in this tutorial with examples to help the MySQL users select the correct data type of the table’s field at the time of table creation.
