Menu

MySQL TINYINT: Range, Size, and BOOLEAN Alias

Updated on

MySQL TINYINT stores signed or unsigned integers in one byte. The (M) display width does not change the range or storage size, and integer display widths are deprecated in MySQL 8.0.17 and later.

Range and storage

Definition Storage Minimum Maximum
TINYINT 1 byte -128 127
TINYINT UNSIGNED 1 byte 0 255

Use UNSIGNED when negative values are not valid and the larger nonnegative range is useful. If an inserted value is outside the range, strict SQL mode rejects it with an error; without strict mode, MySQL may clip it to the nearest endpoint and issue a warning. Keep strict mode enabled when out-of-range data should be rejected.

BOOLEAN and TINYINT(1)

In MySQL, BOOL and BOOLEAN are synonyms for TINYINT(1). The 1 is a deprecated display width, not a constraint: a TINYINT(1) column can store values other than 0 and 1. FALSE and TRUE are aliases for 0 and 1, and MySQL treats zero as false and nonzero values as true in boolean contexts.

If a column must contain only 0 or 1, add a CHECK constraint (enforced starting with MySQL 8.0.16):

CREATE TABLE users (
    user_id BIGINT PRIMARY KEY,
    is_subscribed BOOLEAN NOT NULL DEFAULT FALSE
        CHECK (is_subscribed IN (0, 1))
);

Store a small status value

Use TINYINT UNSIGNED when a status is a small nonnegative number. Add a constraint if only a defined set of values is valid:

CREATE TABLE tasks (
    task_id BIGINT PRIMARY KEY,
    task_name VARCHAR(100) NOT NULL,
    status TINYINT UNSIGNED NOT NULL DEFAULT 0
        CHECK (status IN (0, 1, 2))
);

Here, 0, 1, and 2 are allowed status codes. Document what each code means in the application or schema so the numeric values remain interpretable.

Display zero-padded values

ZEROFILL and integer display widths such as TINYINT(3) are deprecated in MySQL 8.0.17 and later. They do not provide a numeric range limit. Format values for display in application code, or use LPAD() when a SQL text representation is needed:

SELECT LPAD(CAST(7 AS CHAR), 3, '0') AS formatted_code;

This returns the text 007; it does not change the stored integer.

Further reading