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?

try this link: http://stackoverflow.com/questions/6498881/how-to-bitwise-or-into-a-binary100 — AnandPhadke, Oct 17 '12 at 06:59
@AnandPhadke - that link is for a datatype that stores MANY bytes; that requires a more complicated solution than this question. — ToolmakerSteve, Oct 06 '19 at 20:12

score 59 · Accepted Answer · answered Oct 17 '12 at 07:06

59

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

answered Oct 17 '12 at 07:06

Ian Clelland

43,011
8
86
87

To generalize to multiple bits: Suppose want to simulataneously set bits with values 16 and 2. That is binary value `b'10010'`. So the first snippet would end with: `= permission | b'10010';` Corresponding clear would end with: `= permission & ~b'10010';`. – ToolmakerSteve Oct 06 '19 at 20:26
... or if you prefer writing the decimal values of the bits, can do multiple bits this way: *To set multiple bits:* `... = permission | (16+2);`, *To clear multiple bits:* `... = permission & ~(16+2);`. – ToolmakerSteve Oct 06 '19 at 20:36

score 4 · Answer 2 · answered Oct 17 '12 at 07:05

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

First snippet is wrong. Should be `permission>>4`, to shift down to the 1's digit. An alternative to shifting, is to work directly with binary literals. `SELECT permission & b'10000' ...` tests the bit with value 16. Or use `SELECT permission & 16 ...`. — ToolmakerSteve, Oct 06 '19 at 20:30

update specific bit in integer column

2 Answers2

Linked