Alternative to bitwise operation

问题:

Scenario : I have say 4 check boxes and users can select those checkboxes in any combination(they also have the power to not select even a single check box). I have to store these 4 options to a single column. I think the best option is to store using binary representation

option1 has the constant value 1 
option2 has the constant value 2
option3 has the constant value 4
option4 has the constant value 8

So if the customer selects option2 and option4, then the value that is stored in the DB will be 2 + 8 ie: 10, if customer selects option1, option4 and option8 the value will be 1 + 4 + 8 which is 13.

Also when I query from mysql I can use

Select * from option_table where (option & 4)=4;

if I want to retrieve rows where option3 is selected.

But for some reason I cannot use this approach, or just say I need to know what's the next best option to store these multiple values in a single column ?

回答1:

An alternative could be to multiply prime numbers. To select, you would select columns where the modulus of the prime number into the value of the column is 0.

Example:

Value 1: 2
Value 2: 3
Value 3: 5
Value 4: 7

Value 2 & 3 would be 3*5 = 15
Values 1, 2, 3, 4 would be 2*3*5*7 = 210

To get the rows where value 1 is on, select where value % 2 == 0

回答2:

You can always use this approach as long the number of bits is less equal than the bit length of the type for that column. If there are more bits to store you can use multiple columns(fields).

You can also use Bit Fields.