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

You should look into MySQL's SET. It may make your life a lot easier. Instead of this funky binary logic, you can just create a set and let MySQL do the binary logic for you. That will limit the number of possible programmer errors, debugging issues, and will make your code more readable.

But if you are dead set on using an integer, look into Bit Functions. For example, to see if a permission 16 is set:

SELECT permission<<4 & 1 FROM users

And if you want to add the permission:

UPDATE users SET permission = permission | 16

Answer 2

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