Skip to content
Tutorials Center
My learning

Topics

Lesson 8 of 75 ยท Data Types

String Types

Learn the MySQL string types: CHAR vs VARCHAR, the TEXT family, BINARY, VARBINARY and BLOB, plus length limits, row size and how bytes differ from characters.

Almost every table stores text: names, emails, titles, slugs, descriptions. MySQL gives you a whole family of string types, and the choice affects storage, indexing, comparison rules and even whether a CREATE TABLE statement is accepted at all. Picking VARCHAR(255) for everything works for a while, but understanding the options lets you avoid surprises later.

The types fall into two groups. Character strings (CHAR, VARCHAR, the TEXT family, ENUM, SET) hold text and obey a character set and a collation. Binary strings (BINARY, VARBINARY, the BLOB family) hold raw bytes with no character set at all. Character sets and collations get their own lesson (Character Sets and Collations). Here we focus on the types themselves.

Two families of strings

Character strings CHAR, VARCHAR, TEXT. They know about character sets.
Binary strings BINARY, VARBINARY, BLOB. Raw bytes, compared byte by byte.

Pick the family first, then the size.

1 / 2

CHAR and VARCHAR

CHAR(n) holds exactly n characters, where n is 0 to 255. If you store fewer, MySQL pads the value with spaces on disk and strips those trailing spaces again when you read it. VARCHAR(n) holds up to n characters and stores a small length prefix (one byte if the value fits in 255 bytes, otherwise two) followed by only the characters you actually stored.

SQL
CREATE TABLE country (
  code  CHAR(2)     NOT NULL,   -- always 2 letters: 'DE', 'US'
  name  VARCHAR(80) NOT NULL,   -- anywhere from 1 to 80 characters
  PRIMARY KEY (code)
);

Use CHAR for values that really have a fixed width: ISO country and currency codes, hash digests stored as hex, two-letter state codes. Use VARCHAR for everything of variable length. The old advice that CHAR is "faster" rarely matters on InnoDB, and padding wastes space when lengths vary.

The number is characters, not bytes

In VARCHAR(80) the 80 is a number of characters. How many bytes that needs depends on the character set. With utf8mb4, the default, each character takes 1 to 4 bytes: ASCII letters take 1, accented Latin letters usually 2, most Asian scripts 3 and emoji 4. MySQL reserves room for the worst case when it computes row and index limits, so a VARCHAR(255) column in utf8mb4 can occupy up to 1,020 bytes.

You can see the difference between characters and bytes with two functions. In MySQL, CHAR_LENGTH(x) counts characters and LENGTH(x) counts bytes. SQLite has no such pair, but you can get the same contrast by casting the text to a blob, because length() counts bytes for a blob and characters for text:

SQL Characters versus bytes (stand-in for CHAR_LENGTH and LENGTH)

Press Run (or Ctrl + Enter) to see the result here.

The output shows hello as 5 characters and 5 bytes, hรฉllo as 5 and 6, ๆ—ฅๆœฌ่ชž as 3 and 9, and the emoji as 1 and 4. This is why a "length 10" column can hold ten emoji in utf8mb4 but only three in the retired utf8mb3 character set, which cannot store them at all.

Trailing spaces

CHAR and VARCHAR treat trailing spaces differently from what you might expect. CHAR strips them on retrieval. Both types also take part in comparisons whose handling depends on the collation: old collations such as utf8mb4_unicode_ci use PAD SPACE (trailing spaces are ignored, so 'abc' = 'abc ' is true), while the modern utf8mb4_0900_* collations use NO PAD (they are significant). SQLite never ignores trailing spaces by default, so a stand-in shows the NO PAD behavior and RTRIM collation shows the PAD SPACE one:

SQL Trailing spaces in comparisons

Press Run (or Ctrl + Enter) to see the result here.

The first column is 0 and the second is 1. On a MySQL 8.x server with the default collation the behavior matches the first column. Remember it when you migrate older databases: two values that looked equal on an old server may be different on a new one.

The TEXT family

When values can be long, use a TEXT type. There are four sizes, and the number in the name is a limit in bytes, not characters:

Type Maximum size Length prefix
TINYTEXT 255 bytes 1 byte
TEXT 65,535 bytes (about 64 KB) 2 bytes
MEDIUMTEXT 16,777,215 bytes (16 MB) 3 bytes
LONGTEXT 4,294,967,295 bytes (4 GB) 4 bytes

TEXT(n) with a number is also accepted: MySQL silently picks the smallest TEXT type that can hold n characters. Most projects use plain TEXT for article bodies and comments, and MEDIUMTEXT when a single value might pass 64 KB (rendered HTML, imported documents).

