MySQL CHAR Data Type: A Complete Guide
The CHAR data type in MySQL is used to store fixed-length character strings. Unlike VARCHAR, which stores variable-length strings, CHAR always takes up the defined storage space. This makes it ideal for storing data of consistent length, such as country codes, fixed-length identifiers, and status codes.
1. Understanding MySQL CHAR Data Type
CHAR Characteristics
- Fixed-length string storage (between 1 and 255 characters).
- Faster than VARCHAR for fixed-length values due to predictable storage.
- Consumes full defined space (e.g.,
CHAR(10)
always uses 10 bytes). - Removes trailing spaces automatically when storing and retrieving values.
2. Declaring a CHAR Column
✔ The code
column will always store 10 characters, even if the actual value is shorter.
✔ If category
is 'Electronics' (11 characters), it will still be stored in a CHAR(20) column with trailing spaces.
3. Storing and Retrieving CHAR Data
Inserting Data
Selecting Data
✔ MySQL removes trailing spaces automatically when retrieving CHAR
values.
4. CHAR vs VARCHAR: Key Differences
Feature | CHAR | VARCHAR |
---|---|---|
Storage | Fixed-length | Variable-length |
Max Length | 255 characters | 65,535 characters (row limit) |
Performance | Faster for fixed-size values | More efficient for varying-length data |
Padding | Right-padded with spaces | Stores only actual length |
Use Case | Codes, fixed-length fields | Names, descriptions, emails |
✔ Use CHAR for fixed-length fields and VARCHAR for varying-length text.
5. Best Practices for CHAR
✅ Use CHAR when all values have a fixed length (e.g., country codes, product codes).
✅ Avoid CHAR for long text fields; use VARCHAR instead.
✅ Consider CHAR for indexing because fixed-length storage improves performance.
✅ Remember trailing spaces are ignored in comparisons ('ABC '
= 'ABC'
).
6. Example Use Cases
Storing Fixed-Length Codes
Comparing CHAR Values (Trailing Spaces Ignored)
✔ This matches 'USA'
because MySQL ignores trailing spaces in CHAR comparisons.
Finding the Length of a CHAR Value
✔ Returns 3 bytes, even if stored as 'US '
.
7. Summary
- CHAR is ideal for fixed-length strings (e.g., country codes, fixed-length identifiers).
- Faster than VARCHAR for fixed-size fields.
- Always consumes defined space, padding with trailing spaces if needed.
- Trailing spaces are ignored in comparisons.
Would you like performance tuning tips for CHAR indexing? 🚀