MYSQL bitwise OR over multiple entries

Question

I need to update the status (bits) of a job depending on the status of all it's articles (also bits).

So for example I have a Job which has got the following articles

A: status = 1001
B: status = 1011
C: status = 0010

Now I want the job status to be = A OR B OR C = 1011.

Any way I can do this in MYSQL without having to make loops through all articles?

Thanks!

What format is the bit string stored in? Is is a 4 long bit field or a string of some kind? For a 4 long bit you should be able to use BIT_OR aggregate function:- dev.mysql.com/doc/refman/5.0/en/… — Kickstart
– Kickstart, Commented Jan 29, 2013 at 16:42

Ian Atkin · Accepted Answer · 2013-01-29 16:48:49Z

1

As with many problems, it's a simple case of reading the documentation: MySQL Bit Functions.

But let's get some things straight here. Are these values intended to be bitmasks? If that's the case (and I'm sure it is), then B is equal to A | C, which is a tad odd. Typically, bitmasks work in the following way...

A = 0001
B = 0010
C = 0100
...etc.

In this way, you can combine values and have the bitmask account for multiple options, so the combination of A and C (A | C) would be 0101.

The whole purpose of a bitmask is that they can represent multiple values in a fixed-length string, so they're space efficient. The downside is that they don't make for human-readable output, unless you're used to looking at The Matrix!

On the other hand, I could be way off.

But, to get you going, I believe you are looking for the BIT_OR function: MySQL GROUP BY (Aggregate) Functions, which calculates and returns the bitwise OR of all bits in expr.

edited Jan 29, 2013 at 16:48

answered Jan 29, 2013 at 16:37

Ian Atkin

6,3862 gold badges19 silver badges25 bronze badges

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

4 Comments

Kickstart Over a year ago

I suspect that these are status fields on different rows (ie, one row per article). If so I suspect an aggregate function is required

Ian Atkin Over a year ago

I totally agree. I've used this in the past... but now I steer clear of bitmasks after several hours of trying to explain their value to silly bosses! :)

Kickstart Over a year ago

I am used to them from mainframe (PL/1) days but never really used them in MySQL.

user1511924 Over a year ago

Thanks! What I needed was BIT_OR. Saves me a lot of "looping arround time" :-) And lucky me, I won't need to explain anything to my Boss, as long it works ;-) Cheers!

Collectives™ on Stack Overflow

MYSQL bitwise OR over multiple entries

1 Answer 1

4 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

4 Comments

Your Answer

Sign up or log in

Post as a guest

Related