BIT_OR, BIT_AND и BIT_XOR в SQL: как агрегировать битовые флаги

Иногда в базе хранят не отдельные признаки в отдельных столбцах, а целую пачку флагов внутри одного числа.

Например, права пользователя можно записать так:

• 1 — чтение;
• 2 — запись;
• 4 — удаление;
• 8 — экспорт.

Если у пользователя есть чтение и удаление, его маска будет равна 5, потому что:

1 + 4 = 5

На первый взгляд это выглядит странно: как одно число может хранить несколько прав сразу? Но идея простая: каждое право занимает свой бит. Один бит — один переключатель. Он либо включён, либо выключен.

И вот здесь обычные агрегаты вроде SUM уже не подходят. Складывать такие числа по группе чаще всего бессмысленно: если у двух строк стоит право на чтение, сумма даст 2, а это уже будет выглядеть как право на запись. Хотя на самом деле никакой записи там не было.

Для битовых масок нужны не арифметические агрегаты, а побитовые:

• BIT_OR — собирает все биты, которые встретились хотя бы где-то;
• BIT_AND — оставляет только биты, которые есть во всех строках;
• BIT_XOR — показывает биты, которые встретились нечётное число раз.

Эти функции особенно полезны для прав доступа, фич, флагов, технических признаков и компактных настроек.

Зачем вообще упаковывать флаги в число

Представьте таблицу прав:

CREATE TABLE permissions (
    user_id bigint,
    resource_id bigint,
    flags int
);

В столбце flags лежит не одно право, а сразу несколько.

Например:

INSERT INTO permissions (user_id, resource_id, flags)
VALUES
    (1, 101, 1),
    (1, 102, 3),
    (1, 103, 5);

Расшифруем значения:

1 = read
3 = read + write
5 = read + delete

То есть у пользователя на разных ресурсах разные наборы прав.

Теперь вопрос: какие права есть у пользователя хотя бы где-то?

Обычная сумма не подойдёт:

SELECT
    user_id,
    SUM(flags) AS wrong_mask
FROM permissions
GROUP BY user_id;

Для пользователя 1 сумма будет:

1 + 3 + 5 = 9

А 9 означает:

read + export

Получилась ерунда: право export появилось из-за сложения чисел, хотя его не было ни в одной строке.

Правильный инструмент здесь — BIT_OR.

BIT_OR: какие биты встретились хотя бы раз

BIT_OR проходит по всем значениям в группе и применяет побитовое ИЛИ.

Если бит был включён хотя бы в одной строке, он будет включён в результате.

SELECT
    user_id,
    BIT_OR(flags) AS effective_mask
FROM permissions
GROUP BY user_id;

Для наших данных:

1 = read
3 = read + write
5 = read + delete

Итоговая маска будет:

7 = read + write + delete

То есть BIT_OR отвечает на вопрос:

Какие права у пользователя есть хотя бы на одном ресурсе?

Это самый частый сценарий для битовых агрегатов: собрать итоговую маску прав из нескольких строк.

Как читать отдельный бит через &

После BIT_OR на руках снова обычное число. Чтобы проверить, включён ли конкретный бит, используют побитовое И — оператор &.

Например, бит удаления равен 4.

SELECT
    user_id,
    BIT_OR(flags) AS effective_mask
FROM permissions
GROUP BY user_id
HAVING (BIT_OR(flags) & 4) = 4;

Запрос вернёт пользователей, у которых право удаления есть хотя бы где-то.

Можно писать проверку и через сравнение с нулём:

SELECT
    user_id,
    BIT_OR(flags) AS effective_mask
FROM permissions
GROUP BY user_id
HAVING (BIT_OR(flags) & 4) <> 0;

Обе записи читаются нормально:

(BIT_OR(flags) & 4) = 4

означает:

В итоговой маске точно включён бит 4.

А запись:

(BIT_OR(flags) & 4) <> 0

означает:

После проверки бита 4 получился не ноль, значит бит включён.

Для одного конкретного бита оба варианта обычно дают один и тот же смысл.

Почему скобки вокруг битовой операции лучше ставить всегда

В выражениях с побитовыми операторами легко ошибиться глазами.

Поэтому лучше сразу привыкнуть писать так:

(mask & 4) = 4

А не так:

mask & 4 = 4

Даже если конкретная СУБД разберёт выражение так, как вы ожидаете, скобки делают намерение очевидным. Через полгода вы сами скажете себе спасибо.

Особенно это важно, когда выражение становится длиннее:

HAVING (BIT_OR(flags) & 4) = 4

Так сразу видно: сначала проверяем бит, потом сравниваем результат.

BIT_AND: какие биты есть у всех строк

BIT_OR спрашивает:

Есть ли бит хотя бы где-то?

А BIT_AND задаёт другой вопрос:

Есть ли бит во всех строках группы?

