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:

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:

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.

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.