MySQL: Example of where to use the BIT Data Type
If I understand correctly, the pre MySQL 5.0.3 interpretation of the BIT data type meant it could be used as a series of flags? If this is the case I can see practical uses for it in MySQL but imagine it would not have been as efficient as using SET.
Even if the above is not the case, I have great difficulty in understanding how the current implementation of the BIT data type can be practically applied in a database. If anyone is able to provide a simplified explanation and, an example of where it would be applicable, I would be grateful.
I have searched for descriptions and examples elsewhere but have been unsuccessful in finding examples applicable solely to databases.
Assume you have row with property field
Properties defined as
1 - Has property 1 2 - Has property 2 4 - Has property 3 8 - Has property 4
assign properties 1 and 2 (i.e. only 1 and 2 others will be cleaned up)
set status = status & 3
add property 3
set status = status | 4
remove property 1
set status = status | 14
Select rows with properties 1 and 2
SELECT .. WHERE (Properties & 3) = 3
Select rows where properties 1 or 2
SELECT .. WHERE (Properties & 1) = 1 OR (Properties & 2) = 2