String Types — MySQL

MySQL String Types

MySQL's string types cover a wide spectrum: fixed-length character strings, variable-length strings, large text objects, binary data, and raw byte sequences. Choosing the right string type affects storage efficiency, query performance, and whether you need to worry about character encoding.

CHAR vs VARCHAR

CHAR and VARCHAR are the two fundamental character string types. They differ in how storage is allocated and how trailing spaces are handled.

Feature	CHAR(n)	VARCHAR(n)
Storage	Fixed: always n bytes (padded)	Variable: actual length + 1-2 bytes overhead
Max length	255 characters	65,535 bytes (row limit)
Trailing spaces	Padded on store, stripped on retrieve	Preserved exactly
Performance	Faster for fixed-length data	Better for variable-length data
Best for	Fixed codes: MD5 hashes, country codes, state abbreviations	Names, emails, URLs, descriptions

SQL

CREATE TABLE char_vs_varchar (
  -- CHAR: always stores exactly n bytes
  country_code CHAR(2)     NOT NULL,  -- 'US', 'DE', 'JP' -- always 2 chars
  status_code  CHAR(3)     NOT NULL,  -- 'ACT', 'DEL', 'SUS'
  md5_hash     CHAR(32)    NOT NULL,  -- hex MD5 is always 32 chars
  uuid         CHAR(36),              -- formatted UUID string

  -- VARCHAR: stores actual content + length bytes
  email        VARCHAR(254) NOT NULL,
  name         VARCHAR(100) NOT NULL,
  url          VARCHAR(2048),
  bio          VARCHAR(1000)
);

When CHAR is better than VARCHAR:

The value is always or almost always the same length (ISO country codes, state abbreviations, hex hashes)
The column is frequently updated — VARCHAR rows that grow require InnoDB to move the row, while CHAR rows stay in-place
The column is part of a composite index — fixed-width columns in indexes are more compact and cache-friendly

Note

The VARCHAR length limit (n) is in characters, not bytes. For utf8mb4 columns (where each character can use up to 4 bytes), VARCHAR(255) can use up to 1020 bytes. The per-row size limit of 65,535 bytes applies to the sum of all VARCHAR and CHAR columns together.

TEXT Types

TEXT types store large text values. They differ only in maximum size:

Type	Max Size	Length Prefix	Use for
TINYTEXT	255 bytes	1 byte	Short notes, tags (usually VARCHAR is better)
TEXT	65,535 bytes (~64 KB)	2 bytes	Articles, blog posts, descriptions up to 64KB
MEDIUMTEXT	16,777,215 bytes (~16 MB)	3 bytes	Long documents, generated HTML, code files
LONGTEXT	4,294,967,295 bytes (~4 GB)	4 bytes	Very large content (e-books, database dumps)

TEXT vs VARCHAR

TEXT and VARCHAR(65535) can store similar amounts of data, but they behave differently:

VARCHAR values are stored inline in the row (for short values), TEXT is always stored off-page with only a pointer in the row
VARCHAR columns can have DEFAULT values; TEXT columns cannot
VARCHAR columns can be fully indexed; TEXT columns require a prefix index (e.g., INDEX(bio(100)))
VARCHAR is faster for short to medium strings because the data lives with the row
TEXT is appropriate when content may be very long and won't be used in WHERE or ORDER BY clauses

SQL

CREATE TABLE articles (
  id          INT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
  slug        VARCHAR(200) NOT NULL UNIQUE,
  title       VARCHAR(500) NOT NULL,
  excerpt     VARCHAR(1000),          -- short preview, used in lists
  body        MEDIUMTEXT NOT NULL,    -- full article content
  created_at  TIMESTAMP DEFAULT CURRENT_TIMESTAMP,

  -- You can index a prefix of a TEXT column
  INDEX idx_slug (slug),
  FULLTEXT INDEX ft_body (title, body)
);

Tip

For columns that will be used in WHERE, ORDER BY, or GROUP BY clauses, use VARCHAR rather than TEXT. TEXT columns cannot be sorted without a full scan and cannot be used in indexes without a prefix length.

BINARY and VARBINARY

BINARY and VARBINARY are the binary equivalents of CHAR and VARCHAR. They store raw bytes rather than character strings — no character set encoding or collation applies.

SQL

CREATE TABLE binary_demo (
  -- BINARY: fixed-length byte string (like CHAR for bytes)
  file_hash   BINARY(16),    -- packed MD5 hash (16 bytes vs 32 chars)
  uuid_packed BINARY(16),    -- packed UUID (16 bytes vs 36 chars)

  -- VARBINARY: variable-length byte string (like VARCHAR for bytes)
  signature   VARBINARY(256),
  thumbnail   VARBINARY(65000)
);

-- Storing a packed UUID (more efficient than CHAR(36))
INSERT INTO binary_demo (uuid_packed)
VALUES (UNHEX(REPLACE('550e8400-e29b-41d4-a716-446655440000', '-', '')));

-- Retrieving the UUID as a formatted string
SELECT HEX(uuid_packed),
       INSERT(INSERT(INSERT(INSERT(HEX(uuid_packed), 9, 0, '-'), 14, 0, '-'), 19, 0, '-'), 24, 0, '-')
         AS formatted_uuid
FROM binary_demo;

Note

Storing UUIDs as BINARY(16) instead of CHAR(36) saves 20 bytes per row. For tables with millions of rows, this is significant — and the index on BINARY(16) is smaller and faster than an index on CHAR(36).

