CHAR vs VARCHAR data types in MySQL

Data types are used in RDBMS like MySQL to insert and store the specific data in the database. There are different data types used in MySQL to arrange data like INT, CHAR, VARCHAR, and DATE. In MySQL, tables contain columns; when a column is defined with a particular data type, it does not allow the data of other data types to be inserted in that column. Moreover, the data types classify the data based on the nature of the data and make it easier to manage and computate. In this post, we are going to explain the use and limitation of the CHAR and VARCHAR data types.

What is the CHAR data type in MySQL

The CHAR data type stores the characters and string of fixed length, it stores the value between 0 to 255. If you know the exact number of characters then you can use the CHAR data type and it should be noticed that the spaces will also be counted in the length of characters. To understand it, we will create a table with the following command:

CREATE TABLE customer_name (first_name CHAR(5), last_name CHAR(6));

In the above command, we have declared the first_name with CHAR data type with five characters and last_name with CHAR data type with six characters, to insert the value, run the command:

INSERT INTO customer_name VALUES (‘John’, ‘Paul’);

To display the table, run the command:

SELECT * FROM customer_name;

The name has successfully been updated, now we will insert the values which will be beyond the defined range of CHAR which is 5 for first_name and 6 for last_name:

INSERT INTO customer_name VALUES (‘Sophia’, ‘Alexander’);

In the above output, we can see that it has generated the error of range, we will create the table once again and assign it with the length of 256, as we told earlier the range of CHAR data type to store data is a maximum of 255, so it will also generate the error:

CREATE TABLE customer_names (first_name CHAR(256), last_name CHAR(6));

It generated the error of the maximum length and suggested using BLOB or TEXT instead of CHAR.

What is the VARCHAR data type in MySQL

The VARCHAR is another data type used in MySQL to store the data which is in the form of characters and strings. The length of VARCHAR data type to store data is up to 65535 (in version 5.0.3 and later), so a person can use it to define long strings and characters, moreover, it can store both the characters as well as integers. Let us create a table using the following command:

CREATE TABLE customer_name (first_name VARCHAR(200), last_name VARCHAR(200), address VARCHAR(1200));

To insert the values, run the command:

INSERT INTO customer_name VALUES (‘Sophia’, ‘Alexander’, ‘5000 P KERRYLYNN ALASKA, USA’ );

To view the value, execute the command:

SELECT * FROM customer_name;

The values have been inserted successfully in the table.

Comparison between CHAR and VARCHAR in MySQL

Both are the data types and used to store strings and characters in MySQL but the few differences are:

CHAR	VARCHAR
It is the representation of “character”	It is the representation of “variable character”
It stores characters between 0 to 255	It stores up to 65535 characters (3.0.5 and later versions)
It supports the static memory allocation	It stores the dynamic memory allocation

Conclusion

VARCHAR and CHAR both are the data types of MySQL and are used to store the characters. This write up focuses on discussing the comparison of both VARCHAR and CHAR data types in MySQL. Both have the same functionality but the difference is; CHAR data type stores data between 0 to 255 characters of fixed length and the VARCHAR data type can store characters up to 65535. It is recommended when you know the exact size of the characters to be inserted and they are in the range of CHAR data type, use it as its processes fast, on the other hand, if you do not know the exact size of characters or the size of characters is beyond the range of CHAR data type, then use VARCHAR data types.

CHAR vs VARCHAR data types in MySQL | Compared

What is the CHAR data type in MySQL

What is the VARCHAR data type in MySQL

Comparison between CHAR and VARCHAR in MySQL

Conclusion

About the author

Hammad Zahid