Например, у пользователя есть права на несколько ресурсов. Нужно понять, какие права гарантированно есть на каждом ресурсе.

SELECT
    user_id,
    BIT_AND(flags) AS common_mask
FROM permissions
GROUP BY user_id;

Представим такие значения:

7 = read + write + delete
3 = read + write
1 = read

Общий бит у всех трёх строк только один — read.

Результат:

1 = read

Потому что:

• read есть везде;
• write есть не везде;
• delete есть не везде.

BIT_AND удобно использовать для поиска общего знаменателя.

Пример: минимальные права отдела

Допустим, у каждого сотрудника есть набор прав, и мы хотим понять, какие права есть у всех сотрудников отдела.

SELECT
    e.dept,
    BIT_AND(p.flags) AS common_to_all
FROM employees AS e
JOIN permissions AS p
    ON p.user_id = e.id
GROUP BY e.dept;

Если результат равен 0, значит в отделе нет ни одного общего бита для всех строк.

Например:

common_to_all = 0

Это означает:

Нет такого права, которое было бы у всех записей в группе.

А если результат равен 1, значит у всех есть как минимум право чтения.

Поиск нарушителей через BIT_AND

BIT_AND хорош для проверки правил.

Например, договорились: у каждой записи пользователя должен быть включён бит 1, который означает активность.

Найдём пользователей, у которых хотя бы в одной строке этот бит отсутствует:

SELECT
    user_id
FROM permissions
GROUP BY user_id
HAVING (BIT_AND(flags) & 1) = 0;

Почему это работает?

BIT_AND(flags) оставляет только те биты, которые есть во всех строках пользователя.

Если бит 1 пропал из результата, значит где-то он был выключен.

То есть запрос отвечает на вопрос:

У каких пользователей бит 1 стоит не во всех строках?

Для проверок качества данных это очень удобный приём.

BIT_XOR: какие биты встретились нечётное число раз

BIT_XOR используют реже, но он тоже полезен.

Он включает бит в результате, если этот бит встретился нечётное число раз.

Пример:

SELECT
    user_id,
    BIT_XOR(flags) AS parity_mask
FROM permissions
GROUP BY user_id;

Допустим, у пользователя такие значения:

1
1
4

Бит 1 встретился два раза, то есть чётное число раз. В результате он погаснет.

Бит 4 встретился один раз, то есть нечётное число раз. В результате он останется.

Итог:

4

BIT_XOR можно использовать для контрольных проверок, поиска непарных значений и простых технических сверок.

Например, если одинаковые флаги должны приходить парами, BIT_XOR поможет найти то, что осталось без пары.

Три агрегата в одном запросе

Посмотрим на все три функции рядом.

SELECT
    user_id,
    BIT_OR(flags) AS any_bit_set,
    BIT_AND(flags) AS bits_in_every_row,
    BIT_XOR(flags) AS parity
FROM permissions
GROUP BY user_id;

Расшифровка:

• any_bit_set — всё, что встретилось хотя бы раз;
• bits_in_every_row — только то, что есть в каждой строке;
• parity — то, что встретилось нечётное число раз.

Можно запомнить так:

BIT_OR — объединение.

BIT_AND — пересечение.

BIT_XOR — нечётность.

Пример с фичами продукта

Битовые маски часто используют не только для прав, но и для фич.

Допустим:

1 = beta_ui
2 = dark_mode
4 = exports
8 = ai_tools

В таблице лежит, какие фичи включены у пользователей:

CREATE TABLE user_features (
    user_id bigint,
    country text,
    flags int
);

Хотим понять, какие фичи включены хотя бы у одного пользователя в каждой стране.

SELECT
    country,
    BIT_OR(flags) AS country_features
FROM user_features
GROUP BY country;

Теперь можно проверить, где включён экспорт:

SELECT
    country
FROM user_features
GROUP BY country
HAVING (BIT_OR(flags) & 4) <> 0;

Это вернёт страны, где хотя бы у одного пользователя есть фича exports.

А если нужно найти страны, где фича dark_mode включена у всех записей, используем BIT_AND:

SELECT
    country
FROM user_features
GROUP BY country
HAVING (BIT_AND(flags) & 2) <> 0;

Как сделать результат понятным человеку

Сама маска вроде 7 или 13 удобна машине, но человеку читать её тяжело.

Поэтому в отчётах часто сразу раскладывают маску на отдельные булевы признаки.

SELECT
    user_id,
    BIT_OR(flags) AS mask,
    (BIT_OR(flags) & 1) <> 0 AS can_read,
    (BIT_OR(flags) & 2) <> 0 AS can_write,
    (BIT_OR(flags) & 4) <> 0 AS can_delete,
    (BIT_OR(flags) & 8) <> 0 AS can_export
FROM permissions
GROUP BY user_id;

