# 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

| Aspect                 | CHAR                          | VARCHAR                          |
|------------------------|-------------------------------|----------------------------------|
| **Storage Size**       | Fixed size.                  | Dynamic size.                   |
| **Space Usage**        | Pads shorter strings with spaces to meet the defined length. | Uses only as much space as required for the string, plus 1-2 bytes for length metadata. |
| **Storage Efficiency** | Efficient for consistently sized data. | More efficient for variable-length data. |

**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

| **CHAR**                   | **VARCHAR**                  |
|----------------------------|------------------------------|
| **Best for fixed-length data**: Codes, abbreviations, or data with a predictable length, such as `ZIP codes`, `country codes`. | **Best for variable-length data**: Names, descriptions, email addresses, or any text where lengths can vary significantly. |
| Small tables with frequently accessed rows. | Large tables or columns with significantly varying string lengths. |

---

## 7. Maximum Length

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

---

## 8. Example Comparison

```sql
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.