# 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:

flag | new | mask | (flag&~mask)|(new&mask) | (flag&~mask)|new |
---|---|---|---|---|

0 | 0 | 0 | 0 | 0 |

0 | 0 | 1 | 0 | 0 |

0 | 1 | 0 | 0 | 1 |

0 | 1 | 1 | 1 | 1 |

1 | 0 | 0 | 1 | 1 |

1 | 0 | 1 | 0 | 0 |

1 | 1 | 0 | 1 | 1 |

1 | 1 | 1 | 1 | 1 |

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
- Store small numbers in big numbers
- Store a tag-cloud in MySQL
- Disable ON UPDATE CURRENT_TIMESTAMP in MySQL

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