TEXT columns behave differently from VARCHAR in several ways that catch people out:

  • You cannot index the whole column. An index on a TEXT column needs a prefix length, for example INDEX (body(100)). Full-text indexes are the exception (Full-Text Search).
  • A literal DEFAULT is not allowed. Since 8.0.13 an expression default such as DEFAULT ('') works, with the parentheses.
  • Large values may be stored off-page: InnoDB keeps a pointer in the row and the data elsewhere, so reading the column costs extra I/O. Selecting * on a table with big TEXT columns is therefore more expensive than it looks.

A rule of thumb: if the value is a short label, name, email or slug, use VARCHAR with a realistic limit. If it is free-form prose, use TEXT and keep it out of most queries.

Binary strings

BINARY(n) and VARBINARY(n) are the byte counterparts of CHAR and VARCHAR. n is measured in bytes, and comparison is byte by byte with no collation. BINARY(n) pads short values with 0x00 bytes (not spaces), and those pad bytes are not removed when you read. That makes it a good fit for fixed-size identifiers such as a 16-byte UUID or a 32-byte SHA-256 digest.

The BLOB family mirrors TEXT: TINYBLOB, BLOB, MEDIUMBLOB and LONGBLOB with the same four size limits. They suit small binary payloads such as thumbnails or serialized objects. For large files, most applications store the file in object storage (S3 or similar) and keep only a path or key in MySQL, because large blobs bloat backups and the buffer pool.

SQLite has a blob type and the hex() and unhex() functions, so you can inspect binary values live:

SQL Binary values as hexadecimal

Press Run (or Ctrl + Enter) to see the result here.

In MySQL you read such a column with HEX(digest) for display and write it with UNHEX('DEADBEEF') or the 0xDEADBEEF literal. Never try to print a binary column directly in a terminal: the bytes are not text.

Row size and the 65,535-byte limit

All non-BLOB, non-TEXT columns of a table share one limit: a row may not exceed 65,535 bytes in total. That is a limit of the MySQL server layer, so it applies to InnoDB tables too. In utf8mb4 a single VARCHAR column can therefore be at most VARCHAR(16383), because 16,383 ร— 4 = 65,532 bytes plus the length prefix. Two columns of VARCHAR(10000) in utf8mb4 will not fit together, and MySQL refuses the table:

SQL
CREATE TABLE too_wide (
  a VARCHAR(10000),
  b VARCHAR(10000)
) CHARACTER SET utf8mb4;
-- ERROR 1118 (42000): Row size too large. The maximum row size for the
-- used table type, not counting BLOBs, is 65535. This includes storage
-- overhead, check the manual. You have to change some columns to TEXT or BLOBs

TEXT and BLOB columns count only a few bytes toward the limit (9 to 12), because their data lives elsewhere. InnoDB has a second, tighter limit: a row stored in a 16 KB page must fit in about half a page, roughly 8 KB, so wide rows push long columns off-page automatically. The practical conclusions are simple. Do not declare VARCHAR(65535) "just in case". Choose a limit that matches real data, and use TEXT when you need more.

ENUM and SET, briefly

ENUM('draft','published') stores one value from a fixed list, and SET('a','b','c') stores any combination of listed values. Both are string types as far as you can see and numbers as far as storage goes. They have enough surprises to deserve their own lesson: ENUM and SET in Practice.

What ran live here

The TryIt blocks above used SQLite to show length, trailing-space comparison and binary values. The CHAR/VARCHAR declarations, the TEXT sizes, padding on disk and the row size error are MySQL server behavior, so they are shown as read-only code with the error text a real MySQL 8.4 server prints.

Check your understanding

Quick check ยท 4 questions

of 4 answered You scored / 4 ยท perfect!

  1. 1. In MySQL, what does the 80 in VARCHAR(80) count?

    VARCHAR(n) limits the number of characters. The bytes needed depend on the character set, up to 4 bytes per character in utf8mb4.
  2. 2. Which type fits a fixed 16-byte UUID stored in compact binary form?

    BINARY(16) holds exactly 16 bytes, compares byte by byte, and uses less space than the 36-character text form of a UUID.
  3. 3. Why can you not create two VARCHAR(10000) columns in one utf8mb4 table?

    Each utf8mb4 character can need 4 bytes, so each column reserves up to 40,000 bytes, and the two together pass the 65,535-byte row limit.
  4. 4. What is true about indexing a TEXT column?

    A normal index on TEXT or BLOB needs a prefix length. A FULLTEXT index is a different kind of index and works without one.

Summary

  • Character strings (CHAR, VARCHAR, TEXT) have a character set and a collation. Binary strings (BINARY, VARBINARY, BLOB) are raw bytes.
  • CHAR(n) is fixed width, VARCHAR(n) is variable, and n counts characters. utf8mb4 characters take 1 to 4 bytes.
  • TEXT has four sizes (255 bytes, 64 KB, 16 MB, 4 GB), cannot be fully indexed and is often stored off-page.
  • BINARY(n) pads with zero bytes and suits UUIDs and digests. Keep big files in object storage.
  • A row may not exceed 65,535 bytes outside BLOB and TEXT columns, so choose realistic limits.

Next: Date and Time Types.