我知道,这里经常讨论从mysql中删除重复项。但是在我的情况下,没有一个解决方案可以正常工作。
因此,我有一个数据库,其地址数据几乎如下所示:
ID; Anrede; Vorname; Nachname; Strasse; Hausnummer; PLZ; Ort; Nummer_Art; Vorwahl; Rufnummer
ID 是主键且唯一。
我有这样的条目,例如:
1;Herr;Michael;Müller;Testweg;1;55555;Testhausen;Mobile;012345;67890
2;Herr;Michael;Müller;Testweg;1;55555;Testhausen;Fixed;045678;877656
不同的电话号码不是问题,因为它们与我无关。所以我只想删除姓氏、街道和邮政编码中的重复项。在这种情况下,ID 1 或 ID 2。两者中的哪一个无关紧要。
我实际上尝试了这样的删除:
DELETE db
FROM Import_Daten db,
Import_Daten dbl
WHERE db.id > dbl.id AND
db.Lastname = dbl.Lastname AND
db.Strasse = dbl.Strasse AND
db.PLZ = dbl.PLZ;
并插入到复制表中:
INSERT INTO Import_Daten_1
SELECT MIN(db.id),
db.Anrede,
db.Firstname,
db.Lastname,
db.Branche,
db.Strasse,
db.Hausnummer,
db.Ortsteil,
db.Land,
db.PLZ,
db.Ort,
db.Kontaktart,
db.Vorwahl,
db.Durchwahl
FROM Import_Daten db,
Import_Daten dbl
WHERE db.lastname = dbl.lastname AND
db.Strasse = dbl.Strasse And
db.PLZ = dbl.PLZ;
完整的表包含超过 10Mio 行。尺寸实际上是我的问题。mysql在Macbook上的MAMP服务器上运行,具有1,5GHZ和4GB RAM。所以不是很快。SQL 语句在 phpmyadmin 中运行。实际上我没有其他系统可能性。
您可以编写一个存储过程,每次选择不同的数据块(例如,在两个值之间rownumber
),然后仅从该范围中删除。这样,您将慢慢一点一点地删除重复项
更有效的双表解决方案如下所示。我们只能存储我们真正需要删除的数据,并且只能存储包含重复信息的字段。假设我们正在 姓氏 、 分支、豪舒默 字段中查找重复数据。
创建表以保存重复数据
DROP TABLE data_to_delete;
用我们需要删除的数据填充表(我假设所有字段都有 VARCHAR(255) 类型)
CREATE TABLE data_to_delete (
id BIGINT COMMENT 'this field will contain ID of row that we will not delete',
cnt INT,
Lastname VARCHAR(255),
Branche VARCHAR(255),
Hausnummer VARCHAR(255)
) AS SELECT
min(t1.id) AS id,
count(*) AS cnt,
t1.Lastname,
t1.Branche,
t1.Hausnummer
FROM Import_Daten AS t1
GROUP BY t1.Lastname, t1.Branche, t1.Hausnummer
HAVING count(*)>1 ;
现在让我们删除重复数据,只留下所有重复集的一条记录
DELETE Import_Daten
FROM Import_Daten LEFT JOIN data_to_delete
ON Import_Daten.Lastname=data_to_delete.Lastname
AND Import_Daten.Branche=data_to_delete.Branche
AND Import_Daten.Hausnummer = data_to_delete.Hausnummer
WHERE Import_Daten.id != data_to_delete.id;
DROP TABLE data_to_delete;
您可以添加新列,例如 uq
并使其UNIQUE
.
ALTER TABLE Import_Daten
ADD COLUMN `uq` BINARY(16) NULL,
ADD UNIQUE INDEX `uq_UNIQUE` (`uq` ASC);
完成此操作后,您可以执行如下UPDATE
查询
UPDATE IGNORE Import_Daten
SET
uq = UNHEX(
MD5(
CONCAT(
Import_Daten.Lastname,
Import_Daten.Street,
Import_Daten.Zipcode
)
)
)
WHERE
uq IS NULL;
更新所有条目并再次执行查询后,所有重复项将具有值 = NULL
的 uq
字段,并且可以删除。
结果是:
0 row(s) affected, 1 warning(s): 1062 Duplicate entry...
对于新添加的行,请始终创建uq
哈希,并在所有条目唯一后考虑将其用作主键。