MySQL CHAR Data Type

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

CREATE TABLE products (
    id INT AUTO_INCREMENT PRIMARY KEY,
    code CHAR(10) NOT NULL,
    category CHAR(20)
);

✔ 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

INSERT INTO products (code, category) 
VALUES ('P1234', 'Electronics');

Selecting Data

SELECT code, category FROM products;

✔ 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

CREATE TABLE countries (
    country_code CHAR(3) PRIMARY KEY,
    name VARCHAR(100)
);

Comparing CHAR Values (Trailing Spaces Ignored)

SELECT * FROM countries WHERE country_code = 'USA ';

✔ This matches 'USA' because MySQL ignores trailing spaces in CHAR comparisons.

Finding the Length of a CHAR Value

SELECT LENGTH(country_code) FROM countries;

✔ 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? 🚀