Mysql 按位运算和过滤
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/7637922/
Warning: these are provided under cc-by-sa 4.0 license. You are free to use/share it, But you must attribute it to the original authors (not me):
StackOverFlow
Mysql Bitwise operations and filter
提问by Hubert
I try to implement a bitwise filter using MYSQL (with udf if needed)
我尝试使用 MYSQL 实现按位过滤器（如果需要，使用 udf）
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 :
过滤器有点像 AND 但我想使用掩码来构建一个新的位串......让我用一个示例来解释你：
Suppose I have a table with blob storing 8 bit streams:
假设我有一个存储 8 位流的 blob 表：
- data1: 10110110
- data2: 01100010
- data3: 00010011
- 数据1：10110110
- 数据2：01100010
- 数据3：00010011
Then I have a mask to apply to get the bits from data when mask value is 1
然后我有一个掩码可用于在掩码值为 1 时从数据中获取位
- MASK: 00101011
- 面具：00101011
And so get the following expected results:
因此得到以下预期结果：
- data1: 1010
- data2: 1010
- data3: 0011
- 数据 1：1010
- 数据 2：1010
- 数据 3：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
我刚刚为帖子取了 8 位，但它更像是 256 个字节
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"...
对于 Joe ：为了澄清示例，掩码 00101011 被解释为：从位置 3、5、7、8 的数据字段中获取位值，如果您从左到右读取掩码，从位 1 到位 8 枚举。 ..希望这个澄清是“清楚的”......
回答by Johan
You can use bitwise operators in MySQL:
您可以在 MySQL 中使用按位运算符：
http://dev.mysql.com/doc/refman/5.0/en/bit-functions.html
http://dev.mysql.com/doc/refman/5.0/en/bit-functions.html
Example:
例子：
SELECT (data1 & b'00101011') as output1 FROM ......
Quick test:
快速测试：
SELECT (b'10110110' & b'00101011') as output1
This does a bitwise ANDwith the binary pattern of the mask you specified.
See the above link for more toys.
这对AND您指定的掩码的二进制模式按位执行。
更多玩具请看上面的链接。
回答by Ilmari Karonen
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:
上面的示例代码是用 JavaScript 编写的，因此您可以在此处将其作为片段运行并获得：
((data >> 0) & 3) | ((data >> 1) & 4) | ((data >> 2) & 8)
logged to the console, but it should be pretty easy to port to other languages.
登录到控制台，但移植到其他语言应该很容易。
