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 CHARVARCHAR

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.

Difference Between Char and Varchar: Char(n) Datatype

CHAR data type, or char datatype, is used to store characters of fixed length. The n is referred to as the length specified at the time of creating the field, the set length for the column, 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, to store a character string when values are consistently sized.

Let’s understand with a proper example.

Transform Your Career

Choose from our industry-leading programs designed for career success

NSDC Certified

Modern Software and AI Engineering Program

Master full-stack development with AI integration

12 MonthsDuration

AI-LedCurriculum

Career SupportSupport

+1000 more

Go to Program

NSDC Certified

Modern Data Science and ML with specialisation in AI

Advanced data science techniques with AI specialization

12 MonthsDuration

AI-LedCurriculum

Career SupportSupport

+1000 more

Go to Program

NSDC Certified

Advanced AIML with Specialisation in Agentic AI

Deep dive into AIML with focus on Agentic systems

12 MonthsDuration

AI-LedCurriculum

Career SupportSupport

+1000 more

Go to Program

NSDC Certified

DevOps, Cloud & AI Platform Engineering

Build and manage AI-powered cloud infrastructure

12 MonthsDuration

AI-LedCurriculum

Career SupportSupport

+1000 more

Go to Program

NSDC Certified

AI Engineering Advanced Certification by IIT-Roorkee

Premier AI engineering certification from IIT-Roorkee

3 MonthsDuration

AI-LedCurriculum

Career SupportSupport

Go to Program

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, also called the varchar datatype, is used to store characters of variable length. It can store character string data without padding the entered value to a fixed size. The n is referred to as the upper limit of characters to be stored, while the storage used depends on the actual length of the value entered. 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 and data values do not all have the same size.

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, and the column stores the entered string based on its length rather than padding it. In the insertion code, the length of the field comes out to be 7 as the field contains Sushant, so the storage size reflects the actual content length.

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, which can result in extra memory spaces when the stored value is shorter than the defined size. 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).

• CHAR data type is used to store characters of fixed length.

• VARCHAR data type is used to store characters of variable length.

• CHAR can store characters up to the length of 255.

• VARCHAR can store characters up to the length of 65,535.

• CHAR is used where the length of data is known.

• VARCHAR is used where the length of data is unknown.

• The primary difference is in how they handle storage: CHAR pads values with spaces to reach the defined length, while VARCHAR stores only the actual data without extra spaces.

• CHAR may offer slightly better performance for exact-length searches due to fixed-length storage, whereas VARCHAR may have a slight performance overhead due to variable-length storage.

• To store unicode characters, SQL commonly uses Unicode-specific string types instead of standard CHAR or VARCHAR.

• Unicode characters are typically stored with NCHAR or NVARCHAR, which are designed for international and special text.

• In SQL Server, large text is often handled with VARCHAR(MAX) rather than regular VARCHAR.