mysql - 基于位掩码在mysql中查找重复记录

Question

我有一个存储传感器维护日志的 mysql 表。我想设计一个查询来查找出于相同原因维修/维护给定传感器的实例。 (重复出现的问题查找器。)

我的表格(简化版)如下所示:

id    name     mask
==    ====     ====
11    alpha    0011
12    alpha    0010
13    alpha    0100
14    beta     0001

掩码字段是一个位掩码，其中每个位置代表一种特定类型的修复。我能够成功地弄清楚如何比较位掩码 ( per this question )，但事实证明，尝试将其合并到查询中比我想象的要困难。

给定上述示例记录，只有 id 的 11 和 12 适用，因为它们在第三个掩码位置都有一个 1。

以下是我尝试过的方法及其无效的原因:

---

<强>1。永不结束...
这个查询似乎永远运行，我不认为它按照我想要的方式工作。

SELECT t1.id, t1.name
FROM data t1
LEFT OUTER JOIN data t2
ON (CONV(t1.mask,2,10) & CONV(t2.mask,2,10) > 0)
GROUP BY t1.name
HAVING COUNT(*) >1;

<强>2。查询不完整...
我正在考虑创建一个 View ，以仅解析表中实际上有多个条目的传感器。我不确定从这里到哪里去。

SELECT COUNT(t1.name) AS times, t1.name, t1.id, t1.mask
FROM data AS t1
GROUP BY t1.name ASC
HAVING times > 1;

---

对此有什么建议吗？

Answer 1

由于数据库结构在设计时并没有考虑到 RDBM 的实际情况(可能不是你做的，无论如何我必须指出这一点......)，性能总是很差，尽管可以编写一个查询将完成。

Jim 是正确的，查询结果是笛卡尔积。如果要返回未分组和未过滤的查询，您可能期望 (SELECT POW(COUNT(*), 2) FROM data)结果。此外，任何形式的外部联接都是不必要的，因此标准的内部联接是您在这里想要的(并不是说它应该在性能方面有所不同，它只是更合适)。

还有另一个连接条件，t1.id != t2.id是必要的，以免每条记录匹配自身。

SELECT t1.id, t1.name
FROM data t1
JOIN data t2
     ON t1.name = t2.name
     AND t1.id != t2.id     //
WHERE CONV(t1.mask, 2, 10) & CONV(t2.mask, 2, 10) > 0
GROUP BY t1.name
HAVING COUNT(*) > 1;

您的查询不完整:

SELECT t1.id, t1.name, t1.mask
FROM data t1
WHERE t1.name IN (SELECT t2.name FROM data t2 GROUP BY t2.name HAVING COUNT(*) > 1);

SELECT t1.id, t1.name, t1.mask
FROM data t1
WHERE EXISTS (SELECT 1 FROM data t2 WHERE t2.name = t1.name GROUP BY t2.name HAVING COUNT(*) > 1);

在我的头脑中，我无法告诉你哪一个会表现最好。如果data.name被索引(我希望它是)，任何一个查询的成本都应该相当低。前者将缓存子选择的副本，而后者将对索引执行多个查询。

一个非常基本的优化(同时保持整个表结构不变)是将掩码字段转换为无符号整数数据类型，从而节省对 CONV() 的多次调用。 .

WHERE CONV(t1.mask, 2, 10) & CONV(t2.mask, 2, 10) > 0

成为

WHERE t1.mask & t2.mask > 0

当然，进一步分解数据确实更有意义。不是将位掩码存储在一个记录中，而是将所有的位分解成单独的记录

id    name     mask
==    ====     ====
11    alpha    1101

会变成

id    name    value
==    ====    =====
11    alpha       1
12    alpha       4
13    alpha       8

现在，在 name 上有一个战略性的索引和 value使查询变得小菜一碟

SELECT name, value
FROM data
GROUP BY name, value
HAVING COUNT(*) > 1;

希望对您有所帮助。