Mysql bit value

Discussion in 'Programming/Scripts' started by rodobrist, Jan 11, 2011.

  1. rodobrist

    rodobrist New Member

    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)
     
  2. falko

    falko Super Moderator ISPConfig Developer

    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.
     
  3. rodobrist

    rodobrist New Member

    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
     
  4. rodobrist

    rodobrist New Member

    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
     

Share This Page