Usar un campo entero en MySQL para almacenar mas de un flag

Ante la necesidad de almacenar varios flags (banderas) en un solo campo entero, podemos implementar la siguiente solucion:

UNSIGNED TINYINT = 8 bits / 1 byte
UNSIGNED SMALLINT = 16 bits / 2 bytes
UNSIGNED MEDIUMINT = 24 bits / 3 bytes
UNSIGNED INT = 32 bits / 4 bytes

etc, etc ...

Por cada bit, tenemos una posicion, asi que si tomamos el ejemplo de un campo TINYINT, tendriamos disponibles 8 posiciones, u 8 flags para utilizar.

A cada posicion le asignamos un valor superior en base 2, de esta forma:

define("POS_1", 1);
define("POS_2", 2);
define("POS_3", 4);
define("POS_4", 8);
define("POS_5", 16);
define("POS_6", 32);
define("POS_7", 64);
define("POS_8", 128);

Ahora, supongamos que queremos activar los flags de las posiciones 3, 6 y 8, entonces realizamos un bitwise OR (operacion binaria):

$campo = (POS_3 | POS_6 | POS_8);

La operacion OR entre dos numeros dice que si el valor A o el valor B son 1, el resultado final es 1:

00000100 = 4
OR
00100000 = 32
OR
10000000 = 128
--------
10100100 = 164 = campo en la tabla MySQL

y a la variable campo la almacenamos en la base de datos.

Ahora, la "magia" la hacemos formulando la siguiente consulta, que de acuerdo a mis test, funciona bastante bien a pesar que el optimizador de MySQL no la optimiza.

SELECT * FROM `tabla` WHERE ((`campo` & 4) && (`campo` & 32) && (`campo` & 128));

En esta consulta se hace una operacion bitwise AND entre el valor de nuestro campo (164 = 10100100) en la base de datos y el valor decimal de cada posicion.

10100100 = 164
AND
00000100 = 4
--------
00000100 = 4

La operacion AND dice que si el valor en A y el valor en B son 1, entonces el resultado final es 1.

Ahora, en la consulta vemos que el WHERE hace AND's bitwise y logicos, y ahi esta el secreto, ya que si la operacion and en cada parentesis da un numero cero, la comprobacion logica falla y el registro no concuerda.

Por ejemplo, si buscamos para la POS_5, que es 16, entonces tenemos que:

10100100 = 164
AND
00010000 = 16
--------
00000000 = 0

Por ende, no se cumple y afecta al resto de la comprobacion logica (doble ampersand &&).

He probado esta consulta en una tabla con 88000 registros, de los cuales unos 4000 son distintos y obtuve un tiempo de 0.0024 seg de promedio, asi que podriamos decir que la consulta funciona bien, aunque el optimizador no este trabajando.
Esto incluyendo SQL_NO_CACHE para obtener un resultado fiel (no usar el cache, obviamente).


Saludos


PD: comentarios y sugerencias, totalmente aceptados ...

Inflación

"No es el dinero, como a veces se dice, sino la depreciación del dinero -la destrucción cruel y astuta del dinero- la que es la causa de muchos males.
La inflación destruye el ahorro individual y la autosuficiencia mientras gradualmente erosiona la riqueza personal. Pocas son las políticas más calculadas para destruir las bases existentes de una sociedad libre que la destrucción de su moneda."

Hans F. Sennholz