Logo Questions Linux Laravel Mysql Ubuntu Git Menu
 

Mysql Bitwise operations and filter

I try to implement a bitwise filter using MYSQL (with udf if needed)

The filter is something like a AND but I want to use the mask to build a new bit string... Let me explain you with a sample :

Suppose I have a table with blob storing 8 bit streams:

  • data1: 10110110
  • data2: 01100010
  • data3: 00010011

Then I have a mask to apply to get the bits from data when mask value is 1

  • MASK: 00101011

And so get the following expected results:

  • data1: 1010
  • data2: 1010
  • data3: 0011

Is there a way to optimize the filtering, without looping on each bit of "mask" to get the corresponding value in "data" row...

CLARIFICATION

I've just taken 8 bits for the post, but it's more like 256 bytes

for Joe : To clarify the exemple, the mask 00101011 is interpreted as : get the bit value from data field at position 3,5,7,8, if you read the mask from left to right, enumerated from bit 1 to bit 8... Hope this clarification is "clear"...

like image 419
Hubert Avatar asked Oct 03 '11 16:10

Hubert


People also ask

Which operator is used to perform bitwise XOR operations MySQL?

BIT_XOR() function in MySQL is used to return the bitwise XOR of all bits in a given expression. It first converts all decimal values into binary values, and then perform bitwise xor operation on those binary values.

What are bitwise operators in SQL?

Bitwise operators perform bit manipulations between two expressions of any of the data types of the integer data type category. Bitwise operators convert two integer values to binary bits, perform the AND, OR, or NOT operation on each bit, producing a result.


1 Answers

The only way I know of doing what you want is something like

SELECT ((data >> 2) & 8) | ((data >> 1) & 4) | (data & 3) FROM ...

Obviously, you'll have to construct the expression based on your mask; it's not very difficult to do, just a bit tedious — you basically need to loop over the bits in the mask, something like this:

var mask = 0b00101011;
var parts = new Array();
var shift = 0;
var unshift = 0;
while (mask > 0) {
    while ((mask & 1) == 0) {
        shift = shift + 1;
        mask = mask >> 1;
    }
    submask = 0;
    while ((mask & 1) == 1) {
        submask = submask + (1 << unshift);
        unshift = unshift + 1;
        mask = mask >> 1;
    }
    parts.push( "((data >> " + shift + ") & " + submask + ")" );
}
var expr = parts.join( " | " );
console.log(expr);

The example code above is in JavaScript, so you can run it as a snippet here and get:

((data >> 0) & 3) | ((data >> 1) & 4) | ((data >> 2) & 8)

logged to the console, but it should be pretty easy to port to other languages.

like image 124
Ilmari Karonen Avatar answered Sep 24 '22 02:09

Ilmari Karonen