Problem: You are working with an antique data model where someone thought is was a good idea to store true/false data as bits in an integer value.
Bitfields are a kind of composit column which stores many logical values in one physical column as an integer value. It allows you to store many yes/no options in a very small space. When looking at this data you'll see integers but what is really important is the bits that make up that number.
Lets say I want to store event level info in a log table.
CREATE TABLE eventlog(
level INT(11) NOT NULL,
msg varchar(32) NOT NULL
bit 1 (0001) = fatal error (int 1)
bit 2 (0010) = warning (int 2)
bit 3 (0100) = error (int 4)
and bit 4 (1000) = user error (int 8)
What if I have both a user error and a warning error? That's int 6.
Or all options? That's int 15.
Or all the level's with a warning logged? That's int 2,3,7 and 15
Querying or filtering on a single bit is hard. It's as difficult to work with as CSV string columns. If this data had been normalized properly, each part would have its own column in the database.
SELECT msg, EXPORT_SET(level,1,0,'',4) AS debugBits, SUBSTRING(EXPORT_SET(level,1,0,'',4),1,1) AS hasFatal, SUBSTRING(EXPORT_SET(level,1,0,'',4),2,1) AS hasWarning, SUBSTRING(EXPORT_SET(level,1,0,'',4),3,1) AS hasError, SUBSTRING(EXPORT_SET(level,1,0,'',4),4,1) AS hasUserError FROMeventlog
which results in:
We're taking the bitfield column, turning the column into strings and pulling the string apart. Notice here that bit 1 is on the far right.
What happens if I want to add context to this data. That's what MAKE_SET is for.
SELECT msg, MAKE_SET(level, 'fatal', 'warning', 'error', 'userError') AS levelTextCsv FROM warnings;
which will output your bitfield value as a single CSV column with text for each selected option.
Bitfields aren't ideal. Combining data into a single column is an anti-pattern in SQL. Should you encounter this in the wild, you'll know what to do.