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?

Crackbrain

A tiny bit of "constructive" criticism: Have you thought of having a table with option names and linking that to the users? (I'm assuming you have options per user rather than installation). So for example the options table has 2 columns [user_id,option_name]. In Java you can map option_name to an Enum (I'm quite sure other languages have similar constructs). The benefit of this approach is that you can read the option, rather than doing some difficult calculation. Specially if the app grows to have 20 or 30 options. – Manmade

Doesn't work like that sorry. It's not user->options. – Crackbrain

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'

Stair

Actually I was referring to an INT field type. I was using it long before BIT was actually a bit field type (previous to 5.0.3 it was an alias to TINYINT). I haven't actually tried the newer BIT field, so I'm not sure if it used the same way or not. – Stair

The BIT field doesn't seem to allow me to store multiple options. So I'm pretty happy to continue using an INT field type. – Crackbrain

Using INT I'm unable to store anything that begins with a leading 0 (obviously). Ideas to keep a number but allow 0? – Crackbrain

Hmm, not sure I follow what you're saying? I updated the answer with how to set bits in an INT. – Stair

Oh I see, I forgot about the b operator – Crackbrain

Answer 2

Would the SET field type be of any use here?

Maryannamaryanne

Looks the part, but due to the amount of new options/flags that are added, this wouldn't be a viable solution. – Crackbrain

With INT/BIGINT type you can store up to 32/64 options/flags, the same applies to SET datatype. Its just more convenient to use named flags insead of numbers – Orthodontia