CHAR, VARCHAR, TEXT, and BLOB

CHAR(M) (up to 255 characters) is space-padded on storage and stripped on retrieval. VARCHAR(M) stores exactly what you give it plus a 1- or 2-byte length, and M counts characters, so a utf8mb4 VARCHAR(100) may need 400 bytes. BINARY and VARBINARY hold bytes.

Padding, trailing spaces and the length limitSQL

CREATE TABLE pad (c CHAR(5), v VARCHAR(5));
INSERT INTO pad VALUES ('ab  ', 'ab  ');
SELECT CONCAT('[', c, ']') AS c, CONCAT('[', v, ']') AS v, c = 'ab' AS c_eq, v = 'ab' AS v_eq,
       CAST(v AS BINARY) = 'AB  ' AS bytes_eq FROM pad;
INSERT INTO pad (v) VALUES ('abcdef');

Output

+------+--------+------+------+----------+
| c    | v      | c_eq | v_eq | bytes_eq |
+------+--------+------+------+----------+
| [ab] | [ab  ] |    1 |    0 |        0 |
+------+--------+------+------+----------+
ERROR 1406 (22001): Data too long for column 'v' at row 1

v_eq is 0 because the default collation, utf8mb4_0900_ai_ci, is NO PAD: trailing spaces count (older PAD SPACE collations ignore them), so trim input in PHP. As bytes, ab and AB differ. A row's columns share 65,535 bytes, so two utf8mb4 VARCHAR(10000) columns fail with error 1118, "Row size too large". TEXT and BLOB live off the row, counting 9 to 12 bytes, and hold up to 255 bytes (TINY), 64 KB, 16 MB (MEDIUM) or 4 GB (LONG). Prefer VARCHAR for values you filter or sort, since TEXT can only be prefix-indexed (Prefix and Invisible Indexes).

MENU

CHAR, VARCHAR, TEXT, and BLOB