Mysql bit value

Hi all,

I'm falling into the trap of mySQL optimisation and my question is about using binary values, BIT(3) in particular.

To imagine how I'm using this data think of a permissions table:
(read|write|delete)
(0, 1 , 0)

Three bits, and in that example the user only has write permission.
I'm not worried about the data manipulation side of the equation(php), more the query:

SELECT name FROM permission_table
WHERE bit_colum LIKE b '%1%';

This query does not work, and I would not expect it to either(% only works on text), but my question is how could I make a

SELECT name where user has write permission
so the 3 bits = (any,1,any)

 

Why don't you do it like Linux does it? For example:

write = 4
read = 2
delete = 1

Then you add up the permissions, for example:
write, read, delete = 4 + 2 + 1 = 7
write, read = 6
write, delete = 5
write = 4
read, delete = 3
read = 2
delete = 1

That way, you can take the value you are looking for and query it in the database.

 

i never thought of that

Thats a pretty good way to solve the issue when there are 3 bits to check for, and I guess its my fault for not indicating otherwise, but if I'm working with 8(which I am at this stage) bits and I'm curious about the 3rd and only the third

(?|?|1|? | ?|?|?|?), to try and map out the combinations numerically would be a nightmare, ie WHERE bincol=64||65||66||67 .. etc(total of 128 possibilities)

I think a bitwise & operator might be needed eg:

(??1? , ????)&
(0010,0000)

==(0010,0000)

I have a feeling Im close to the solution

 

found it

here it is
SELECT * FROM bits

WHERE bnry & 0x20 = 0x20


so if we have an 8 bit column and you want to check if the third bit

(??1? ????)

you use the bitwise & operator against (0010 0000)

which when matched will == (0010 0000)

sorry to answer my own question