What is the difference between CHAR and VARCHAR data types in MySQL? — MySQL Interview Question #9

In MySQL, ‘CHAR‘ and ‘VARCHAR‘ are two commonly used data types for storing character string data.

‘CHAR‘ is a fixed-length data type, which means that it always occupies the same amount of storage for every value, regardless of the content. For example, if you define a column as CHAR(10), it will always occupy 10 characters, even if the actual string stored in the column is shorter. If the string is shorter than the defined length, it will be padded with spaces until it reaches the defined length.

‘VARCHAR‘, on the other hand, is a variable-length data type. It means that the storage space for each value depends on the actual length of the string. For example, if you define a column as VARCHAR(10), it will occupy only the needed space plus one byte to store the length of the string. If the string is shorter than the defined length, it will not be padded with spaces.

In general, ‘VARCHAR‘ is more flexible than ‘CHAR‘, as it saves storage space for shorter strings and can accommodate a larger range of string lengths. However, ‘CHAR‘ may perform slightly better for fixed-length strings, as it requires less processing time to find the exact length of each value.

Here is an example to illustrate the difference:

Let’s create two tables, ‘char_table‘ and ‘varchar_table‘, with a column defined as ‘CHAR(5)‘ in the first one, and as ‘VARCHAR(5)‘ in the second one.

CREATE TABLE char_table (
   id INT PRIMARY KEY,
   name CHAR(5)
);

CREATE TABLE varchar_table (
   id INT PRIMARY KEY,
   name VARCHAR(5)
);

Now let’s insert some data into both tables:

INSERT INTO char_table (id, name) VALUES (1, 'John');
INSERT INTO varchar_table (id, name) VALUES (1, 'John');

If we select the data from both tables, we will see that the ‘name‘ column in ‘char_table‘ is padded with spaces to reach the defined length:

SELECT * FROM char_table;
+----+------+
| id | name |
+----+------+
|  1 | John |
+----+------+

While the ‘name‘ column in ‘varchar_table‘ is not padded, but only occupies the necessary space:

SELECT * FROM varchar_table;
+----+------+
| id | name |
+----+------+
|  1 | John |
+----+------+

In summary, the choice between ‘CHAR‘ and ‘VARCHAR‘ depends on the specific needs of your application. If you know that your string values will always be fixed-length, ‘CHAR‘ may be a better choice in terms of performance. However, if you need to store strings of varying lengths, ‘VARCHAR‘ is more appropriate and saves storage space.