Learning Focus

Use this lesson to understand CHAR with practical syntax and examples.

MySQL CHAR Data Type: Complete Guide

---

What is CHAR?

CHAR is a fixed-length string data type in MySQL, designed to store short, predictable-length text. It pads values with spaces to fill the defined length and trims trailing spaces when retrieving data.

Key Features

Feature	Details
Storage	Fixed length (e.g., CHAR(5) uses 5 bytes for latin1 encoding)
Max Length	255 characters
Collation	Follows table/column charset (e.g., utf8mb4)
Padding	Right-padded with spaces to match declared length
Comparison	Case-insensitive by default (depends on collation)

---

When to Use CHAR?

[OK] Best for:

• Fixed-length codes (e.g., country codes, abbreviations)
• Hashes/UUIDs (e.g., CHAR(32) for MD5)
• Flags or statuses with fixed sizes (e.g., 'Y'/'N')
• Columns with consistent lengths (e.g., CHAR(2) for US state codes)

[X] Avoid for:

• Variable-length text (use VARCHAR)
• Long text (use TEXT types)
• Multi-language text (ensure proper charset like utf8mb4)

---

Syntax & Declaration

Basic Syntax

column_name CHAR(M) [CHARACTER SET charset] [COLLATE collation]

• M: Length (1-255)

• Example:

  CREATE TABLE country (
      code CHAR(2),          -- 'US', 'IN', 'DE'
      currency CHAR(3)       -- 'USD', 'EUR', 'JPY'
  );
  

---

Storage & Padding

• Fixed Allocation: A CHAR(5) column always uses 5 bytes (for single-byte charsets) or 20 bytes (for utf8mb4, 4 bytes per char 5).

• Padding: Inserts spaces to fill unused length.

  INSERT INTO test (fixed_col) VALUES ('A');  -- Stored as 'A    ' (for CHAR(5))
  

• Retrieval: Trims trailing spaces automatically:

  SELECT fixed_col FROM test;  -- Returns 'A' (not 'A    ')
  

---

CHAR vs VARCHAR

Feature	CHAR	VARCHAR
Storage	Fixed (allocates full length)	Variable (+1-2 bytes for length)
Speed	Faster for frequent comparisons	Slightly slower
Use Case	Predictable lengths (e.g., codes)	Variable lengths (e.g., names)
Max Length	255	65,535

Example Comparison

-- CHAR(4) vs VARCHAR(4)
INSERT INTO table (char_col, varchar_col) VALUES ('AB', 'AB');
-- CHAR(4) uses 4 bytes, VARCHAR(4) uses 3 bytes (2 chars + 1 length byte)

---

Practical Examples

Example 1: Country Codes

CREATE TABLE countries (
    code CHAR(2) PRIMARY KEY,      -- 'US', 'GB', 'FR'
    name VARCHAR(50)
);

INSERT INTO countries VALUES ('JP', 'Japan');

Example 2: Fixed-Length Hashes

CREATE TABLE users (
    id INT PRIMARY KEY,
    api_key CHAR(32)  -- MD5 hash (32 characters)
);

Example 3: Status Flags

CREATE TABLE orders (
    id INT PRIMARY KEY,
    status CHAR(1)  -- 'P' (Pending), 'C' (Completed), 'F' (Failed)
);

---

Common Issues

1. Unexpected Truncation

INSERT INTO test (code) VALUES ('USA');  -- Fails if `code` is CHAR(2)

2. Collation Conflicts

-- Case-sensitive comparison:
SELECT * FROM table WHERE code = BINARY 'us';  -- 'US' = 'us'

3. Padding Mismatches

WHERE code = 'A';  -- Matches 'A    ' in CHAR(5)

---

Best Practices

1. Use for truly fixed-length data (e.g., CHAR(36) for UUIDs stored as strings).

2. Avoid over-sizing: CHAR(10) for 2-character codes wastes space.

3. Specify charset explicitly if non-default:

   password_token CHAR(64) CHARACTER SET ascii
   

4. Use VARCHAR for variable-length text to save storage.

---

Summary

• CHAR stores fixed-length strings with space padding.
• Best for: Short, predictable-length data (codes, flags).
• Watch for: Wasted space, charset/collation settings.

---

Next Topics

1. VARCHAR (variable-length strings)
2. BINARY/VARBINARY (binary byte storage)
3. String Functions (CONCAT(), SUBSTRING())

Concept Map

flowchart LR
    A[Schema Context] --> B[CHAR]
    B --> C[Query Pattern]
    C --> D[Validation]
    D --> E[Production Use]

Common Pitfalls

Pitfall	Consequence	Prevention
Executing queries without validating sample rows	Logic errors reach production data or reports	Start with SELECT ... LIMIT 10 and inspect edge cases
Ignoring NULL and duplicate behavior	Aggregations and filters return misleading results	Test with NULL, duplicates, and empty sets explicitly
Using advanced syntax before checking schema	Queries fail due to missing columns/indexes	Verify structure with DESCRIBE table_name; and adapt query design

Quick Reference

CREATE TABLE sample_char (id INT PRIMARY KEY, value CHAR);
DESCRIBE sample_table;
SHOW CREATE TABLE sample_table;

What's Next

• Next: Enum, Set - Continue to the next concept with incremental complexity.
• Module Overview - Return to this module index and choose another related lesson.