A Complete Guide to the MySQL BIT

Ever needed to count how many bits are set to 1 in a binary number? MySQL’s BIT_COUNT() function does exactly that—it takes a numeric value, looks at its binary representation, and tells you how many bits are turned on.

This function is particularly useful when working with bitmask flags, permissions, or any system that stores data in compact binary formats. Instead of manually converting numbers to binary and counting bits, BIT_COUNT() gives you the answer in a single, efficient operation.

In this guide, we’ll break down how BIT_COUNT() works, explore its syntax, and walk through real-world examples to help you apply it effectively in your database queries.

Understanding the Syntax

The BIT_COUNT() function has a simple structure:

BIT_COUNT(number)

number: An integer (or an expression that evaluates to one) whose bits you want to count.

The function returns an unsigned integer representing the number of 1 bits in the binary form of the input.

How BIT_COUNT() Works

At its core, BIT_COUNT() converts the given number to its binary form and scans each bit, tallying up every 1 it encounters.

For example:

The number 5 in binary is 0101.
The number 3 in binary is 0011.

Running BIT_COUNT(5) returns 2 (two 1s), while BIT_COUNT(3) returns 2 as well.

Practical Use Cases

Counting Active Flags in a Bitmask

Suppose you have a table storing user permissions as bitmask values:

CREATE TABLE user_permissions (
    user_id INT,
    permissions INT
);

INSERT INTO user_permissions VALUES
(1, 5),  -- 0101 (Read + Execute)
(2, 3),  -- 0011 (Read + Write)
(3, 1);  -- 0001 (Read)

To find out how many permissions each user has:

SELECT
    user_id,
    BIT_COUNT(permissions) AS total_permissions
FROM user_permissions;

Result:

user_id | total_permissions
1       | 2
2       | 2
3       | 1

Detecting Dense Binary Data

If you’re working with compact binary-encoded data (like feature toggles), BIT_COUNT() helps measure how “dense” a value is—i.e., how many features are enabled.

SELECT
    BIT_COUNT(255) AS all_bits_on,      -- 11111111 (8 bits)
    BIT_COUNT(0) AS no_bits_on,         -- 00000000 (0 bits)
    BIT_COUNT(42) AS some_bits_on;      -- 00101010 (3 bits)

Output:

all_bits_on | no_bits_on | some_bits_on
8           | 0          | 3

Handling Negative Numbers

MySQL stores integers in two’s complement form, meaning negative numbers have leading 1s. BIT_COUNT() treats them just like positives, counting all 1 bits.

Example:

SELECT BIT_COUNT(-1);  -- 64 (in a 64-bit system, -1 is all 1s in binary)

Combining with Other Functions

Finding Users with Exactly Two Permissions

Using BIT_COUNT() in a WHERE clause:

SELECT user_id
FROM user_permissions
WHERE BIT_COUNT(permissions) = 2;

Result: Users 1 and 2 (both have two permissions).

Grouping by Bit Density

You can group records based on how many bits are set:

SELECT
    BIT_COUNT(status_flags) AS active_flags,
    COUNT(*) AS users
FROM system_status
GROUP BY active_flags;

Performance Considerations

Since BIT_COUNT() operates directly on integer values, it’s highly efficient—even on large datasets. However, applying it on unindexed columns in a WHERE clause may slow down queries.

Conclusion

MySQL’s BIT_COUNT() is a small but mighty function for working with binary data. Whether you’re analyzing permissions, feature flags, or encoded statuses, it provides a quick way to measure bit density without manual conversions.

Key takeaways:

Returns the number of 1 bits in a number’s binary form.
Works seamlessly with bitmasks, permissions, and binary-encoded data.
Efficient for filtering and grouping based on bit patterns.

Next time you encounter bitwise data, remember: BIT_COUNT() can save you time and simplify your queries.