如何比较(AND)代表mysql中的二进制掩码的两个字符串? [英] How do I compare (AND) two strings which represent a binary mask in mysql?

问题描述

我在 mysql 中有一个表，它存储(在其他列中)一个位掩码作为字符串，例如:

I have a table in mysql which stores (among other columns) a bitmask as a string, for example:

000100

我想执行一个查询，该查询将AND这些字段放在一起以提供一个结果，该结果显示任何两个掩码位置都为真.

I'd like to perform a query which will AND these fields together to provide a result that shows when any two mask positions are both true.

例如，考虑以下三个示例记录:

As an example, consider these three sample records:

id  name  mask
==  ====  ====
11  a     000100
12  a     000110
13  a     010000

这个查询是一个自连接.我正在寻找对于给定的 name，相同位掩码出现两次或更多次的记录.

This query is a self-join. I am looking for records where, for a given name, the same bitmask occurs twice or more.

在上面的例子中，符合这个条件的记录只有11和12(第四位都是1).

In the above example, the only records that match this condition are 11 and 12 (the fourth bit is 1 in both cases).

我遇到的问题是在掩码上执行 AND.由于它存储为字符串，我不确定如何让 mysql 将其视为二进制值.

The problem I am having is performing the AND on the mask. Since it is stored as a string, I am unsure how to get mysql to treat it as a binary value.

推荐答案

你可以使用conv，例如

select conv('1100', 2, 10) & conv('0110', 2, 10);

重新评论，它似乎对我有用:

Re comment, it seems to work for me:

mysql> select conv('1001', 2, 10) & conv('0110', 2, 10) = 0;
+-----------------------------------------------+
| conv('1001', 2, 10) & conv('0110', 2, 10) = 0 |
+-----------------------------------------------+
|                                             1 |
+-----------------------------------------------+
1 row in set (0.00 sec)

mysql> select conv('1001', 2, 10) & conv('0111', 2, 10) = 0;
+-----------------------------------------------+
| conv('1001', 2, 10) & conv('0111', 2, 10) = 0 |
+-----------------------------------------------+
|                                             0 |
+-----------------------------------------------+
1 row in set (0.00 sec)

这篇关于如何比较(AND)代表mysql中的二进制掩码的两个字符串?的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！