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

由于数据库结构的设计并没有考虑到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;

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