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

Question

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.

FuzzyTree · Accepted Answer · 2014-06-26 18:21:47Z

2

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.

edited Jun 26, 2014 at 18:21

answered Jun 26, 2014 at 17:42

FuzzyTree

32.4k3 gold badges58 silver badges87 bronze badges

Sign up to request clarification or add additional context in comments.

1 Comment

jesse_galley Over a year ago

It will mostly be used for reporting purposes, there will be a few tests made against it, but there are some interesting ways to work around the efficiency issues with bitwise fields in MySQL[1], so I'm not entirely worried about it. Your answer looks good, and I'll most likely accept it after doing some tests. [1] explainextended.com/2009/10/01/bitwise-operations-and-indexes

Collectives™ on Stack Overflow

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

1 Answer 1

1 Comment

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

1 Comment

Your Answer

Sign up or log in

Post as a guest

Related