Результат будет примерно таким:

user_id | mask | can_read | can_write | can_delete | can_export
--------+------+----------+-----------+------------+-----------
1       | 7    | true     | true      | true       | false
2       | 3    | true     | true      | false      | false
3       | 9    | true     | false     | false      | true

Так уже понятно, что именно включено у каждого пользователя.

Не превращайте биты в магические числа

Главная проблема битовых масок — через некоторое время никто не помнит, что означает 4, 8 или 16.

Поэтому в проекте обязательно нужно хранить понятную раскладку.

Например, в документации:

1 = read
2 = write
4 = delete
8 = export
16 = admin

Или в отдельной таблице:

CREATE TABLE flag_dictionary (
    bit_value int PRIMARY KEY,
    code text NOT NULL
);

Данные:

INSERT INTO flag_dictionary (bit_value, code)
VALUES
    (1, 'read'),
    (2, 'write'),
    (4, 'delete'),
    (8, 'export'),
    (16, 'admin');

Так у команды будет единый справочник, а не набор загадочных чисел в разных местах приложения.

Один бит — один независимый признак

Битовые флаги хорошо подходят для независимых признаков.

Например:

• может читать;
• может писать;
• может удалять;
• может экспортировать;
• включён новый интерфейс;
• разрешены уведомления.

Каждый признак может быть включён или выключен независимо от других.

Но не стоит кодировать битами взаимоисключающие состояния.

Плохой пример:

1 = new
2 = processing
4 = done
8 = failed

Статус заказа не должен быть одновременно new и done. Для таких вещей лучше отдельный столбец:

status text

Или справочник статусов.

Биты хороши для набора независимых переключателей. Статусы, этапы и состояния лучше хранить отдельно.

Integer или bigint

Если флагов немного, часто хватает типа integer.

Но у integer ограниченное количество битов. Если флагов становится много, лучше взять bigint.

CREATE TABLE permissions (
    user_id bigint,
    flags bigint
);

Практическое правило:

• мало флагов — integer;
• много флагов или есть риск роста — bigint.

Не стоит пытаться запихнуть бесконечное количество признаков в одно число. Если флагов стало слишком много, возможно, модель данных уже просит отдельную таблицу.

Что происходит с NULL

Как и многие агрегаты, побитовые агрегаты игнорируют NULL.

Например, если в группе есть значения:

1
NULL
4

BIT_OR будет работать по значениям 1 и 4, а NULL пропустит.

Если же в группе нет ни одного нормального значения, результат будет NULL.

Поэтому иногда полезно использовать COALESCE.

SELECT
    user_id,
    COALESCE(BIT_OR(flags), 0) AS effective_mask
FROM permissions
GROUP BY user_id;

Так вместо NULL вы получите 0, то есть маску без включённых битов.

Работа с типом bit

В PostgreSQL побитовые агрегаты могут работать не только с целыми числами, но и с битовыми строками типа bit.

Например:

CREATE TABLE feature_bits (
    group_id bigint,
    flags bit(4)
);

Данные:

INSERT INTO feature_bits (group_id, flags)
VALUES
    (1, B'1000'),
    (1, B'0100'),
    (1, B'1100');

Агрегация:

SELECT
    group_id,
    BIT_OR(flags) AS any_flags,
    BIT_AND(flags) AS common_flags
FROM feature_bits
GROUP BY group_id;

Результат будет битовой строкой, а не обычным числом.

На практике для прав и фич часто удобнее integer или bigint, но знать про bit(n) полезно.

Индексы и поиск по битам

Битовые маски компактны, но у них есть важный минус: обычный индекс по столбцу flags не всегда помогает для запроса по отдельному биту.

Например:

SELECT
    user_id
FROM permissions
WHERE (flags & 4) <> 0;

Такой запрос ищет строки, где включён бит 4.

Если таблица большая и такой поиск нужен часто, можно завести индекс по выражению.

CREATE INDEX idx_permissions_can_delete
ON permissions ((flags & 4));

После этого PostgreSQL сможет использовать индекс для выражения, если запрос написан совместимо.

Можно сделать ещё более точный частичный индекс:

CREATE INDEX idx_permissions_delete_enabled
ON permissions (user_id)
WHERE (flags & 4) <> 0;

Такой индекс хранит только строки, где бит 4 включён.

Но здесь стоит задать себе вопрос: если вы постоянно ищете по отдельным правам, возможно, права лучше вынести в отдельную таблицу.

Когда лучше вынести флаги в отдельную таблицу

Битовые маски удобны, когда нужно компактно хранить набор технических признаков и быстро агрегировать их.

Но они не всегда лучший выбор.

Отдельная таблица может быть лучше, если:

