Query MySQL with BIT_AND operator

Question

Im have a database which saves user preferences in binary.

Chips = 1;
Pizza = 2;
Chinese = 4;

For example, if a user likes chips, then their preference will be 1. If they like pizza, their preference will be 2. If the like both, their preference will be 3. If they like chinese and chips, but not pizza, then their preference will be 5.

I need to create a mysql query where I can select all users who DO NOT like Pizza. I have attempted to solve this problem, but Im finding a lack of docs for using bit and in a where clause:

Logically, this makes sense to me, but this is not right:

SELECT * FROM `User` WHERE BIT_AND(preferences,2) != 2;

Community · Accepted Answer · 2020-06-20 09:12:55Z

5

SELECT * FROM `User` 
WHERE preferences & 2 = 0

Source

SQLFiddle demo

edited Jun 20, 2020 at 9:12

CommunityBot

11 silver badge

answered Nov 4, 2013 at 16:26

juergen d

205k40 gold badges303 silver badges377 bronze badges

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

Collectives™ on Stack Overflow

Query MySQL with BIT_AND operator

1 Answer 1

SQLFiddle demo

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

Comments

Your Answer

Sign up or log in

Post as a guest

Related