MySQL String Types and Character Sets

MySQL string columns have both a logical character limit and a physical byte representation. Keeping those ideas separate prevents many surprises with multilingual text and indexes.

CHAR and VARCHAR #

CHAR(n) is a fixed-length character string. MySQL pads values to its declared length for storage semantics, so it is best for genuinely fixed-width values such as country codes. VARCHAR(n) is variable length and is usually the better default for names, labels, and other text whose length varies.

In both cases, n is a maximum number of characters, not a number of bytes. A VARCHAR(10) column can therefore hold ten emoji or ten Chinese characters when its character set supports them, even though those characters use more than ten bytes on disk.

CREATE TABLE profile (
  country_code CHAR(2) NOT NULL,
  display_name VARCHAR(100) NOT NULL
) CHARACTER SET utf8mb4;

TEXT Types #

Use TINYTEXT, TEXT, MEDIUMTEXT, or LONGTEXT for larger variable-length text. Their limits are measured in bytes, so the practical character capacity depends on the column character set. TEXT columns can be indexed, but large or older MySQL configurations may require an index prefix; review the engine and MySQL version before designing an index around long text.

Character Sets and Collations #

A character set defines how characters are encoded as bytes. For new MySQL applications, utf8mb4 is generally the safe choice because it supports the full Unicode range. MySQL’s historical utf8 name can refer to a three-byte implementation and does not represent every Unicode character.

A collation defines comparison and sorting rules for a character set. It determines whether comparisons are case-sensitive or accent-sensitive and how strings sort. Character set and collation can be set at the server, database, table, or column level; explicit table or column settings make an application’s expectations clearer.

CREATE TABLE article (
  title VARCHAR(255) CHARACTER SET utf8mb4
    COLLATE utf8mb4_0900_ai_ci NOT NULL
);

Choose a collation based on the comparisons the application needs. For identifiers, a binary or case-sensitive comparison may be appropriate; for human-facing search, an accent- and case-insensitive collation may be preferable.

Binary String Types #

BINARY and VARBINARY store bytes, not text. They use the binary character set and collation, so MySQL does not transcode or apply linguistic comparison rules. BINARY(n) pads stored values with 0x00; VARBINARY(n) does not. Use BLOB types for larger byte sequences. Do not store encrypted data, digests, or protocol bytes in a character column because connection conversion and collation rules can alter their interpretation.

CREATE TABLE artifact (
  sha256 BINARY(32) NOT NULL,
  payload LONGBLOB NOT NULL,
  PRIMARY KEY (sha256)
);

Defaults, Connection Settings, and Indexes #

Character-set and collation defaults resolve from server, database, table, and column settings. A string literal also has a character set and collation. A connection declares character_set_client, character_set_connection, and character_set_results; use the driver’s utf8mb4 configuration or issue SET NAMES utf8mb4 for clients that do not configure it.

Inspect effective settings rather than assuming package defaults:

SHOW CREATE TABLE article;
SHOW VARIABLES LIKE 'character_set%';
SHOW VARIABLES LIKE 'collation%';

Index limits are measured in bytes. A VARCHAR(255) under utf8mb4 can require up to 1,020 bytes for character data, before index metadata. InnoDB supports large index keys in current MySQL releases, but a design must still account for the selected row format, page size, and MySQL version. Prefer an index over a bounded search key; use a prefix index only when its prefix remains selective.