Difference Between CHAR and VARCHAR in MySQL

In MySQL, CHAR and VARCHAR are two commonly used data types for storing string data. Although they seem similar, there are significant differences in their behavior, storage requirements, and usage scenarios.

---

1. Definition

• CHAR: A fixed-length string data type. It always stores strings with a fixed length, padding with spaces to the defined length if the string is shorter.
• VARCHAR: A variable-length string data type. It stores strings with lengths that can vary up to a defined limit.

---

2. Storage Behavior

Example: For a column defined as CHAR(10):

• A string abc is stored as abc (padded with 7 spaces).

For a column defined as VARCHAR(10):

• A string abc is stored as abc (with 1-2 bytes of metadata).

---

3. Length Handling

• CHAR: Length is always fixed as defined (e.g., CHAR(5) always uses 5 bytes, even for shorter strings).
• VARCHAR: Length varies with the actual content (e.g., VARCHAR(5) uses only 3 bytes for a 3-character string).

---

4. Performance

• CHAR: Faster for fixed-length operations because the storage size is constant, making retrieval predictable.
• VARCHAR: Can be slower for operations involving large numbers of variable-length rows due to the overhead of managing variable sizes.

---

5. Trimming Behavior

• CHAR: Removes trailing spaces automatically during retrieval.
• VARCHAR: Retains trailing spaces.

Example: If a CHAR(5) column contains abc, it will be retrieved as abc.
If a VARCHAR(5) column contains abc, it will be retrieved as abc.

---

6. Use Cases

---

7. Maximum Length

• CHAR: Maximum length is 255 characters.
• VARCHAR: Maximum length is 65,535 bytes (shared across the row).

---

8. Example Comparison

CREATE TABLE example_char (
    code CHAR(5)
);

CREATE TABLE example_varchar (
    name VARCHAR(50)
);

-- Insert data
INSERT INTO example_char (code) VALUES ('abc');
INSERT INTO example_varchar (name) VALUES ('abc');

-- Storage
-- In `example_char`, 'abc' is stored as 'abc  ' (padded).
-- In `example_varchar`, 'abc' is stored as 'abc' (no padding).

---

Key Takeaways

1. Use CHAR for:

   • Fixed-length data.
   • Small tables where retrieval speed is critical.

2. Use VARCHAR for:

   • Variable-length data.
   • Large tables with significantly different string lengths.

Choosing the right data type depends on your specific use case and the characteristics of your data.