mySQL SELECT rows where a specific bit of an integer is set

Question

i have to do a select query in a posting table where a specific bit of an integer is set. The integer represents a set of categories in a bitmask: E.g.

1 => health
2 => marketing
3 => personal
4 => music
5 => video
6 => design
7 => fashion
8 => ......

Data example:

id | categories | title
1  | 11         | bla bla
2  | 48         | blabla, too

I need a mysql query that selects postings, that are marked with a specific category. Let's say "all video postings" This means i need a result set of postings where the 5th bit of the catgories column is set (e.g. 16,17,48 ....)

SELECT * FROM postings WHERE ....????

Any ideas ?

Answer 1

You can use bitwise operators like this. For video (bit 5):

WHERE categories & 16 = 16

Substitute the value 16 using the following values for each bit:

1 = 1
2 = 2
3 = 4
4 = 8
5 = 16
6 = 32
7 = 64
8 = 128

This goes from least significant bit to highest, which is opposite of the way most programmers think. They also start at zero.

Answer 2

How about

SELECT * FROM postings WHERE (categories & 16) > 0; -- 16 is 5th bit over

One issue with this is you probably won't hit an index, so you could run into perf issues if it's a large amount of data.

Certain databases (such as PostgreSQL) let you define an index on an expression like this. I'm not sure if mySQL has this feature. If this is important, you might want to consider breaking these out into separate Boolean columns or a new table.