MySQL provides multiple ways to store binary or boolean-like data. Although these types appear similar, they differ in representation, storage size, retrieval behavior, and usage.

BIT(M) — How It Works

1
BIT(M) stores raw bitfields, where M ranges from 1 to 64.
2
Storage is in binary form, packed into bytes (M bits ≈ CEIL(M/8) bytes).
3
Retrieval returns binary output, often represented as bytes (e.g., \0\1).
4
BIT values do NOT behave like numbers unless explicitly converted using BIN(), HEX(), or CAST(... AS UNSIGNED).
5
Useful for flags, bitmasks, and compact boolean arrays.

BOOLEAN / BOOL

1
BOOLEAN is just an alias for TINYINT(1).
2
Stored internally as a 1-byte integer (0 or 1).
3
Retrieval returns numeric values (0,1) — not binary representation.
4
Behaves like an integer in comparisons and sorting.
5
Supports NULL unlike typical boolean types in other DBs.

TINYINT(1)

1
A numeric type storing 1 byte (range: -128 to 127 SIGNED, 0 to 255 UNSIGNED).
2
MySQL treats TINYINT(1) as boolean in some contexts but does NOT enforce boolean values.
3
You may store any numeric value within the allowed range.
4
Indexes behave like integer indexes — fast and numeric-based.

BINARY(M) and VARBINARY(M)

1
Store fixed-length (BINARY) or variable-length (VARBINARY) binary strings.
2
Not bit-level — store bytes, not individual bits.
3
Used for opaque data such as hashes, tokens, binary identifiers.
4
Retrieval returns raw bytes, not integers or boolean values.
5
Comparison is byte-by-byte with binary collation rules.

Key Differences: Storage & Representation

1
BIT(M) stores tightly packed bits → most compact for bit flags.
2
BOOLEAN/TINYINT(1) store numeric values → simplest to query and index.
3
BINARY/VARBINARY store binary strings → best for binary data, not bitwise logic.
4
BIT(M) retrieval produces binary output (\x01), while TINYINT/BOOLEAN return integers.

Retrieval Differences (Important)

1
BIT columns show escaped binary sequences unless converted.
2
TINYINT(1)/BOOLEAN display numeric values.
3
BINARY/VARBINARY show hex-like binary output depending on client settings.
4
BIT values require functions like BIN(), HEX(), or CAST() for readability.

Example: BIT vs TINYINT Retrieval

In this example, a appears as raw bytes, HEX(a) returns 0A, and b displays as 1.

When to Use Each Type

1
Use BIT(M) for bit flags and compact boolean arrays.
2
Use BOOLEAN/TINYINT(1) for logical true/false values needed in queries.
3
Use BINARY/VARBINARY for opaque binary data such as hashes and keys.
4
Avoid BIT(M) when readability is important — BIT requires conversions to interpret.

Difficulty: 5/10

Topics: storage, type conversion, schema design

Scenario Questions

0-2 years experience

1
You need to add a column to a user table to track whether the user has opted into email notifications. Would you choose BIT(1) or BOOLEAN, and why?
2
If you create a BIT(5) column and insert the value 31, what will be stored and how many bytes does MySQL allocate for that column?

2-5 years experience

1
A legacy service reads a BIT(1) column as a string '0' or '1' and fails when it receives '\x01'. How would you debug and fix the mismatch?
2
During a data migration you notice that a TINYINT(1) column used as a flag now contains values like 128 after importing from a BIT column. Explain why this happened and how to correct it.

5-8 years experience

1
Design a schema for a permissions table that needs to store up to 64 independent boolean flags per user. Discuss the trade‑offs between using a BIGINT, multiple BIT columns, or a VARBINARY(8) column.
2
Your application experiences a performance slowdown when filtering on a BIT(1) column. What indexing strategies or type changes would you consider, and what are the implications for storage and query plans?

8+ years experience

1
Your organization is moving from MySQL 5.7 to 8.0 and wants to standardize on a single boolean representation across dozens of services. How would you plan the migration from mixed BIT, BOOLEAN, and TINYINT(1) columns, ensuring backward compatibility and minimal downtime?
2
A cross‑team feature requires storing feature‑flags that may later need to support three‑state logic (true/false/unknown). How would you evolve the existing BIT(1) columns to accommodate this while preserving existing queries and indexes?

Follow-up Questions

❓What happens if you insert a value that exceeds the defined BIT length?
❓How does MySQL treat BIT columns in arithmetic or logical expressions?
❓Can you create an index on a BIT column, and what are the performance considerations?