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:

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

• Flag based COUNT using MySQL
• Disable ON UPDATE CURRENT_TIMESTAMP in MySQL
• Store small numbers in big numbers
• Calculate the sum of divisors

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