SQL. Хранение нескольких признаков-флагов в одном поле19.09.2024 13:30

Подобных статей не нашёл, поэтому решил поделиться открытием. Возможно кому-то такой способ хранения покажется странным, т.к. это не особо читаемо, а кому-то наоборот покажется интересным вариантом хранения множества признаков сущности в одном поле flags (ну или чтобы шпионы не разобрались в коде). Тем не менее, лично мне пришлось с этим разбираться, чтобы вносить правки. Дабы вы не пугались когда такое встретите — упрощаю максимально, чтобы была понятна суть. Поехали…

Демонстрация на PostgreSQL (местами хардкод, чтобы не отвлекаться от сути).

-- Установка признака
create function set_flag(flag bigint, mask bigint) returns bigint
    language sql
as
$$
select (flag::bit(64) | mask::bit(64))::bigint
$$;


-- Работа с признаками
create function get_flag(flag bigint, mask bigint) returns boolean
    language sql
as
$$
select 	(flag::bit(64) & mask::bit(64))::bigint <> 0;
$$;

  • создание таблицы и добавление тестовых данных (на основе спецификации, в которой аналитики указали «вес» каждого компонента на основе своих предпочтений)

-- создание демо-таблицы
create table test_flags(id int, name text, flags bigint default 0);

-- вставка самих компонентов из спецификации (для наглядности)
insert into test_flags (id, name) values (1, 'масло');
insert into test_flags (id, name) values (2, 'чай');
insert into test_flags (id, name) values (3, 'хлеб');
insert into test_flags (id, name) values (4, 'колбаса');

-- добавление сущностей, которые имеют несколько признаков 
insert into test_flags (id, name) values (5, 'бутерброд с маслом');
insert into test_flags (id, name) values (6, 'бутерброд с колбасой');
-- установка признаков для одиночных компонентов
update test_flags set flags = set_flag(flags, 1) where id = 1; -- масло
update test_flags set flags = set_flag(flags, 8) where id = 2; -- чай
update test_flags set flags = set_flag(flags, 32) where id = 3; -- хлеб
update test_flags set flags = set_flag(flags, 1024) where id = 4; -- колбаса

/* 
  Установка нескольких признаков для составных компонентов 
  (по сути флаги складываются через set_flag) 
*/
-- 1. бутерброд с маслом (в спецификации: признак масла = 1)
update test_flags set flags = set_flag(flags, 32) where id = 5; -- хлеб
update test_flags set flags = set_flag(flags, 1) where id = 5;

-- 2. бутерброд с колбасой (в спецификации: признак колбасы = 1024)
update test_flags set flags = set_flag(flags, 32) where id = 6; -- хлеб
update test_flags set flags = set_flag(flags, 1024) where id = 6;

-- результат
select * from test_flags;

  • таблица test_flags наполнена так

    c0503e21ec43cf8b54d64e0fafab8abd.png

  • теперь посмотрим на то, для чего это всё придумано

-- получить все значение признаков, исходя из одного поля flags
select id, name, flags,
       get_flag(flags, 1)    as better,
       get_flag(flags, 8)    as tea,
       get_flag(flags, 32)   as bread,
       get_flag(flags, 1024) as sousage
from test_flags
order by id;

-- для тех кто шпрехает ву франсе и т.д.:
-- better=масло, tea=чай, bread=хлеб, sousage=колбаска

Получается, что из одного поля flags получилось несколько столбцов с признаками

63b71182e6711a9488b0819cdf470311.png

И теперь если нужны строки, обладающие каким-то конкретным свойством, мы просто применяем функцию get_flag

-- выбрать строки с маслосодержащими сущностями (спецификация: масло = 1)
select * from test_flags where get_flag(flags, 1);

-- output
-- id  name                flags
-- 1   масло               1 (побочный продукт примера)
-- 5   бутерброд с маслом  33

-- с хлебосодержащими (спецификация: хлеб = 32)
select * from test_flags where get_flag(flags, 32);

-- output
-- id  name                  flags
-- 3   хлеб                  32 (побочный продукт примера)
-- 5   бутерброд с маслом    33
-- 6   бутерброд с колбасой  1056

Выводы

Я не знаю почему в данной конторе сделали именно так, но пришлось разбираться! Надеюсь кому-то подниму веки! Себе вроде поднял (разве что в спецификации ещё и hex формат присутствует, т.е. 32 равно 0×20).

Вопрос к вам, коллеги! Где бы вы применили такую фишку, если бы очень захотелось?

© Habrahabr.ru