Storing binary string in MySQL

Question

I've developed a small binary flag system for our admin centre. It allows us to set items to have multiple options assigned to them, without having to store have a table with several fields.

Once the options are converted into binary with bitwise operators, we'd end up with an option like 10000 or 10010 which is all good. Doing it this way allows us to keep adding options, but without having to re-write which value is which, 10010 & (1 << 4) and I know that we have something turned on.

The problem however is storing this data in our MySQL table. I've tried several field types, but none of them are allowing me to perform a query such as,

SELECT * FROM _table_ x WHERE x.options & (1 << 4)

Suggestions?

Answer 1

To check if a bit is set your query needs to be:

SELECT * FROM _table_ x WHERE x.options & (1 << 4) != 0

And to check if it's not set:

SELECT * FROM _table_ x WHERE x.options & (1 << 4) = 0

Update: Here's how to set an individual bit:

UPDATE table SET options = options | (1 << 4)

To clear an individual bit:

UPDATE table SET options = options &~ (1 << 4)

You can also set them all at once with a binary string:

UPDATE table SET options = b'00010010'

Answer 2

Would the SET field type be of any use here?