CODEWITHSUNDEEP
mysqlbit-manipulationbit
Didzis
Didzis

Reputation: 453

Setting and clearing multiple bits stored as integer in MySQL

I have a table that has a column 'userExtraStatus' with type signed integer.

I am storing bits for various user statuses.

to set bits I have a query

UPDATE 
    publicUsers 
SET 
    userExtraStatus = userExtraStatus |1024 |2048 |1 
WHERE 
    userID='1'

All three bits are set. Column value = 3073 (1024 + 2048 + 1) which is correct as I see it.

If I try to unset any of the bits, for example '1024' and keep the remaining bits as is I am using a query

UPDATE 
    publicUsers 
SET 
    userExtraStatus = userExtraStatus |1024 &~ 2048 | 1 
WHERE 
    userID='1'

And the result is still 3073

Expected result: 1025 (which is 3073 - 2048)

Am I missing something with Bitwise operators?

Upvotes: 0

Views: 417

Answers (1)

Raymond Nijland
Raymond Nijland

Reputation: 11602

You need to use this. 

SELECT ( 1024 | 2048 | 1 ) & ~2048

or use simple minus. 

SELECT ( 1024 | 2048 | 1 ) - 2048

see demo http://sqlfiddle.com/#!9/bacec0/25

Example usage with your update query.
Assuming the field userExtraStatus contains ( 1024 |2048 | 1 = 3073 ) already.

UPDATE 
    publicUsers 
SET 
    userExtraStatus = (userExtraStatus) & ~2048
WHERE 
    userID='1'

Editted because off comment

Is there a way I can mix instructions for Setting and Unsetting bits in one query? For example If I sumbit a form with checkboxes for each bits - I do not want to check if the particular bit is already set - I want it to be set therefore resulting in a query UPDATE ... SET status = status | bit1 | bit2 &~ bit3

That's possible but you need to use proper parentheses like so

UPDATE ... SET status = ( status | bit1 | bit2 ) & ~bit3

Unsetting multiple bits is possible with

UPDATE ... SET status = ( status | bit1 | bit2 ) & ~( bit3 | bit4 )

That's possible but you need to use proper parentheses like so

Upvotes: 2

Related Questions