Bit Wise Flags in MySQL
This is easy if you can count in binary. If counting in Base 2 is not a drummed in skill then look here first and maybe here…
Using an integer field (column) in a table, you can store independent boolean flags (variables), all in this one field.
First you can define some bit masks (note the pythony ## inline-comments),
each one only sets one bit ON:
FEATURE_A_BIT = 1 ## or in base(2) this would look like 00000001 FEATURE_B_BIT = 2 ## or in base(2) this would look like 00000010 FEATURE_C_BIT = 4 ## or in base(2) this would look like 00000100 FEATURE_D_BIT = 8 ## or in base(2) this would look like 00001000
As an example Imagine that our field (let’s call it “features”) is initially set to Zero. This initial state represents all fearture (A – D) are OFF.
The bit wise OR “|” operator Adds in the feature_B bit and leaves all other bits just the same.
So for example: To set the feature_B bit ON: mysql> UPDATE your_table SET features = (features | FEATURE_B_BIT) where id = someid;
0000000
| 0000010
----------
0000010 # great the Feature B bit is set ON
The bit wise AND “&” and the NOT “~” operator Subtracts the feature_B bit, again leaving all other bits just the same.
For example: To set the feature_B bit OFF: mysql> UPDATE your_table SET features = (features &~ FEATURE_B_BIT) where id = someid;
0000010 ## the inital value of features
& 1111101 ## this is just NOT (0000010) from ~ FEATURE_B_BIT
---------
0000000 ## and this is what you wanted: the Feature B bit is reset OFF.
And to retreive a single bit value, with a SELECT statement: mysql> SELECT (features & FEATURE_B_BIT) as feature_B FROM your_table where id = someid;
You can make it more complicated by combining bits and logic, but these are the basics of using “Bit Wise Flags in MySQL.”
In practice you would use this to store data like “opt-in”, “is-valid-format”, or “ok-by-mom” etc… So, really you would replace FEATURE_A_BIT with OPT_IN and FEATURE_B_BIT would be called OK_BY_MOM, but make sure that your bit masks only set one bit, or it gets confusing fast. If you do need to set (or reset) two or more feature bits at once you can OR them together , like this (OPT_IN | OK_BY_MOM), when you feed that into the bit operations above, you will be setting or resetting all in one go.
