Difference Between Char And Varchar In MySQL

MySQL is a relational database management system that utilizes SQL (Structured Query Language) for data manipulation. Within MySQL, two essential character storage types are CHAR and VARCHAR.

CHAR stores characters of fixed length, necessitating a predetermined length when defining the field for character storage. In contrast, VARCHAR accommodates variable-length characters, providing flexibility by eliminating the need to specify an exact character count.

CHAR is suitable for consistent-length data, such as codes or identifiers, while VARCHAR is preferable for storing text or descriptions with varying lengths. Choosing between these data types in MySQL depends on the specific data storage requirements and optimization for space or performance. Understanding the distinctions between CHAR and VARCHAR is crucial for effective data management in MySQL databases.

Refer to the Differences between Char and Varchar section for elaborated one-on-one difference between Char and Varchar in MySQL.

Char(n) Datatype

CHAR data type is used to store characters of fixed length. The n is referred to as the length specified at the time of creating the field where the characters will be stored. The number of characters stored in the field should not exceed the number of characters assigned at the time of creation(n). CHAR is used where the length of data is known.

Let's understand with a proper example.

Example

In this example, we have a structure of table Student which will contain some related fields like stu_name, mid_name, last_name, and the mid_name should not contain more than 1 character.

Code(Inserting data):

Code(Using Length)

Output:

Code(If insert data of length more than assigned):

Code(Using Length)

Output:

Explanation: In the above example, we have created a table STUDENT which has two fields stu_name, stu_mid_name and stu_id. The stu_mid_name field can accommodate characters up to size 1; hence, its initial size is also 1. As seen in the insertion code, the length of the field comes out to be 1 as the field contains M. In the next insertion, the data inserted Am contains more characters than the field can accommodate. Therefore, it throws an error that stated the data is too long for the specified column.

Varchar(n) Datatype

VARCHAR data type is used to store characters of variable length. The n is referred to as the upper limit of characters to be stored. The VARCHAR data type in MySQL stores data up to the size of 65,535 characters. VARCHAR is used where the length of data is unknown.

Let's understand with a proper example.

Example

In this example, we will have a table Student which will contain some related fields like stu_name and stu_id where the characters in the name of a student are not fixed.

Code(Inserting data)

Code(Using Length)

Output:

Explanation: In the above example, we have a table STUDENT which has two fields stu_name, and stu_id. The size of the stu_name field is not fixed therefore we have taken an upper limit of 255 characters. In the insertion code, the length of the field comes out to be 7 as the field contains Sushant.

Key Difference Between Char and Varchar

The main difference between Char and Varchar is how they store character data in a database. Char, which stands for “character,” is a fixed-length data type, meaning it always reserves a specific amount of storage space for each value, regardless of whether the actual data occupies that space entirely. For example, if you define a Char(10) column and store the word “apple” in it, it will still occupy 10 characters’ worth of space, with extra spaces padding the unused positions.

On the other hand, Varchar, short for “variable character,” is a variable-length data type. It only uses as much storage space as needed to store the actual data without padding. So, if you store “apple” in a Varchar(10) column, it will only occupy 5 characters’ worth of space, leaving the remaining space available for other data.

Differences Between Char and Varchar

Let's take a brief look at the CHAR Vs VARCHAR in the MySQL database by having a one-on-one comparison.

What are Some More SQL Datatypes?

Several data types in MySQL act as containers for different types of data like integer, binary, string, etc. There exist data types like Numerical data types, String data types, Date and Time data types, etc.

Let's discuss some of them:

• BINARY(size): BINARY data type in MySQL is same as CHAR. It stores binary byte strings of specified size which are passed as a parameter.
• TINYTEXT: TINYTEXT in MySQL is used to store strings with a maximum size of 255 characters.
• INT: INT in MySQL is used to store data of integer type.
• DATE: DATE in MySQL is used to store data in the format of the date(YYYY-MM-DD).