Difference Between CHAR and VARCHAR in MySQL
CHAR reserves fixed space and pads with spaces; VARCHAR uses only what the value needs plus a length byte. Storage proof table and when each one wins.
The fixed box vs stretch bag analogy
CHAR(10) storing 'Aman' -> 'Aman ' (padded, 10 chars always)
VARCHAR(10) storing 'Aman' -> 'Aman' + 1 length byte (5 bytes total)Storage proof
CREATE TABLE demo (
a CHAR(10),
b VARCHAR(10)
);
INSERT INTO demo VALUES ('Aman', 'Aman');
SELECT LENGTH(a), LENGTH(b) FROM demo;(Note: MySQL strips the trailing spaces of CHAR when reading, so SELECT a shows 'Aman' — but the storage is still 10. LENGTH exposes the truth.)
| Value stored | CHAR(10) uses | VARCHAR(10) uses |
|---|---|---|
| 'A' | 10 chars | 1 + 1 = 2 bytes |
| 'Aman' | 10 chars | 4 + 1 = 5 bytes |
| 'AmanKumar!' | 10 chars | 10 + 1 = 11 bytes |
Interesting: for values that always fill the full width, CHAR is actually smaller (no length byte) and slightly faster (fixed positions, no length reading). That is exactly when CHAR wins.
Comparison table
| Point | CHAR(n) | VARCHAR(n) |
|---|---|---|
| Storage | Fixed n, space-padded | Actual length + 1-2 length bytes |
| Max n | 255 | 65,535 (row-size limits apply) |
| Best for | Values of one fixed length | Values of varying length |
| Speed | Slightly faster (fixed offsets) | Normal |
| Wasted space on short values | Yes (padding) | No |
Real column decisions (school database)
CREATE TABLE students (
roll_no INT PRIMARY KEY,
name VARCHAR(50), -- names vary: 'Om' to 'Ramakrishnan'
gender CHAR(1), -- always exactly 1: 'M'/'F'/'O'
pincode CHAR(6), -- Indian PIN: always exactly 6
mobile CHAR(10), -- always exactly 10 digits
address VARCHAR(200), -- wildly varying length
aadhar CHAR(12) -- always exactly 12
);The pattern is obvious once seen: always-same-length codes → CHAR; naturally-varying text → VARCHAR. A VARCHAR(1) for gender would waste a length byte on every row for nothing; a CHAR(200) for address would pad thousands of spaces.
'ab ' saved in CHAR comes back as 'ab', in VARCHAR as 'ab ' — a subtle difference that has broken many login comparisons in real applications.Fixed डिब्बा vs stretch थैली analogy
CHAR(10) में 'Aman' -> 'Aman ' (padded, हमेशा 10 chars)
VARCHAR(10) में 'Aman' -> 'Aman' + 1 length byte (कुल 5 bytes)Storage proof
CREATE TABLE demo (
a CHAR(10),
b VARCHAR(10)
);
INSERT INTO demo VALUES ('Aman', 'Aman');
SELECT LENGTH(a), LENGTH(b) FROM demo;(Note: MySQL पढ़ते समय CHAR के trailing spaces हटा देता है, तो SELECT a 'Aman' दिखाता है — पर storage फिर भी 10 है. LENGTH सच दिखाता है.)
| Stored value | CHAR(10) लेता है | VARCHAR(10) लेता है |
|---|---|---|
| 'A' | 10 chars | 1 + 1 = 2 bytes |
| 'Aman' | 10 chars | 4 + 1 = 5 bytes |
| 'AmanKumar!' | 10 chars | 10 + 1 = 11 bytes |
Interesting: जो values हमेशा पूरी width भरती हैं, उनके लिए CHAR असल में छोटा है (length byte नहीं) और थोड़ा तेज़ (fixed positions). वहीं CHAR जीतता है.
Comparison table
| Point | CHAR(n) | VARCHAR(n) |
|---|---|---|
| Storage | Fixed n, spaces से padded | असली length + 1-2 length bytes |
| Max n | 255 | 65,535 (row-size limits लागू) |
| Best for | एक fixed length वाली values | बदलती length वाली values |
| Speed | थोड़ा तेज़ (fixed offsets) | Normal |
| छोटी values पर waste | हां (padding) | नहीं |
असली column decisions (school database)
CREATE TABLE students (
roll_no INT PRIMARY KEY,
name VARCHAR(50), -- names बदलते हैं: 'Om' से 'Ramakrishnan'
gender CHAR(1), -- हमेशा exactly 1: 'M'/'F'/'O'
pincode CHAR(6), -- Indian PIN: हमेशा exactly 6
mobile CHAR(10), -- हमेशा exactly 10 digits
address VARCHAR(200), -- बेतहाशा बदलती length
aadhar CHAR(12) -- हमेशा exactly 12
);Pattern एक बार दिखा तो साफ है: हमेशा-same-length codes → CHAR; naturally बदलता text → VARCHAR. Gender के लिए VARCHAR(1) हर row पर बेकार का length byte खर्च करता; address के लिए CHAR(200) हज़ारों spaces pad करता.
'ab ' वापस 'ab' आता है, VARCHAR में 'ab ' — यह बारीक फर्क असली applications के कई login comparisons तोड़ चुका है.Frequently Asked Questions
What is the difference between CHAR and VARCHAR?
CHAR(n) always occupies exactly n characters, padding short values with spaces; VARCHAR(n) stores only the actual length plus 1-2 length bytes, with n as just the upper limit.
When should CHAR be used instead of VARCHAR?
For values that are always exactly the same length — gender CHAR(1), PIN code CHAR(6), mobile CHAR(10), Aadhaar CHAR(12) — where CHAR is both smaller and slightly faster.
What happens to trailing spaces in CHAR and VARCHAR?
CHAR strips trailing spaces when values are retrieved, while VARCHAR preserves them exactly as entered — a subtle difference that can break string comparisons.