In MySQL, what is the practical difference between a VARCHAR(n) column and a TEXT column, and when do you choose each?
answer
- both variable-length, different families
- characters, not bytes
- the shared 65,535-byte row limit
- index prefix and expression default
- strict mode rejects overlong values
basics
~20 sIn 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 sBoth 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 linesCREATE 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
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.
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.
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.
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