skip to content

Types & Online DDL

Interviewers hand you a table to design or alter in MySQL: INT sizes, VARCHAR vs TEXT, DATETIME vs TIMESTAMP, utf8mb4, and how to change a big table without an outage.

questions

6

In MySQL, what is the practical difference between a VARCHAR(n) column and a TEXT column, and when do you choose each?

level: juniorimportance: must knowfreq 72%

answer

  1. both variable-length, different families
  2. characters, not bytes
  3. the shared 65,535-byte row limit
  4. index prefix and expression default
  5. strict mode rejects overlong values

basics

~20 s

In MySQL, VARCHAR(n) holds up to n characters, can be indexed whole and takes a literal DEFAULT; TEXT is a large-object type that needs a prefix length to be indexed and only an expression default. Choose VARCHAR for bounded, searchable strings.

solid answer

~40 s

Both are variable-length, so neither pads short values. `VARCHAR(n)` counts characters, stores a 1- or 2-byte length prefix and counts its full maximum against the 65,535-byte row limit that all columns share; it can be indexed whole, made `UNIQUE` and given a literal `DEFAULT`. `TEXT` holds up to 65,535 bytes (`MEDIUMTEXT` and `LONGTEXT` go further), contributes only 9 to 12 bytes to that row limit, needs a prefix length such as `customer_note(64)` to be indexed, and accepts only an expression default like `DEFAULT ('')`. So I use `VARCHAR` for emails, references and statuses I filter, join or constrain, and `TEXT` for free-form bodies I mostly read back. With MySQL 8.4's default `sql_mode`, an overlong value for an InnoDB column fails with error 1406 instead of being truncated.

code

