mysql / bitmask: select for NOT value

Question

I have a table with a bitmask column(unsigned int). This field has values 1, 2, 4, 8, 16, etc.

How do I select for NOT a specific value? IE I don't care what the other bits are - only that a specific bit be 0.

What I've tried:

select count(*) from mytable;

This gives me 3387255.

select count(*) from mytable where outageMask & ~8;

This gives me 552061.

So I would assume that:

select count(*) from mytable where outageMask & 8;

would give me 2835194. Not so. Instead I get 87711.

What am I doing wrong?

does the column have `NULL` values? – Fabian Schmengler Jan 28 '13 at 17:14 — Fabian Schmengler, Jan 28 '13 at 17:14
No NULLs. 0 would represent 'no data'. – ethrbunny Jan 28 '13 at 17:14 — ethrbunny, Jan 28 '13 at 17:14

score 5 · Accepted Answer · answered Jan 28 '13 at 17:25

only that a specific bit be 0.

This is important. Let's see, what you calculated (with 8 bit numbers for simplification):

 8 = 0b00001000
~8 = 0b11110111

So, what do these bitmasks test if you BITWISE AND them with another value? The first mask tests if bit "8" is set. The second mask tests if all other bits are set. But this is not what you intended, you wanted a negation of the whole statement. Which is easy:

select count(*) from mytable where (outageMask & 8) = 0;

mysql / bitmask: select for NOT value

1 Answers1