CODEWITHSUNDEEP
mysqlsqlquery-optimization
jesse_galley
jesse_galley

Reputation: 1676

MySQL - Query to split an integer bitwise field into a boolean column for each bit

Given the following example data:

mysql> select * from test2;
+-------+--------+
| name  | status |
+-------+--------+
| bob   |      0 |
| jim   |      2 |
| karen |      5 |
| jane  |      1 |
| roy   |      7 |
+-------+--------+

I want to come up with a query which will split out the value of the status column into a pseudo-boolean value column for each individual bit of the integer.

What I do now is to have the application generate a query that uses a case statement and bitwise and operator for each column to get the result, like so:

mysql> select 
    ->     name, 
    ->     (case when (status & 1) then 'yes' else 'no' end) as bit1, 
    ->     (case when (status & 2) then 'yes' else 'no' end) as bit2, 
    ->     (case when (status & 4) then 'yes' else 'no' end) as bit4, 
    ->     (case when (status & 8) then 'yes' else 'no' end) as bit8 
    -> from test2;
+-------+------+------+------+------+
| name  | bit1 | bit2 | bit4 | bit8 |
+-------+------+------+------+------+
| bob   | no   | no   | no   | no   |
| jim   | no   | yes  | no   | no   |
| karen | yes  | no   | yes  | no   |
| jane  | yes  | no   | no   | no   |
| roy   | yes  | yes  | yes  | no   |
+-------+------+------+------+------+
5 rows in set (0.00 sec)

Now this works, and does so efficiently since there are no sub queries or joins, but it's not very elegant.

I would like a more elegant solution to do this dynamically without having to create a case statement and test for every bit.

This is example data with only a few bits, but in the implementation proper, there will be tens of thousands of rows, and many more bits that can be set.

Bonus:

Base the generation of the columns (column name and test) off of a table containing the definition of each bit like the following:

mysql> select * from bit_spec;
+-----+------+
| bit | desc |
+-----+------+
|   1 | bit1 |
|   2 | bit2 |
|   4 | bit3 |
|   8 | bit4 |
+-----+------+

Currently, the application handles this, and it's not a hard requirement that this processing be done by the MySQL server instead, but it would be advantageous if it was.

Here's the SQL Fiddle containing the example data, example bit definition table, and my current solution.

Upvotes: 2

Views: 2385

Answers (1)

FuzzyTree
FuzzyTree

Reputation: 32402

This can be achieved by retrieving each user/bit/value combination into rows and then pivoting those rows into columns.

select 
  name, 
  `desc`,
  ((status & bit) > 0) value
from test2, bit_spec

The query above produces results in the following format

name  |  desc  |  value
bob   |  bit1  |  0
bob   |  bit2  |  0

From here we can pivot the results into the desired format where each desc/value pair is converted into a column named desc with value value.

Using the following post https://stackoverflow.com/a/12005676/3574819

SET @sql = NULL;
SELECT
  GROUP_CONCAT(DISTINCT
    CONCAT(
      'SUM(case when `desc` = ''',
      `desc`,
      ''' then value end) AS ',
      replace(`desc`, ' ', '')
    )
  ) INTO @sql
from bit_spec;

SET @sql = CONCAT('SELECT t1.name, ', @sql, ' from
(select 
  name, 
  `desc`,
  ((status & bit) > 0) value
from test2, bit_spec) t1
group by t1.name
order by t1.name');

PREPARE stmt FROM @sql;
EXECUTE stmt;
DEALLOCATE PREPARE stmt;

http://sqlfiddle.com/#!2/5f6d0/22

NAME        BIT1    BIT2    BIT3    BIT4
bob         0       0       0       0
jane        1       0       0       0
jim         0       1       0       0
karen       1       0       1       0
roy         1       1       1       0

Sidenote

If you need to search by permission (i.e. show all users that have bit1 & bit2 but not bit3) then I don't recommend storing your permissions as bits in a single integer because the db can't index the results. In that case an indexed join table consisting of columns (user_id, permission_id) would yield faster results.

Upvotes: 2

Related Questions