Robert Eisele
Engineer, Systems Architect and DBA

Simultaneously set and delete bit flags in a database

Imagine, we store bit-flags in a database as integers and want to update the bit-set by simultaneously deleting and updating the bits. An expression like this can quickly become unhandy. However, I wanted to make this solution pretty and after some tinkering I came up with a quite nice solution.

Let's say, you want to formulate a MySQL query like this:

UPDATE t1 SET flag=(SET 3. bit, DELETE 4. BIT) WHERE id=5;

(TL;DR: The finaly copy and paste solution is at the end.) You want all the remaining flags untouched and only 3 and 4 to be modified if necessary. You could do a lookup-table for this, but by looking more closely, you'll recognize, that the old flags should be used if a mask is pointing positively to bit 3 and negatively to bit 4. This will give us the following expression:

\[(\text{flag} \& \text{~}\text{mask}) | (\text{new} \& \text{mask})\]

But what is the mask and what is new?

\[\text{mask} = \sum\limits_{i=0}^N 2^{x_i} = \bigvee\limits_{i=0}^N 1 << x_i\]

\[\text{new} = \sum\limits_{i=0}^N d_i 2^{x_i} = \bigvee\limits_{i=0}^N d_i << x_i\]

With \(x_i\) being the index to be changed and \(d_i\) the appropriate value, either zero or one which should be set. Now the interesting part begins: When you see \((new \& mask)\), you see that the initial formula wasn't minimal enough. But let's proof a simpler expression with a lookup table:

flagnewmask(flag&~mask)|(new&mask)(flag&~mask)|new
00000
00100
01001
01111
10011
10100
11011
11111

Now something really beautiful is possible when you think about it: The mask is made of all bits we need to change and as such, we first delete it and add it again afterwards, which is a redundant step. This means, we can simplify the formula and separate the delete and set-step. So the one contradiction of the proof above becomes a don't-care. More formally we get:

\[\text{flag}'=(\text{flag} \& ~D) | S\]

With \(D\) and \(S\) being the sets to be deleted and set. Putting it together we get a really nice SQL statement, you'll need to run:

UPDATE t1 SET flag=flag & ~16 | 8 WHERE id=5;

In a real world application you would define some constants and calculate the values in the application:

<?php
define('F_FOO1', 1 << 0);
define('F_FOO2', 1 << 1);
define('F_FOO3', 1 << 2);
define('F_FOO4', 1 << 3);

// Delete F_FOO2 and F_FOO3, but set F_FOO4 and F_FOO1:
$S = (F_FOO1 | F_FOO4);
$D = (F_FOO2 | F_FOO3);
mysql_query("UPDATE t1 SET flag=flag & ~" . $D . " | " . $S . " WHERE id=" . $id);

I really like this result. And thinking directly about the solution also makes totally sense.

You might also be interested in the following

 

Sorry, comments are closed for this article. Contact me if you want to leave a note.