Упаковка булевых переменных для хранения и поиска в базе

    Столкнулся с необходимостью хранить в базе формы, большая часть из которых являлась «галочками», то есть, по сути булевыми переменными. Создавать под тридцать галочек тридцать полей INT 1 не хотелось стал искать другой метод.

    И нашел — битовый массив.
    Точнее это не совсем битовый массив, это представление его в целом типе.
    Сделал так:
    — Все «галочки» пронумеровал от единицы примерно вот так:
    <input type="checkbox" name="variable[]" value="1">
    <input type="checkbox" name="variable[]" value="2">
    <input type="checkbox" name="variable[]" value="3">
    <input type="checkbox" name="variable[]" value="3">
    

    — Далее — сделал две функции: упаковывающую массив в «битовый массив»:

     function binary_array_pack($array){
    	$ret = 0;
    
    // Нашумевший каламбур =)
    	if (!is_array($array))
    	    $array = array($array);	
    		
    	foreach ($array as $val){
    		if ($val > 32 || $val < 1)
    			throw new Exception("Значения не должны превышать 32 и быть меньше 1");
    		$ret |= bindec('1'.str_repeat('0',$val-1));
    	}
    	return $ret;
    }
    
    function binary_array_unpack($val){
    	$ret = array();
    	for ($i=0; $i<32; $i++){
    		if ($val & bindec(str_repeat('0',31-$i).'1'.str_repeat('0',$i))){
    			$ret[] = $i;
    		}
    	}
    	return $ret;
    }
    


    Итого, мы получаем следующее:
    — Пользователь отметил, к примеру, первый, второй и четвёртый пункты
    — Мы получили массив $variable = array(1,2,4);
    — Далее, мы скормили этот массив функции binary_array_pack($variable)
    — Функция вернула целое число, которое в двоичном представлении выглядит так — 1011, а в десятичном оно равно 11

    Собственно, мы имеем, что i-1 й двоичный разряд показывает на наличие i-й «галочки». В данном случае у нас есть нулевой, первый и третий разряды, что означает наличие первой, второй и четвёртой галочки.

    Теперь это число можно записать в одно поле базы.

    Вопрос — как же теперь делать выборку по этим полям?
    Ответ — очень просто. Нам помогут побитовые операции, которые поддерживаются (на сколько мне известно) всеми СУБД.

    Например:
    — Пользователь выбрал для поиска вторую галочку
    — Мы запаковали её в наш как бы «битовый массив», получили число 2.
    — Сделали запрос SELECT * FROM t WHERE field & 2 != 0
    — То есть мы сделали побитовое умножение и, например, число 11, которое в двоичном представлении записывается как 1011 даст нам при умножении на 0010 число 0010. Если же в поле не было записана вторая «галочка», то эта операция даст 0. То же самое будет, если из множественного выбора не будет ни одного соответствия.

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

    Вот так можно сократить количество полей в базе без ущерба для функциональности.

    По поводу производительности такой выборки точных данных [пока] нет (тесты последуют), хотя ИМХО, побитовые операции нативны для процессора и должны летать.

    Приведённые функции лишь отражают суть подхода и, конечно, не претендуют на роль окончательной реализации. Все примечания и замечания приветствуются.

    Основное ограничение этого подхода — максимум 64 «галочки» (разрядность BIGINT), а при данной реализации — максимум 32 «галочки» (максимум для функции php bindec).

    UPD
    Спасибо всем комментировавшим. Комментарии все по делу (к счастью).
    Итого: Такой подход можно использовать для фиксирования множественного выбора (например, не известно заранее, сколько галок может поставить юзер, так что не понятно, сколько полей водить), но его использование приведёт к фулскану, что, естесвенно, гораздо хуже, чем обычная выборка по полям. Однако, это выведено из теории, экспериментов не ставилось =)
    Поделиться публикацией

    Комментарии 25

      +3
      За такое хранение расстрел на месте.
      Через год вспомнить что там и как лежит будет невозможно.
      Хорошо если документация будет. А если потеряется?

      Если нужно 30 полей, пускай будет 30 полей.
      Кому они мешают? Пускай лежат себе, с нормальными названиями, комментариями к полям. Никакой документации не нужно и так понятно откуда что брать.
        0
        ну про расстрел за такое хранение не знаю, а вот за потерю документации — точно расстрел =).
        Что где лежит можно (я так делаю) описать в конфиге. Оттуда берутся расшифровки для значений (например из массив).
        Ну 30 полей хранить тоже можно, это выбор каждого. Я не претендую на абсолютную истину, но ИМХО, такой метод вполне жизнеспособный и (опять же ИМХО) достаточно изящный.
          0
          Документация теряется элементарно. Проект сделан, сдан. Контора сделавшая его исчезла.
          На доделывание отдали другой. Шанс найти документацию стремится к 0.

          Ага, надо знать что конфиг есть, и что оно в нем описано. На поиски в лучшем случае день уйдет. Скорее 2-3 дня.

          Зачем плодить таки сложности буквально на пустом месте.
          Кому мешают это 30 полей? Винты сейчас вообще ничего не стоит. Ограничений на количество столбцов в таблице можно считать что нет.

          И что мы выигрываем от такого хранения?

          Минусы понятны сразу:
          — Непонятная БД
          — Непонятное приложение
          — Сложный код и там и там
          — Необходима внешняя документация
          — Тормоза в перспективе
            0
            Этот подход применим при множественном выборе. Например, если нам надо указать несколько величин одной переменной.
          0
          Есть один минус, максимальная ширина таблицы в mysql составляет 255 поле(к сожалению сам сталкивался с таким).
          +1
          А как же SET? Данные типа SET в MySQL тоже хранятся в виде битовых массивов.
            0
            В этом случае поиск будет осуществляться так:

            SELECT * FROM tbl WHERE FIND_IN_SET('value', flags);
              0
              Безусловно, можно хранить в SET, но на сколько мне известно, не все СУБД его поддерживают.
                0
                Собственно, мой способ — это и есть реализация SET. Почти уверен, что там так и реализуется поиск.
                –1
                > ИМХО, побитовые операции нативны для процессора и должны летать.

                Есть такая штука — индексы. При их использовании сложность поиска в базе составляет, грубо говоря, O(log N). Без их использования — O(N). Порядок проигрыша в скорости поиска в такой базе на, скажем, миллионе записей ~100'000 раз. Вы еще хотите пользоваться нативными битовыми операциями в полях, по которым возможна выборка?
                +1
                нативные то они нативные — но здесь придется д елать фулскан сначало.
                Посмотрите в сторону индексов типа bitmask для отдельно стоящих полей-битов — я думаю разница будет громадная.
                  0
                  дадада. я уже почитал. Спасибо всем.
                  0
                  На счет запроса «SELECT * FROM t WHERE field & 2 != 0» — удивила запись !=, на сколько мне известно в mysql это не работает, а в ms sql не стандартно и возможно тоже не работает или работает не везде :)

                  Откуда такая запись «не равно»? Мне просто интересно из какой БД )
                    0
                    Всё прекрасно работает. Даже специально только что проверил SELECT 1!=2 возвращает 1
                      0
                      Где?)
                        0
                        Mysql 5 с чем-то
                          0
                          Сдаюсь. Тоже только что проверил.
                          Не знаю, когда-то попытался использовать != и вылетела ошибка, с тех пор всегда юзаю <>.
                          И не те справочники видимо читаю, когда-то смотрел список операторов MySQL, там не было !=.

                          Не прав, каюсь :)
                            0
                            да ладно вам =) велика беда =)
                    0
                    Зачем писать bindec('1'.str_repeat('0',$val-1));
                    если можно просто 1 << ($val-1)?
                      0
                      Точняк. Как я мог забыть. Спасибо огромное
                    • НЛО прилетело и опубликовало эту надпись здесь
                        0
                        Не в обиду будет сказано, но мне кажется вы изобретаете велосипед. Во всяком случае в PHP.
                        ru.php.net/pack
                          0
                          Упс. Кажется, я поторопился. pack не поддерживает boolean.
                          Сорри.
                          0
                          А вот есть такой тип данных BIT в MySQL. Почему им нельзя обойтись?

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

                          Самое читаемое