BLOB Types

BLOB (Binary Large Object) types store binary data of arbitrary length — images, files, serialized objects. They mirror the TEXT types in size limits:

Type	Max Size	TEXT Equivalent
TINYBLOB	255 bytes	TINYTEXT
BLOB	65,535 bytes (64 KB)	TEXT
MEDIUMBLOB	16,777,215 bytes (16 MB)	MEDIUMTEXT
LONGBLOB	4,294,967,295 bytes (4 GB)	LONGTEXT

Warning

Storing files in MySQL BLOB columns is generally a bad practice for anything beyond small thumbnails or icons. Files in the database balloon table sizes, make backups slow, don't benefit from CDN caching, and can't be served directly by a web server. Store files in S3 or a CDN and save the URL as VARCHAR.

Character Sets and Collations

Every string column in MySQL has a character set (which characters are supported) and a collation (how characters are compared and sorted). These can be set at the server, database, table, or individual column level — more specific settings override broader ones.

SQL

-- Set character set and collation per column
CREATE TABLE i18n_example (
  -- Case-insensitive comparison (default for most apps)
  name         VARCHAR(100) CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci,

  -- Case-sensitive comparison (for passwords, tokens, slugs)
  token        VARCHAR(64)  CHARACTER SET utf8mb4 COLLATE utf8mb4_bin,

  -- German-specific sort order (ä sorts with a, ö with o)
  german_name  VARCHAR(100) CHARACTER SET utf8mb4 COLLATE utf8mb4_de_pb_0900_ai_ci
);

-- Check the character set and collation of all columns in a table
SELECT COLUMN_NAME, CHARACTER_SET_NAME, COLLATION_NAME
FROM information_schema.COLUMNS
WHERE TABLE_SCHEMA = 'myapp' AND TABLE_NAME = 'i18n_example';

Collation and Case Sensitivity

SQL

-- With utf8mb4_unicode_ci (case-insensitive):
SELECT 'Alice' = 'alice';  -- returns 1 (true)
SELECT 'Alice' = 'ALICE';  -- returns 1 (true)

-- With utf8mb4_bin (binary/case-sensitive):
SELECT _utf8mb4'Alice' COLLATE utf8mb4_bin = 'alice';  -- returns 0 (false)
SELECT _utf8mb4'Alice' COLLATE utf8mb4_bin = 'Alice';  -- returns 1 (true)

Tip

If your email column uses a case-insensitive collation (ci), then WHERE email = 'Alice@Example.com' matches alice@example.com in the database. This is usually what you want for email lookups, but make sure to lowercase emails before storing them to normalize duplicates.

ROW_FORMAT and String Storage

InnoDB stores row data in different formats that affect how variable-length strings are stored:

ROW_FORMAT	VARCHAR/TEXT Storage	When to use
DYNAMIC (default)	Long columns stored off-page automatically	Modern default — handles mixed short/long columns well
COMPACT	First 768 bytes inline, rest off-page	Legacy format, slightly less efficient
REDUNDANT	Oldest format, most overhead	Very old tables, avoid for new schemas
COMPRESSED	Data compressed with zlib	Read-mostly tables where storage savings matter

SQL

-- Check a table's row format
SHOW TABLE STATUS LIKE 'articles'G

-- Explicitly set row format
CREATE TABLE documents (
  id   INT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
  body LONGTEXT
) ROW_FORMAT=DYNAMIC ENGINE=InnoDB;

String Performance Implications

Index length limits: InnoDB has a default maximum key length of 767 bytes (innodb_large_prefix allows 3072 bytes). VARCHAR(191) uses 764 bytes with utf8mb4 — safe for an index. VARCHAR(255) uses 1020 bytes — requires innodb_large_prefix enabled.
Sorting VARCHAR/TEXT: Sorting on TEXT columns requires a temporary file sort (filesort). Sort on VARCHAR columns that are indexed when possible.
LIKE with leading wildcard: <code>WHERE name LIKE '%smith'</code> cannot use an index and requires a full scan. <code>WHERE name LIKE 'smith%'</code> uses an index efficiently.
Full-text search: For searching inside text content, create a FULLTEXT index rather than using LIKE with wildcards.
JSON in TEXT: Avoid storing JSON as raw TEXT — use the JSON type which validates, compresses, and supports path extraction.

Full-Text Indexes on TEXT Columns

SQL

-- Create a table with full-text search capability
CREATE TABLE articles (
  id      INT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
  title   VARCHAR(500) NOT NULL,
  body    MEDIUMTEXT NOT NULL,
  FULLTEXT INDEX ft_search (title, body)
) ENGINE=InnoDB;

-- Full-text search query
SELECT id, title,
  MATCH(title, body) AGAINST('MySQL performance tuning' IN NATURAL LANGUAGE MODE) AS score
FROM articles
WHERE MATCH(title, body) AGAINST('MySQL performance tuning' IN NATURAL LANGUAGE MODE)
ORDER BY score DESC
LIMIT 10;

-- Boolean mode search
SELECT id, title FROM articles
WHERE MATCH(title, body) AGAINST('+MySQL +performance -slow' IN BOOLEAN MODE);

Note

MySQL's built-in full-text search is adequate for simple search needs on small-to-medium datasets. For production search with relevance tuning, facets, autocomplete, and multi-language support, use Elasticsearch or OpenSearch with MySQL as the source of truth.