Difference Between CHAR vs VARCHAR in MySQL

MySQL is a widely used relational database management system (RDBMS) that provides a robust and scalable platform for managing and organizing data. MySQL is an open-source software developed by Oracle Corporation, that provides features for creating, modifying, and querying databases. It utilizes Structured Query Language (SQL) to interact with databases, making it a popular choice for web applications and various software systems. MySQL’s versatility, reliability, and ease of use make it a preferred solution for developers and organizations seeking efficient data management capabilities.

In this article, you will learn about, what is the difference between char and varchar in MySQL.

String Data Type in MySQL

In MySQL, string data types are used to store character data, such as letters, numbers, and symbols. These data types are used for columns that will store textual information like names, addresses, descriptions, etc. Among the most commonly used string data types are CHAR, VARCHAR, TEXT, and BLOB.

Example

CREATE TABLE Employee (
    Name CHAR(20)
);

INSERT INTO Employee (Name) VALUES ('John');

Output:

Query output

In the above, column Name is a string data type.

Example

CREATE TABLE Customer (
    Email VARCHAR(50)
);

INSERT INTO Customer (Email) VALUES ('example@email.com');

Output:

Query output

Here, column Email is also string datatype.

string datatypes in MySQL

CHAR datatype in MySQL

In MySQL, the CHAR data type is used to store fixed-length character strings. When you define a column with the CHAR data type, you specify a fixed length for the data that will be stored in that column.

Example 1: Suppose you have a table named Employees, and you want to store the names of employees. You decide to use the CHAR data type for the Name column, with a fixed length of 20 characters. Here’s how you would create the table:

CREATE TABLE Employees (
    EmployeeID INT AUTO_INCREMENT PRIMARY KEY,
    Name CHAR(20)
);

The Name column is defined as CHAR(20), meaning it can store strings with a maximum length of 20 characters. If you insert a string shorter than 20 characters, MySQL pads it with trailing spaces to fill the fixed length.

For instance,

INSERT INTO Employees (Name) VALUES ('John');

Output:

CHAR Datatype output

After executing this insertion, the string ‘John‘ would be stored as ‘John‘ followed by 15 spaces to fill the remaining length of 20 characters.

Similarly, if you insert a longer string than the specified length, MySQL will truncate the string to fit the defined length.

Example

INSERT INTO Employees (Name) VALUES ('Elizabeth Smith');

Output:

Query output

In this case, ‘Elizabeth Smith’ would be truncated to ‘Elizabeth Smith‘ with trailing spaces added to reach the length of 20 characters.

From above we notice that CHAR columns are space-padded to their defined length, which can lead to wasted storage space if the actual data stored is shorter than the specified length. Therefore, CHAR is typically used for columns where the data length is consistent across all entries, such as fixed-length codes or identifiers.

VARCHAR in MySQL

In MySQL, the VARCHAR data type is used to store variable-length character strings. The VARCHAR data type specify a maximum length for the data that can be stored in that column.

Example: Suppose you have a table named Customers, and you want to store email addresses of customers. You decide to use the VARCHAR data type for the Email column, with a maximum length of 50 characters.

CREATE TABLE Customers (
    CustomerID INT AUTO_INCREMENT PRIMARY KEY,
    Email VARCHAR(50)
);

In this example, the Email column is defined as VARCHAR(50), meaning it can store strings with a maximum length of 50 characters. Unlike CHAR, VARCHAR does not pad spaces. It only uses as much space as needed to store the actual string.

For instance,

INSERT INTO Customers (Email) VALUES ('example@email.com');
between

Output:

VARCHAR output

In this case, the email address ‘example@email.com’ would be stored as is, occupying only the necessary space to store the string without any padding.

If you attempt to insert a string longer than the specified maximum length, MySQL will truncate the string to fit the defined length.

INSERT INTO Customers (Email) VALUES ('very_long_email_address@example.com');

In this case, ‘very_long_email_address@example.com‘ would be truncated to fit the maximum length of 50 characters.

VARCHAR columns are suitable for storing strings with varying lengths, such as email addresses, names, or descriptions. They are more space-efficient compared to CHAR columns because they only consume as much space as required by the actual data length.

CHAR vs VARCHAR in MySQL

Features	CHAR	VARCHAR
Definition	Fixed-length character string	Variable-length character string
Storage	Fixed length, padded with spaces if needed	Variable length, no padding
Storage Efficiency	Less efficient for variable-length data	More efficient for variable-length data
Maximum Length	Must be specified	Must be specified
Usage	Suitable for fixed-length data	Suitable for variable-length data
Example	CHAR(10)	VARCHAR(255)
Example Usage	Social security numbers, postal codes	Names, email addresses, descriptions

Conclusion

The purpose of CHAR in MySQL is different from VARCHAR. The purpose of CHAR is to store data in a fixed-length format for consistency. The purpose of VARCHAR in MySQL is to store content in a variable-length format for data consistency.

When it comes to storing data in MySQL, you have two options: CHAR or VARCHAR. CHAR stores data in fixed-size formats for consistency. VARCHAR stores data in variable-length formats for data consistency. When it comes to choosing the right format for MySQL, you have to consider the trade-off between data consistency and storage efficiency.