CODEWITHSUNDEEP
mysqlsqlbit-manipulation
Smartelf
Smartelf

Reputation: 879

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

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

Upvotes: 3

Views: 454

Answers (4)

txyoji
txyoji

Reputation: 6868

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

Upvotes: 0

Devart
Devart

Reputation: 122002

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;

Upvotes: 2

ajreal
ajreal

Reputation: 47321

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)

Upvotes: 0

lqez
lqez

Reputation: 3008

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)

Upvotes: 0

Related Questions