Break down a row into multiple rows using bitfield column in MySQL

Question

I have a bitfield called warnings stored as an int. I would like to get a list of warnings for each person

lets say we have a table

NAME,  WARNINGS
alex,  0
mike,  5
sarah, 2

where each bit of the integer corresponds to a warning # (bit position). This is currently done in perl with a for loop

for(my $i=0; $i < $warning_size;$i++ ){
     if( (1 << $i ) & $warning != 0){
         print "$name\t" . $i+1 ."\n";
     }
}

Is there any way that I can have this handled by a mysql query.

For the above example, I would like the following output:

name, warning
-------------
mike  1
mike  3
sarah 2

I am trying to get this down to one select statement,

Thanks

no, I don't have a typo. The problem is that I can not create a new or temporary tables. — Smartelf
– Smartelf, Commented Dec 15, 2011 at 16:22
can you explain where there are two mike ? You input only show one mike — ajreal
– ajreal, Commented Dec 15, 2011 at 16:37
warning is a bit field, so 5 = 101, 2 =010, etc... as you can see, mike has 2 bits set, that's why he is shown twice. — Smartelf
– Smartelf, Commented Dec 15, 2011 at 17:05
omg, that does not make sense, why there is single bit and three bit? — ajreal
– ajreal, Commented Dec 15, 2011 at 17:06

Devart · Accepted Answer · 2011-12-15 16:53:44Z

2

Try this one -

CREATE TABLE warn_bits(
  b INT(11) NOT NULL
);

INSERT INTO warn_bits VALUES 
  (1),
  (2),
  (3),
  (4),
  (5),
  (6),
  (7),
  (8);

SELECT w.name, wb.b FROM warnings w
  JOIN warn_bits wb
    ON ((w.WARNINGS >> wb.b - 1) & 1) > 0
ORDER BY w.name, wb.b;

+-------+---+
| name  | b |
+-------+---+
| mike  | 1 |
| mike  | 3 |
| sarah | 2 |
+-------+---+

You may extend warn_bits table to support INT or BIGINT numbers.

Edit2

SELECT w.name, wb.b FROM warnings w
  JOIN (
  SELECT 1 b UNION SELECT 2 UNION SELECT 3 UNION SELECT 4 UNION
  SELECT 5 UNION SELECT 6 UNION SELECT 7 UNION SELECT 8
  ) wb
    ON ((w.WARNINGS >> wb.b - 1) & 1) > 0
ORDER BY w.name, wb.b;

edited Dec 15, 2011 at 16:53

answered Dec 15, 2011 at 15:41

Devart

120k23 gold badges172 silver badges189 bronze badges

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

1 Comment

Smartelf Over a year ago

I can not create a new or temporary tables, that is why I am having so much trouble with it. Is there anyway to create a list of values in the select and alias it as a table, maybe with a sub_query?

lqez · Accepted Answer · 2011-12-15 15:44:39Z

0

Make a temporary table for bit-crushing.

mysql> create table bitcrush( bit int );
Query OK, 0 rows affected (0.02 sec)

mysql> insert into bitcrush values (1),(2),(4),(8),(16),(32),(64),(128),(256);
Query OK, 8 rows affected (0.01 sec)
Records: 8  Duplicates: 0  Warnings: 0

mysql> select name, log2(bit)+1 as warn from c821885 join bitcrush on warn & bit order by name;
+-------+------+
| name  | warn |
+-------+------+
| mike  |    1 |
| mike  |    3 |
| sarah |    2 |
+-------+------+
3 rows in set (0.00 sec)

answered Dec 15, 2011 at 15:44

lqez

3,0585 gold badges28 silver badges55 bronze badges

Comments

ajreal · Accepted Answer · 2011-12-15 15:50:14Z

0

I think you are just seeking for power(2, x);, try this :-

mysql> select length(conv(0, 10, 2));
+------------------------+
| length(conv(0, 10, 2)) |
+------------------------+
|                      1 |
+------------------------+
1 row in set (0.00 sec)

mysql> select length(conv(2, 10, 2));
+------------------------+
| length(conv(2, 10, 2)) |
+------------------------+
|                      2 |
+------------------------+
1 row in set (0.00 sec)

mysql> select length(conv(5, 10, 2));
+------------------------+
| length(conv(5, 10, 2)) |
+------------------------+
|                      3 |
+------------------------+
1 row in set (0.00 sec)

answered Dec 15, 2011 at 15:50

ajreal

47.4k11 gold badges97 silver badges119 bronze badges

Comments

txyoji · Accepted Answer · 2017-01-17 18:16:44Z

If you're ok with having a column per warning you could also use export_set() and substring() to break down the bits.

I'm going to assume you're using 4 bits.

bit 1 = "fatal"
bit 2 = "warning"
bit 3 = "error"
bit 4 = "user error"

SELECT
name,
EXPORT_SET(warning,1,0,'',4) AS debugBits,
SUBSTRING(EXPORT_SET(warning,1,0,'',4),1,1) AS hasFatal,
SUBSTRING(EXPORT_SET(warning,1,0,'',4),2,1) AS hasWarning,
SUBSTRING(EXPORT_SET(warning,1,0,'',4),3,1) AS hasError,
SUBSTRING(EXPORT_SET(warning,1,0,'',4),4,1) AS hasUserError
FROM warnings;

which should output something like:

------|----------|---------|-----------|---------|-------------|
name  |debugBits | hasFatal| hasWarning| hasError| hasUserError|
alex  |      0000|        0|          0|        0|            0|
mike  |      1010|        1|          0|        1|            0|
sarah |      0010|        0|          0|        1|            0|
------|----------|---------|-----------|---------|-------------|

Note that export_set() displays bit 1 on the far left. http://dev.mysql.com/doc/refman/5.5/en/string-functions.html#function_export-set

If you're ok with a CSV column for the warnings, you could use make_set()

SELECT
name,
MAKE_SET(warning,
'fatal',
'warning',
'error',
'userError') AS warningText
FROM warnings

which will get you one column for warnings with a CSV list of set bit labels. http://dev.mysql.com/doc/refman/5.5/en/string-functions.html#function_make-set

Collectives™ on Stack Overflow

Break down a row into multiple rows using bitfield column in MySQL

4 Answers 4

1 Comment

Comments

Comments

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

4 Answers 4

1 Comment

Comments

Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Related