如何比较（AND）mysql中表示二进制掩码的两个字符串？

发布于 2024-09-12 11:27:50 字数 487 浏览 9 评论 0原文

我在 mysql 中有一个表，它将位掩码存储为字符串（在其他列中），例如：

000100

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

作为示例，请考虑以下三个示例记录：

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

该查询是自联接。我正在查找对于给定的名称，相同的位掩码出现两次或多次的记录。

在上面的示例中，唯一符合此条件的记录是 11 和 12（这两种情况下第四位都是 1）。

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

原文

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

000100

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

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

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

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.

分享到QQ

分享到微博

如果你对这篇内容有疑问，欢迎到本站社区发帖提问参与讨论，获取更多帮助，或者扫码二维码加入 Web 技术交流群。

发布评论

需要登录才能够评论，你可以免费注册一个本站的账号。

小伙你站住 2024-09-19 11:27:50

您可以使用conv，例如。

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

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

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)

You can use conv, eg.

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)

回复收藏 0 原文

~没有更多了~