MySQL data types

Every table in MySQL is a fixed-width schema where each column carries one of these type declarations, and the engine uses the declaration to reserve storage, reject values that do not fit, and choose the fastest comparison path when a query filters or sorts on the column. The families you will meet on every production schema are numeric, string, date and time, JSON, and the boolean alias, and the rest of this page maps each family, its sizes, and the decisions that follow from them.

Once a schema is running, the type of a column shapes three daily tasks: reading data back with a client, watching the server with the MySQL Workbench walkthrough you keep on hand, and tuning queries when the optimizer picks the wrong plan. The choice of type also intersects with wider stack decisions, such as the trade-offs covered in Choosing between MySQL and PostgreSQL, and with the operational side of the machine, from logging to the moment you need to Kill a Process on Linux to free a wedged connection.

Numeric data types

Numeric types fall into 3 groups: integer, floating point, and exact decimal. Each integer size is offered in signed and unsigned variants, and the signed range always starts at a negative power of 2 and ends just below the next one.

For money, use DECIMAL(M,D) instead of FLOAT: a DECIMAL(10,2) stores 8 integer digits and 2 decimal places as an exact base-10 value, so 19.99 never drifts to 19.9899997. FLOAT is a 4 byte IEEE 754 number with about 7 digits of precision, DOUBLE is 8 bytes with about 15, and DECIMAL stores each digit in a packed base-1000 representation that costs 1 byte per 3 digits. The rule is simple: integers count, DECIMAL measures value, FLOAT and DOUBLE feed scientific or statistical columns where a small error is acceptable.

String data types

String types differ by whether they store a fixed or a variable length, and by how they encode characters. The four families in daily use are CHAR, VARCHAR, the TEXT ladder, and BLOB for binary payloads.

• CHAR(n) always pads to n characters, up to 255, and is a good fit for fixed codes such as ISO country codes
• VARCHAR(n) stores the actual length plus a 1 or 2 byte length prefix, up to 65535 bytes for the row
• TINYTEXT, TEXT, MEDIUMTEXT and LONGTEXT store 255, 65535, 16777215 and 536870911 bytes respectively
• BLOB, MEDIUMBLOB, LONGBLOB and TINYBLOB mirror those sizes for binary data such as images

A single row in InnoDB cannot exceed 65535 bytes across all its columns combined, so a schema with many large VARCHAR columns can hit the limit before any single column does. VARCHAR(255) under the utf8mb4 character set takes 765 bytes at the ceiling because each character can occupy up to 4 bytes, which is why 255 remains the default primary-key length that plays well with B-tree indexes. Use CHAR for values that genuinely do not vary in length and VARCHAR for everything else, because CHAR wastes the padding on storage and on reads.

Boolean storage in MySQL

MySQL stores a boolean as a 1 byte integer, not as a dedicated bit flag, and the engine exposes it through 2 aliases. BOOLEAN and BOOL are both declared synonyms of TINYINT(1), so a column declared BOOLEAN is physically a TINYINT that holds 0 or 1.

The display width in TINYINT(1) carries no storage cost: TINYINT(1) and TINYINT(4) both occupy 1 byte, and the (1) is only a hint that client tools such as the MySQL Workbench walkthrough screen should render the value as a check box. Two other types can hold per-row flags, and they differ sharply in cost. BIT(1) stores 1 bit per value, and a BIT(64) column packs 64 flags into 8 bytes, but comparisons on BIT columns behave oddly in some drivers because the result comes back as a binary string. ENUM('on','off') stores the value as 1 byte for up to 1024 members, which is more expressive but harder to migrate if the member list changes. For a yes/no column, TINYINT(1) with 0 and 1 remains the safest choice, and an index on it compresses well because the cardinality is only 2 distinct values.

JSON data type

The JSON type in MySQL 5.7 and later stores structured objects in a binary format on disk, and that format is what makes it faster than parsing a TEXT column on every read. Internally the engine sorts the keys of each object and keeps an offset table, so extracting one field reads only the bytes around that field instead of scanning the whole document.

Three capabilities define what a JSON column can do in a query. First, the operators and functions: JSON_EXTRACT, JSON_UNQUOTE, the -> and ->> shorthand, and JSON_CONTAINS for membership tests. Second, generated columns: you can declare a VIRTUAL or STORED column computed from a JSON field and index that generated column, which is the standard way to make a nested key queryable at the speed of a normal index. Third, the size ceiling: a JSON value can hold up to the value of max_allowed_packet, which defaults to 4 MiB on many server builds, so a JSON column can comfortably carry documents in the tens of kilobytes. The trade-off against a normalized set of child tables is query power: a child table gets full referential integrity and cheap joins, while a JSON column keeps related values together in one row at the cost of weaker constraints and a heavier row format. Use JSON for semi-structured data such as product attributes, event payloads, or configuration blobs, and use child tables for data other tables must join on.

Date and time types

Date and time types store calendar values in 4 to 8 bytes, and the difference between them comes down to precision and whether the value is relative to a time zone. The main types and their storage are laid out here.

The 2038 upper bound on TIMESTAMP is the point where the 4 byte Unix epoch count overflows on 32 bit systems, so a schema that must span that date should use DATETIME(6) with microsecond precision. TIMESTAMP values are stored as UTC and converted to the session time zone on read, while DATETIME values are stored exactly as written and carry no zone information, which is why an audit column is usually TIMESTAMP and a user-entered calendar entry is usually DATETIME. Fractional seconds on DATETIME and TIMESTAMP add 1, 2 or 3 bytes for the 1, 2 or 3 digits you declare, so DATETIME(6) costs 8 bytes per value. A practical schema uses DATE for birthdays and anniversaries, TIMESTAMP(6) for created_at and updated_at, and TIME only for durations or clock-of-day values.

Storage implications and choosing types

Choosing a type is choosing a storage and an index footprint, and the two effects compound because every secondary index stores the indexed column plus the primary key. The decisions that matter in practice are 4, and they line up from the smallest saving to the largest.

1. Match the integer size to the data: a counter that will never exceed 1000 fits in a SMALLINT and saves 2 bytes per row compared with INT
2. Cap VARCHAR at the real maximum: a VARCHAR(100) costs the same as a VARCHAR(500) until a row actually needs the bytes, but the cap protects the 65535 byte row limit and keeps the optimizer honest
3. Prefer TINYINT(1) or BIT over ENUM for flags, so a later requirement for a 3rd state does not force an ALTER that rewrites the table
4. Reserve JSON and LONGTEXT for payloads, and pull the fields you query into generated indexed columns

A row with a BIGINT, a VARCHAR(100) in utf8mb4, a DATETIME(6), a TINYINT(1) flag and a JSON field of a few hundred bytes lands near 500 bytes in a typical layout, and InnoDB packs rows into 16 KiB pages, so a page holds roughly 30 to 40 such rows. That arithmetic is what you run before you argue about a single column: the type choice multiplies by the row count and by the number of indexes, and a schema tuned on those numbers reads, sorts and joins faster with no code change at all. Keep the central column types conservative, keep the payloads in JSON, and the storage engine does the rest.

Where to go next

MYSQLMySQL in practiceThe Relational Database for Server-Side Web DevelopmentMYSQLMySQL Workbench walkthroughInstall, Configure, and Use the GUIMYSQLChoosing between MySQL and PostgreSQLWhich One Fits Your StackSYSADMINKill a Process on LinuxSignals, PID, and Stuck MySQL