MySQL Data Types

A data type defines what a column can store, how many bytes it uses, and which operations and comparisons apply.

Concept

A data type defines what a column can store, how many bytes it uses, and which operations and comparisons apply. MySQL has a rich set of types, including several that are MySQL-specific (TINYINT, MEDIUMINT, UNSIGNED, ENUM, SET, YEAR, the TEXT/BLOB families).

Why It Matters

Correct types keep data valid, reduce storage (smaller rows mean more rows per page in the buffer pool), speed up indexes, and avoid subtle bugs such as money rounding errors or the year-2038 TIMESTAMP limit.

Syntax

column_name TYPE[(length[, scale])] [UNSIGNED] [CHARACTER SET cs] [COLLATE coll] [NOT NULL] [DEFAULT ...]

Example

5.1 Integer types Flags, small codes, percentages Years, small counters, CAN DLC INT / INTEGER about ±2.1 billion Most IDs and counts about ±9.2 × 10^18

High-volume IDs, event logs, timestamps in ms INT(11) display widths are deprecated since 8.0.17 and do not limit values; write INT.

ZEROFILL is deprecated; format numbers at display time with LPAD().

BOOL/BOOLEAN is an alias for TINYINT(1): TRUE = 1, FALSE = 0.

In strict SQL mode, out-of-range values cause an error; in non-strict mode they are silently clipped to the limit with a warning.

5.2 Fixed-point and floating-point types DECIMAL(p,s) / NUMERIC(p,s) Exact, up to 65 digits

Money, quantities that must be exact ~7 significant digits (4 bytes) Approximate sensor values DOUBLE / REAL ~15 significant digits (8 bytes) Scientific/engineering calculations

FLOAT(M,D) / DOUBLE(M,D) syntax and UNSIGNED on DECIMAL/FLOAT/DOUBLE are deprecated.

5.3 String types Fixed n characters Trailing spaces removed on retrieval

65,535 bytes per row (shared by all columns) Actual length + 1–2 bytes Most text columns Descriptions, comments Long documents, logs Very large text BINARY(n) / VARBINARY(n) Like CHAR/VARCHAR, in bytes

Hashes, UUIDs (BINARY(16)), raw CAN payloads TINYBLOB / BLOB / MEDIUMBLOB / LONGBLOB 255 B / 64 KB / 16 MB / 4 GB Binary files (images, firmware)

n in VARCHAR(n) counts characters; with utf8mb4 each character can use up to 4 bytes, so the byte limit matters: VARCHAR(16383) is the maximum for a single utf8mb4 column.

TEXT/BLOB columns cannot have a normal default value except via expression defaults (8.0.13+), and indexes on them need a prefix length.

There is no separate NVARCHAR type in practice; NVARCHAR is accepted as a synonym for VARCHAR with the utf8 (utf8mb3) character set — use VARCHAR with utf8mb4 instead.

5.4 ENUM and SET Values per row Any combination (up to 64 members)

By index position in the definition, not alphabetically Caveats: adding values at the end of an ENUM is instant, but reordering or removing requires a table rebuild; ENUMs are MySQL-specific. A lookup table with a foreign key (or a VARCHAR with a CHECK constraint) is more portable and flexible.

5.5 Date and time types Time zone behavior None (can represent durations) None — stored exactly as given

1970-01-01 00:00:01 UTC … 2038-01-19 03:14:07 UTC Converted from session time zone to UTC on write and back on read

fsp = fractional seconds precision 0–6: DATETIME(3) stores milliseconds, DATETIME(6) microseconds — useful for CAN timestamps and test logs.

TIMESTAMP has the year-2038 limit; for dates that may be later (warranty expiry, contracts, long-term logs), use DATETIME.

Zero dates ('0000-00-00') are rejected under the default SQL mode (NO_ZERO_DATE); use NULL for unknown dates.

MySQL has no INTERVAL column type; INTERVAL is used only in expressions (d + INTERVAL 7 DAY).

5.6 BIT and BOOLEAN 5.7 JSON and spatial types JSON (5.7.8+): validated, stored in an optimized binary format; see Module 23.

Spatial: GEOMETRY, POINT, LINESTRING, POLYGON, and multi-types; see Module 25.

MySQL has no native UUID type. Options:

CHAR(36) works but uses more than twice the space and makes every secondary index larger (InnoDB secondary indexes store the primary key).

5.9 Choosing types — quick guide INT UNSIGNED / BIGINT UNSIGNED AUTO_INCREMENT DECIMAL(12,2) (or DECIMAL(19,4))

FLOAT/DOUBLE or DECIMAL(10,3) if exactness matters VARCHAR(100) / VARCHAR(255) Country / currency code CHAR(2) / CHAR(3) Phone number, PIN code VARCHAR(20) (never INT)

DATETIME(3) in UTC, or TIMESTAMP if within 1970–2038 Status with fixed values