sql · 8 lines
sql
CREATE TABLE orders (
  id BIGINT UNSIGNED NOT NULL AUTO_INCREMENT PRIMARY KEY,
  order_ref VARCHAR(32) NOT NULL,
  status VARCHAR(20) NOT NULL DEFAULT 'pending',
  customer_note TEXT NOT NULL DEFAULT (''),
  UNIQUE KEY uq_order_ref (order_ref),
  KEY ix_note_prefix (customer_note(64))
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

go deeper

for a junior

Recall that n counts characters, that TEXT needs a prefix length to be indexed, and that VARCHAR is the type for short strings you search or constrain.

for a middle

Explain the 65,535-byte row limit and why TEXT escapes it, the 1- or 2-byte length prefix, the expression-only default for TEXT and how strict mode decides between error 1406 and truncation.

for a senior

Show you check @@sql_mode on real servers, size VARCHAR lengths with the character set's byte width in mind, and know which widening changes the length prefix and therefore needs a table copy.

for a principal

Argue for a team convention on string lengths and strict mode, weighing honest bounds that document intent against the cost of widening a hot table later.

## Two string types that look alike MySQL offers two families for character data of varying length. **`VARCHAR(n)`** is a variable-length string whose declared `n` is the maximum number of **characters**, not bytes. The **`TEXT` family** (`TINYTEXT`, `TEXT`, `MEDIUMTEXT`, `LONGTEXT`) holds character data as a large object, the character-set twin of the `BLOB` types. Neither pads a short value: a three-letter status in a `VARCHAR(20)` column stores three characters plus a length prefix, and so does the same string in a `TEXT` column. The differences that matter in an interview are not about wasted space. They are about **limits, indexing and defaults**, and each one follows from TEXT being treated as a large object. ## How each is stored and limited | | `VARCHAR(n)` | `TEXT` | |---|---|---| | What `n` means | maximum characters | no `n` needed; `TEXT` holds up to 65,535 bytes | | Length prefix | 1 byte if values need at most 255 bytes, else 2 | 2 bytes for `TEXT` | | Counted toward the 65,535-byte row limit | its full maximum byte length | only 9 to 12 bytes | | Literal `DEFAULT 'x'` | allowed | rejected; only an expression `DEFAULT ('x')` | | Index | whole column, if within the key-length limit | needs a prefix length | Two details trip people up: - **The row limit counts the maximum, not the data.** MySQL's internal row format caps a table definition at **65,535 bytes** shared across all columns, and a `VARCHAR` counts at its widest. In `utf8mb4`, where a character can take 4 bytes, a `VARCHAR(20000)` needs up to 80,000 bytes and the `CREATE TABLE` fails with **error 1118, Row size too large**. Changing the column to `TEXT` fixes it, because large objects contribute only 9 to 12 bytes. - **Character sets change the byte math.** `VARCHAR(255)` in `utf8mb4` may need up to 1,020 bytes, so it carries a 2-byte length prefix, while `VARCHAR(60)` (at most 240 bytes) carries a 1-byte prefix. That boundary matters again when you later widen the column with online DDL. InnoDB also has its own, smaller per-page row limit and moves long variable-length values off-page; how it does that belongs with InnoDB's row formats rather than here. ## Indexing and defaults - **Indexes.** A `VARCHAR` column can be indexed whole, made `UNIQUE` and used as a join key, as long as its byte length fits the index key limit (3,072 bytes for InnoDB's default `DYNAMIC` row format). A `TEXT` column must be given a **prefix length**, for example `KEY ix_note (customer_note(64))`, so only the first 64 characters are indexed and a `UNIQUE` index on it would only enforce uniqueness of that prefix. - **Defaults.** `status VARCHAR(20) NOT NULL DEFAULT 'pending'` is ordinary. For `TEXT`, MySQL accepts a default only when it is written as an expression, so `DEFAULT ('')` works and `DEFAULT ''` is an error. - **Uniqueness on a prefix.** Because a `TEXT` index covers only its prefix, it cannot enforce that whole long values are unique; if a long string must be unique, the usual workaround is a unique index on a stored hash of the value. ## What happens when a value is too long This is a MySQL-specific behaviour that interviewers like because it used to be a famous source of silent data loss. 1. With **strict SQL mode** on, an `INSERT` or `UPDATE` that puts an overlong value into an InnoDB column fails with **error 1406, Data too long for column**, and the statement rolls back. 2. With strict mode **off**, MySQL truncates the value to fit and raises only a warning, visible through `SHOW WARNINGS`. 3. MySQL 8.4's default `sql_mode` includes `STRICT_TRANS_TABLES`, so on a default install the first behaviour applies to InnoDB tables. Installers, option files and application drivers can override the mode, so check `SELECT @@sql_mode` rather than assuming. The same rule covers `TEXT`: a value larger than the type's maximum errors in strict mode and is truncated with a warning otherwise. ## Choosing between them A practical rule of thumb: 1. If the value has a natural bound and you **filter, join, sort or constrain** on it (email, order reference, SKU, status, country code), use `VARCHAR` with a realistic `n`. 2. If the value is **free-form and potentially long** (a customer note, a description, a rendered document) and you mostly read it back with the row, use `TEXT` or `MEDIUMTEXT`. 3. If a column is wide but must be searched by its content, a prefix index or a full-text index covers it; that is index design rather than type choice. Setting `n` honestly also documents intent: `VARCHAR(255)` everywhere hides which strings are short and which can grow, and makes later widening decisions harder to reason about.

  • Why can a utf8mb4 MySQL table reject several wide VARCHAR columns but accept the same columns as TEXT?
    MySQL caps a table's row definition at 65,535 bytes shared by all columns, and each VARCHAR counts at its maximum byte length, which in utf8mb4 is four bytes per character. A few VARCHAR(10000) columns exceed that and CREATE TABLE fails with error 1118, Row size too large. TEXT columns contribute only 9 to 12 bytes each, so the same table succeeds.
  • How does a MySQL server with sql_mode set to an empty string treat a 30-character value written into a VARCHAR(20) column?
    Without strict mode, MySQL truncates the value to its first 20 characters, stores the row and raises a warning that only SHOW WARNINGS reveals, so the application sees success. With MySQL 8.4's default mode, which includes STRICT_TRANS_TABLES, the same statement on an InnoDB table fails with error 1406 and rolls back.

  • VARCHAR(255) reserves 255 bytes in every row however short the value is
  • The n in VARCHAR(n) counts bytes, so utf8mb4 needs VARCHAR(4n)
  • A TEXT column can be indexed whole, exactly like a VARCHAR
  • A TEXT column takes a plain literal default such as DEFAULT ''
  • MySQL 8.4 silently truncates overlong strings by default
open as a page

In MySQL, how do DATETIME and TIMESTAMP differ in range, storage and time-zone handling, and which suits an order's placed-at column?

level: middleimportance: must knowfreq 66%

basics

~20 s

MySQL's TIMESTAMP converts values from the session time zone to UTC and back and only covers 1970 to 2038; DATETIME stores exactly what you give it from year 1000 to 9999. For order times, DATETIME written in UTC is usually the safer choice.

open as a page

In MySQL 8.4, what do ALTER TABLE's ALGORITHM=INSTANT, INPLACE and COPY mean, and why pin ALGORITHM and LOCK explicitly?

level: middleimportance: must knowfreq 60%

basics

~20 s

INSTANT changes only data-dictionary metadata, INPLACE works inside InnoDB and usually allows concurrent writes, and COPY rebuilds the table row by row while blocking writes. Pinning ALGORITHM and LOCK makes an ALTER fail immediately rather than silently fall back to a blocking copy.

open as a page

In MySQL, why should new tables use utf8mb4 rather than utf8mb3, and what does the default collation utf8mb4_0900_ai_ci do to comparisons?

level: middleimportance: should knowfreq 55%

basics

~20 s

MySQL's utf8mb3 stores at most three bytes per character, so emoji and other supplementary characters fail; utf8mb4 stores all of Unicode and is the default. Its default collation, utf8mb4_0900_ai_ci, compares case- and accent-insensitively, so 'Jose' equals 'josé'.

open as a page

An ALGORITHM=INSTANT ALTER TABLE on a busy MySQL orders table hangs, and within seconds every query on that table stalls; what is happening, and how do you recover and prevent it?

level: seniorimportance: should knowfreq 45%

basics

~20 s

The ALTER is waiting for an exclusive metadata lock that an open transaction on orders still holds, and MySQL makes every new query on the table wait behind that pending request. Kill the ALTER or the blocker, then retry with a short lock_wait_timeout.

open as a page

As the lead for a MySQL fleet, how do you decide whether a large-table change runs as native online DDL, through gh-ost or through pt-online-schema-change?

level: principalimportance: should knowfreq 35%

basics

~20 s

Use MySQL's native INSTANT DDL whenever the change allows it; for changes that rebuild or copy a large hot table, use gh-ost, which reads the binlog and can be throttled, or pt-online-schema-change, which uses triggers but handles foreign keys.

open as a page