• права часто ищут по одному конкретному признаку;
• к правам нужны дополнительные данные;
• флагов много и они часто меняются;
• бизнес-пользователи должны легко читать эти данные;
• нужны внешние ключи, ограничения и понятные связи;
• важна прозрачность для аналитиков.

Например, вместо маски можно хранить так:

CREATE TABLE user_permissions (
    user_id bigint,
    permission_code text
);

Данные:

INSERT INTO user_permissions (user_id, permission_code)
VALUES
    (1, 'read'),
    (1, 'write'),
    (1, 'delete');

Такую модель проще читать, индексировать и расширять.

Битовые маски — это инструмент. Хороший, быстрый, компактный, но не универсальный.

MySQL

В MySQL тоже есть агрегаты BIT_OR, BIT_AND и BIT_XOR.

Пример проверки права удаления:

SELECT
    user_id
FROM permissions
GROUP BY user_id
HAVING (BIT_OR(flags) & 4) <> 0;

Смысл тот же:

• BIT_OR собирает все включённые биты по группе;
• & 4 проверяет конкретный бит;
• <> 0 оставляет только тех, у кого бит включён.

При переносе запросов между СУБД всё равно проверяйте поведение на граничных случаях: NULL, пустые группы, типы чисел и размер маски могут отличаться.

ClickHouse

В ClickHouse похожие функции называются иначе:

• groupBitOr;
• groupBitAnd;
• groupBitXor.

Пример:

SELECT
    user_id,
    groupBitOr(flags) AS effective_mask,
    groupBitAnd(flags) AS common_mask,
    groupBitXor(flags) AS parity_mask
FROM permissions
GROUP BY user_id;

Названия другие, но идея та же:

• объединить биты;
• найти общие биты;
• найти нечётность.

ClickHouse часто используют для аналитики, поэтому такие функции там полезны для агрегирования больших объёмов событий и технических флагов.

Практический пример: итоговые права пользователя

Соберём понятный запрос, который можно встретить в реальном проекте.

Есть таблица прав:

CREATE TABLE permissions (
    user_id bigint,
    resource_id bigint,
    flags int
);

Заполним её:

INSERT INTO permissions (user_id, resource_id, flags)
VALUES
    (1, 101, 1),
    (1, 102, 3),
    (1, 103, 5),
    (2, 101, 1),
    (2, 102, 1),
    (3, 101, 9);

Получим итоговые права пользователей:

SELECT
    user_id,
    BIT_OR(flags) AS mask,
    (BIT_OR(flags) & 1) <> 0 AS can_read,
    (BIT_OR(flags) & 2) <> 0 AS can_write,
    (BIT_OR(flags) & 4) <> 0 AS can_delete,
    (BIT_OR(flags) & 8) <> 0 AS can_export
FROM permissions
GROUP BY user_id
ORDER BY user_id;

Результат:

user_id | mask | can_read | can_write | can_delete | can_export
--------+------+----------+-----------+------------+-----------
1       | 7    | true     | true      | true       | false
2       | 1    | true     | false     | false      | false
3       | 9    | true     | false     | false      | true

Теперь результат можно спокойно отдавать в отчёт, API или админку.

Как запомнить разницу

Самая простая шпаргалка:

BIT_OR — хотя бы где-то.

BIT_OR(flags)

Используйте, когда нужно собрать все права, фичи или признаки, которые встретились в группе.

BIT_AND — везде.

BIT_AND(flags)

Используйте, когда нужно найти признаки, которые есть у всех строк группы.

BIT_XOR — нечётное количество раз.

BIT_XOR(flags)

Используйте для технических проверок, контрольных сумм и поиска непарных признаков.

Главное

Битовые флаги позволяют хранить несколько независимых признаков в одном числе. Это компактно и иногда очень удобно, но агрегировать такие значения обычной суммой нельзя.

Для битовых масок нужны специальные агрегаты:

BIT_OR(flags)
BIT_AND(flags)
BIT_XOR(flags)

BIT_OR собирает итоговую маску: бит включён, если он встретился хотя бы в одной строке группы.

BIT_AND оставляет только общий набор: бит включён, если он был во всех строках группы.

BIT_XOR показывает нечётность: бит включён, если встретился нечётное число раз.

Проверять конкретный бит удобно через оператор &:

(BIT_OR(flags) & 4) <> 0

Скобки лучше ставить всегда: так выражение читается проще и безопаснее переносится между разными СУБД.

Битовые агрегаты хороши для прав, фич, флагов и технических признаков. Но они не отменяют нормальную модель данных. Если права нужно часто искать, связывать с другими таблицами, показывать аналитикам и расширять бизнес-логикой, отдельная таблица может оказаться понятнее и надёжнее.

Идея простая: когда состояние упаковано в биты, BIT_OR, BIT_AND и BIT_XOR отвечают на вопросы «хотя бы где-то», «везде» и «нечётное число раз» одним аккуратным проходом по группе.