CHAR and VARCHAR

CHAR and VARCHAR store short text. The length you give is the maximum number of characters.

1
2
3
4
5
6

CREATE TABLE text_demo (
    country_code CHAR(2),
    email VARCHAR(255)
);
INSERT INTO text_demo VALUES ('IN', 'asha@example.com'), ('US', 'ben@example.org');
SELECT country_code, CHAR_LENGTH(country_code) AS code_len, email, CHAR_LENGTH(email) AS email_len FROM text_demo;

1
2
3
4
5
6
7

+--------------+----------+------------------+-----------+
| country_code | code_len | email            | email_len |
+--------------+----------+------------------+-----------+
| IN           |        2 | asha@example.com |        16 |
| US           |        2 | ben@example.org  |        15 |
+--------------+----------+------------------+-----------+
2 rows in set

When to use CHAR

For codes with a fixed length: country codes (IN), currency codes (INR), fixed-length hashes in hex (a SHA-256 hash in hex is CHAR(64)), postal codes in a country where all have the same length. For a fixed-length value, CHAR avoids the 1-byte length prefix and can be a bit faster.

When to use VARCHAR

Almost everywhere else: names, emails, titles, URLs, slugs.

The length is characters, not bytes

With utf8mb4, one character can take up to 4 bytes. VARCHAR(100) holds 100 characters of any kind. Behind the scenes MySQL may reserve up to 400 bytes when it plans memory and indexes.

1
2
3

CREATE TABLE short_text (word VARCHAR(5) NOT NULL);
INSERT INTO short_text VALUES ('hello');
INSERT INTO short_text VALUES ('namaste');

1

ERROR 1406 (22001): Data too long for column 'word' at row 1

Five characters fit, and seven do not. In strict mode MySQL refuses the second row instead of cutting it.

Does a bigger VARCHAR cost more?

For storage, not directly: VARCHAR(255) and VARCHAR(100) use the same disk space for the same text. But the maximum matters in these ways:

• Indexes. InnoDB indexes can be at most 3072 bytes per key. A VARCHAR(1000) in utf8mb4 (up to 4000 bytes) cannot be fully indexed, and MySQL refuses the index or asks you to use a prefix.
• Memory. Temporary tables and sorts may allocate the full maximum for each row.
• Meaning. The length is also a rule. An email cannot exceed 254 characters by standard, so VARCHAR(255) is a sensible limit. A name rarely needs more than VARCHAR(100).

Row size limit

All columns of a row share about 65,535 bytes. Several large VARCHAR columns can exceed it.

1

CREATE TABLE too_wide (a VARCHAR(20000));

1

ERROR 1074 (42000): Column length too big for column 'a' (max = 16383); use BLOB or TEXT instead

The error says the column is too big for utf8mb4 (4 bytes per character, 16,383 characters at most). Use TEXT for longer values (TEXT and BLOB).

Trailing spaces

1
2
3

CREATE TABLE padding (c CHAR(5), v VARCHAR(5));
INSERT INTO padding VALUES ('ab', 'ab'), ('ab   ', 'ab   ');
SELECT CONCAT('[', c, ']') AS char_value, CONCAT('[', v, ']') AS varchar_value, CHAR_LENGTH(c) AS c_len, CHAR_LENGTH(v) AS v_len FROM padding;

1
2
3
4
5
6
7

+------------+---------------+-------+-------+
| char_value | varchar_value | c_len | v_len |
+------------+---------------+-------+-------+
| [ab]       | [ab]          |     2 |     2 |
| [ab]       | [ab   ]       |     2 |     5 |
+------------+---------------+-------+-------+
2 rows in set

CHAR removes trailing spaces when you read it. VARCHAR keeps them. In comparisons, the MySQL 8 default collation treats 'ab' and 'ab ' as different (a “NO PAD” collation), while older collations such as utf8mb4_general_ci ignore trailing spaces (“PAD SPACE”):

1
2

SELECT 'ab' = 'ab ' AS default_collation,
       'ab' = 'ab ' COLLATE utf8mb4_general_ci AS general_ci;

1
2
3
4
5
6

+-------------------+------------+
| default_collation | general_ci |
+-------------------+------------+
|                 0 |          1 |
+-------------------+------------+
1 row in set