代码之家 › 专栏 › 技术社区 › JYelton Melchior Blausand

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

bitmask duplicates mysql

2

JYelton Melchior Blausand · 技术社区 · 14 年前

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

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

掩码字段是位掩码,其中每个位置表示特定类型的修复。我成功地找到了比较位掩码的方法( per this question )但是要把它合并到一个查询中要比我想象的困难得多。

鉴于上述样本记录,只有id的11和12适用,因为它们都有一个 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不完整的查询。。。

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

有什么建议吗?

3 回复 | 直到 7 年前

1

2

Ryan Tenney 14 年前

由于数据库结构的设计并没有考虑到RDBMs的实际情况(可能不是你在做的,我只需要指出这一点),所以性能总是很差的,尽管可以编写一个可以完成的查询。

(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

一个非常基本的优化(同时保持整个表结构不变)是将mask字段转换为无符号整数数据类型,从而节省对 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;

2

0

msw 14 年前

在实列中分解掩码位。RDMB不喜欢位字段。

3

0

Jim Garrison 14 年前