Can you explain the concept of bit flags in SQL and how they are used to store multiple boolean values in a single column?

Bit flags in SQL are a way to store multiple boolean (true/false) values in a single numeric field, using individual bits of the number to represent different flags. Each bit corresponds to a specific flag, which can be on (1) or off (0).

For example, if you have 4 boolean properties, you can use a single integer where:

• 1st bit (value 1) — flag A
• 2nd bit (value 2) — flag B
• 3rd bit (value 4) — flag C
• 4th bit (value 8) — flag D

If the column stores the number 5 (binary 0101), it means flags A and C are on, while B and D are off.

SQL bitwise operations are used to manipulate these flags, for example:

• Check if flag B is on:

  WHERE (flags_column & 2) = 2
  

• Turn on flag D:

  UPDATE table SET flags_column = flags_column | 8 WHERE id = 1
  

• Turn off flag A:

  UPDATE table SET flags_column = flags_column & ~1 WHERE id = 1
  

This approach saves space and simplifies table structure but requires careful handling of bitwise operations.