How to add a bit value to a field for multiple records in MySQL

Question

Have a database where one table has a field that uses binary bits as flags. So depending on which flags are toggled, you could have a wide range of values so looking at the value alone isn't helpful. ie, a value greater than 8 doesn't mean that the value of 8 is in use (17 for example is 16 + 1).

With this in mind, is there a way to update multiple records to either set or unset a bit (say a value of 8) for a particular field? Some records might have it set while others don't, so don't want to just simple add (or subtract) 8 from it.

Joachim Isaksson · Accepted Answer · 2013-02-23 23:23:37Z

4

You can use the bitwise OR | and AND & functions;

UPDATE myTable SET myValue = value |  8;   -- set bit 3
UPDATE myTable SET myValue = value & ~8;   -- clear bit 3

(Very) simple SQLfiddle for testing.

answered Feb 23, 2013 at 23:23

Joachim Isaksson

182k28 gold badges298 silver badges308 bronze badges

Sign up to request clarification or add additional context in comments.

Comments

juergen d · Accepted Answer · 2013-02-23 23:20:23Z

3

Use the BIT functions. To set bit 1:

update your_table
set bit_field = bit_field | b'0001'

To unset bit 1:

update your_table
set bit_field = bit_field & b'1110'

SQLFiddle demo

answered Feb 23, 2013 at 23:20

juergen d

205k40 gold badges305 silver badges377 bronze badges

4 Comments

Wolfie Over a year ago

So | adds the bit and & only keeps matching bits (set to 1)?

Wolfie Over a year ago

Looking at the link you provided, for unsetting, would ^ be a better alternative? * Marked yours as the answer since you answered first. Thanks.

Wolfie Over a year ago

Nevermind, tried the demo link and see that & is the way to go. Thanks again. :)

Joachim Isaksson Over a year ago

@Wolfie & ~ is what you'll want to use for unsetting. ^ will flip the bits every time and not reliably set/unset.

Collectives™ on Stack Overflow

How to add a bit value to a field for multiple records in MySQL

2 Answers 2

Comments

4 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

Comments

4 Comments

Your Answer

Sign up or log in

Post as a guest

Related