Storing and matchig binary data in mysql

Question

I'm trying to store different mutiple select field states in the db. So one select list would be:

<option value="1">a1</option>
<option value="2">a2</option>
<option value="4">a3</option>
<option value="8">a4</option>

I want to try this without a second (normalized) table. So i came up with adding the option values and saving them in the db. That way i can read them out, convert them and search for power of 2 values.

Problem is when searching in the db. Say someone searches for a1. That would be a 1, but also a 3, because its a combination of a1 and a2.

So this is kind of confusing. Now im trying to store the values binary, like 101 and maching the decimal values against it. When searching for a1 OR a2 i have to search for (binary) 1 and 11. I dont know how to match this and i'm shure there is a better approach to this, right?

Answer 1

You are using what's called a bit-field ; which means you'll have to use bitwise operators / functions to work with your data.

See 11.11.1. Bit Functions.

For instance, to select lines that have the "1" bit is set, you'd have to use something like this :

select *
from your_table
where (your_field & 1) = 1

To test for the thirg bit (i.e. the one corresponding to the value 4, which is 1<<2), you'd use :

select *
from your_table
where (your_field & 1<<2) = 1

As a sidenote : when using that kind of calculation, MySQL will have to do a full-scan (i.e. go through every lines in the table) to find those which match the condition... which might not be great for performances, if you're having a lot of lines.

And, as a quick demo :

mysql> select (4 & 1<<2);
+------------+
| (4 & 1<<2) |
+------------+
|          4 |
+------------+

And

mysql> select (3 & 1<<2);
+------------+
| (3 & 1<<2) |
+------------+
|          0 |
+------------+