Lookup table or VARCHAR + CHECK, or ENUM if MySQL-only TEXT / MEDIUMTEXT

Path in VARCHAR + file in object storage (or LONGBLOB if necessary)

CREATE TABLE int_demo (small_u TINYINT UNSIGNED, big BIGINT);
INSERT INTO int_demo VALUES (300, 1);
-- strict mode: ERROR 1264 (22003): Out of range value for column 'small_u' at row 1

SELECT 0.1e0 + 0.2e0 AS double_sum,            -- 0.30000000000000004
       0.1 + 0.2     AS decimal_sum;           -- 0.3 (literals with a decimal point are DECIMAL in MySQL)

CREATE TABLE test_runs (
    run_id     INT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
    result     ENUM('PASS', 'FAIL', 'BLOCKED') NOT NULL,     -- exactly one value
    buses_used SET('CAN', 'LIN', 'FLEXRAY', 'ETH') NOT NULL  -- zero or more values
);

INSERT INTO test_runs (result, buses_used) VALUES ('FAIL', 'CAN,ETH');
SELECT run_id, result, buses_used FROM test_runs WHERE FIND_IN_SET('CAN', buses_used);

CREATE TABLE can_frames (
    frame_id     BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
    captured_at  DATETIME(6)     NOT NULL,     -- microsecond precision
    can_id       INT UNSIGNED    NOT NULL,
    dlc          TINYINT UNSIGNED NOT NULL,
    payload      VARBINARY(64)   NOT NULL      -- classic CAN: 8 bytes; CAN FD: up to 64
);

INSERT INTO can_frames (captured_at, can_id, dlc, payload)
VALUES ('2026-03-10 10:00:00.123456', 0x1A0, 8, UNHEX('0FA00000C8000000'));

SELECT frame_id, captured_at, HEX(can_id) AS can_id_hex, HEX(payload) AS payload_hex FROM can_frames;

+----------+----------------------------+------------+------------------+
| frame_id | captured_at                | can_id_hex | payload_hex      |
+----------+----------------------------+------------+------------------+
|        1 | 2026-03-10 10:00:00.123456 | 1A0        | 0FA00000C8000000 |
+----------+----------------------------+------------+------------------+

CREATE TABLE ecu_flags (
    ecu_id      INT PRIMARY KEY,
    is_active   BOOLEAN NOT NULL DEFAULT TRUE,  -- TINYINT(1)
    status_bits BIT(8)  NOT NULL DEFAULT b'00000000'
);
INSERT INTO ecu_flags VALUES (1, TRUE, b'00101111');
SELECT ecu_id, is_active, BIN(status_bits) AS bits, status_bits & 1 AS bit0 FROM ecu_flags;
-- 1 | 1 | 101111 | 1

CREATE TABLE devices (
    device_id  BINARY(16) NOT NULL DEFAULT (UUID_TO_BIN(UUID(), 1)) PRIMARY KEY,   -- 16 bytes, time-ordered with swap flag
    name       VARCHAR(100) NOT NULL
);
INSERT INTO devices (name) VALUES ('Gateway-01');
SELECT BIN_TO_UUID(device_id, 1) AS device_id, name FROM devices;

Expected Result

SELECT CAST(0.1 AS DOUBLE) + CAST(0.2 AS DOUBLE) AS dbl, CAST(0.1 AS DECIMAL(5,2)) + 0.2 AS dec_sum;

+---------------------+---------+
| dbl                 | dec_sum |
+---------------------+---------+
| 0.30000000000000004 |    0.30 |
+---------------------+---------+

Common Mistakes

FLOAT/DOUBLE for money.

INT(11) believed to limit digits.

TIMESTAMP for dates after 2038.

VARCHAR(255) everywhere, including fixed-length codes.

Storing numbers or dates as strings.

CHAR(36) UUID primary keys in large InnoDB tables.

Relying on ENUM ordering or adding values in the middle.

Best Practices

Use UNSIGNED for values that are never negative (IDs, counts).

Use DECIMAL for money, DATETIME(3/6) for precise event times.

Keep primary keys small; they are copied into every secondary index.

Keep strict SQL mode on so invalid values raise errors.

Interview Relevance

High: "CHAR vs VARCHAR?", "DATETIME vs TIMESTAMP?", "What is the year-2038 problem?", "What is ENUM and its drawbacks?", "How do you store money?", "What does INT(11) mean?", "TEXT vs VARCHAR?".

Practice Questions

Choose MySQL types for: VIN, odometer reading in km, battery voltage (mV), DTC code, detection time with milliseconds, test result (PASS/FAIL/BLOCKED), firmware binary.

Why does TINYINT UNSIGNED reject 300 in strict mode? What happens in non-strict mode?

Create a table that stores CAN frames with microsecond timestamps and 8-byte payloads.

Store a UUID efficiently and display it in text form.

Explain what happens to a TIMESTAMP value when the session time zone changes.