MySQL CHAR Data Type

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

FeatureCHARVARCHAR
StorageFixed-lengthVariable-length
Max Length255 characters65,535 characters (row limit)
PerformanceFaster for fixed-size valuesMore efficient for varying-length data
PaddingRight-padded with spacesStores only actual length
Use CaseCodes, fixed-length fieldsNames, 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? 🚀

Soeng Souy

Soeng Souy

Website that learns and reads, PHP, Framework Laravel, How to and download Admin template sample source code free.

Post a Comment

CAN FEEDBACK
close