update specific bit in integer column

Question

I have a mysql table where user permissions are stored as a bitmask:

|user   | permissions | | Admin | 31          | | User  | 13          |             16 8  4  2  1 Admin ->   1  1  1  1  1  -> 16 + 8 + 4 + 2 + 1 -> 31 User  ->   0  1  1  0  1  ->  8 + 4 + 1 -> 13 

Now I want to add a permission for every user with an sql query. Let's say I want to add the permission 16 for everyone without modifying another bit.

 UPDATE users SET permission = ???? 

How do I do this?

Answer 1

To add permission 16, you just say

UPDATE users SET permission = permission | 16; 

The bitwise OR operator will turn bits on. To turn them off, use a AND operation with the complement of the bit you want off:

UPDATE users SET permission = permission & ~16