MySQL BIT Data Type: Width, Literals, and Bit Flags

MySQL BIT(M) stores an M-bit value, where M ranges from 1 to 64. Use it when one column needs to hold a bit field, such as several independent on/off flags. For one Boolean value, BOOLEAN may be clearer; for one mutually exclusive status such as pending, paid, or shipped, use an integer or ENUM instead of assigning unrelated states to bit positions. See MySQL’s BIT type reference.

Define a bit field

BIT defaults to BIT(1) when M is omitted. A value shorter than the declared width is left-padded with zeros. For example, storing b'101' in BIT(6) is equivalent to storing b'000101'.

CREATE TABLE user_settings (
    user_id INT PRIMARY KEY,
    feature_flags BIT(3) NOT NULL DEFAULT b'000'
);

INSERT INTO user_settings (user_id, feature_flags)
VALUES (1, b'000'), (2, b'010'), (3, b'110');

In this example, the three bit positions are independent flags. The second bit uses the decimal mask 2 (010 in three-bit form). Define and document a mask for each position in the application so the same bit is not assigned conflicting meanings.

Read and filter flags

Bit values in result sets are binary values. The mysql command-line client may display them in hexadecimal form according to its --binary-as-hex option. Use + 0 to evaluate a value as a number, or BIN() to inspect its binary representation. BIN() omits leading zeros, so LPAD() can restore the declared width for display.

SELECT user_id,
       feature_flags + 0 AS flags_decimal,
       LPAD(BIN(feature_flags + 0), 3, '0') AS flags_binary,
       ((feature_flags + 0) & 2) <> 0 AS second_flag_enabled
FROM user_settings
ORDER BY user_id;

Result:

The & operator tests a mask. This query returns only rows where the second flag is set:

SELECT user_id
FROM user_settings
WHERE ((feature_flags + 0) & 2) <> 0;

Set a flag

Use bitwise OR (|) with a mask to set a flag without changing the other bits. The numeric conversion makes the operation’s intent explicit:

UPDATE user_settings
SET feature_flags = (feature_flags + 0) | 1
WHERE user_id = 2;

The mask 1 sets the rightmost bit. MySQL also supports bitwise AND (&), XOR (^), shifts (<<, >>), and inversion (~); when mixing binary strings and numbers, the result type depends on how the operands are evaluated. See MySQL’s bitwise operator rules.

Bit literals

Use b'...' to write a bit literal containing only 0 and 1:

SELECT b'101' + 0 AS decimal_value,
       BIN(b'101' + 0) AS binary_value;

This returns decimal 5 and binary text 101. A bit literal shorter than the column width is padded with zeros when stored. For more on result-set display and conversion, see MySQL’s bit-value literal documentation.

Choose BIT only when its behavior fits

Use BIT(M) when bit-level storage or bitwise flag operations are useful. Use an integer when the value is a number that applications routinely add, sort, or expose as a conventional numeric field. Use BOOLEAN/BOOL for a single true/false attribute, or an ENUM or lookup table for a mutually exclusive named status. MySQL implements BOOLEAN and BOOL as TINYINT(1) aliases; see the Boolean type guide.