MySQL Forums
Forum List  »  Newbie

Re: Help with query...
Posted by: Rick James
Date: March 16, 2012 09:13AM

Consider "set theory". Two fields -- one with bits for what is Wanted, one with bits of what they have.
WHERE a.Want = b.Have -- `b` has exactly the set of items that `a` wants.
WHERE (a.Want & b.Have) -- at least one thing that `a` wants can be satisfied by `b`.
SELECT BIT_COUNT(a.Want) -- how many things `a` wants.
SELECT BIT_COUNT(a.Want & b.Have) -- how many things that `a` wants can be satisfied by `b`.
WHERE (a.Want & b.Have) = b.Have -- everything that `b` has is wanted by `a`.
WHERE (a.Want & ~b.Have) = 0 -- `b` has everything that `a` Wants.

With a BIGINT UNSIGNED, you can store 64 items.
Item #n (0..63) can be turned into a bit patter via (1 << n).
Turning on a bit (adding item 7 to a Want):
UPDATE ... SET Want = Want | (1 << 7) WHERE ...
(If #7 is already turned on, this has no effect).
Removing a Want:
UPDATE ... SET Want = Want & ~(1 << 7) ...

&, |, ~, << are called "operators"; see the manual for lower level discussion.

Options: ReplyQuote


Subject
Written By
Posted
March 14, 2012 05:59PM
Re: Help with query...
March 16, 2012 09:13AM


Sorry, you can't reply to this topic. It has been closed.

Content reproduced on this site is the property of the respective copyright holders. It is not reviewed in advance by Oracle and does not necessarily represent the opinion of Oracle or any other party.