☰

根据使用一个字段的表达式对表进行重复数据删除

tag-icon • tag-icon mysql deduplication mariadb
根据使用一个字段的表达式对表进行重复数据删除 
Mariadb: mysql  Ver 15.1 Distrib 10.3.8-MariaDB

我有一个表，它是其他几个表的查询的联合。这个表看起来像-

+-------------+--------------+------+-----+---------+----------------+
| Field       | Type         | Null | Key | Default | Extra          |
+-------------+--------------+------+-----+---------+----------------+
| id          | int(11)      | NO   | PRI | NULL    | auto_increment |
| source_id   | int(11)      | YES  |     | NULL    |                |
| code        | varchar(64)  | YES  |     | NULL    |                |
| description | varchar(255) | YES  |     | NULL    |                |
+-------------+--------------+------+-----+---------+----------------+

代码字段可以在花括号中包含“额外”信息（将它们视为注释）。如果忽略注释，我想找到代码字段相等的所有行，然后从每个重复组中仅保留一行。

正是“保留每组一行”这一点给我带来了麻烦。

我有一个查询，它将列出每个重复组中所有行的 ID -

select id
from mytable
group by (regexp_replace(code, '(?-i){.*?}', ''))
having count(*) > 1

但如果我将其作为 DELETE WHERE 操作中的子查询，它将删除全部重复的。这不是我想要的。

在寻找“除一个之外的所有”或“从输出列表中弹出一个”函数后，我找到了带有 OFFSET 的 2 参数版本的 LIMIT -

select id
from mytable
group by (regexp_replace(code, '(?-i){.*?}', ''))
having count(*) > 1 limit 1000000 offset 1

但如果你把它放入 DELETE 命令中，它就会失败-

delete from mytable where id in(
    select id
    from mytable
    group by (regexp_replace(code, '(?-i){.*?}', ''))
    having count(*) > 1 limit 1000 offset 1
);
ERROR 1235 (42000): This version of MariaDB doesn't yet support 'LIMIT & IN/ALL/ANY/SOME subquery'

知道如何实现吗？最好是就地实现，但最坏的情况是，我可以选择临时表，然后替换原始表（如果需要的话）。

答案1

您已经知道如何识别重复项，因此我将集中精力于如何删除多余的行，即除每个值的第一行之外的所有行。

我们可以通过将想要保留的行的 ID 放在临时表中，然后删除 ID 不在此列表中的所有行来实现此目的。

我开发了一个小示例脚本以使其简单：

DROP TABLE IF EXISTS hasdupes;
CREATE TABLE hasdupes (
 Id INTEGER NOT NULL AUTO_INCREMENT PRIMARY KEY,
 Value VARCHAR(100)
) ENGINE=InnoDB;

INSERT INTO hasdupes (Value) VALUES ('red'), ('green'), ('blue'), ('blue'),
('green'), ('blue'), ('red'), ('orange'), ('red'), ('blue'), ('green'), ('white');

SELECT Value, COUNT(*) FROM hasdupes GROUP BY Value;
/*
blue    4
green   3
orange  1
red     3
white   1
*/

/* SELECTS an Id for each value in a temporary table */
CREATE TEMPORARY TABLE keep SELECT Id FROM hasdupes GROUP BY Value;
DELETE FROM hasdupes WHERE hasdupes.Id NOT IN (SELECT Id FROM keep);
DROP TEMPORARY TABLE keep;

SELECT Value, COUNT(*) FROM hasdupes GROUP BY Value;
/*
blue    1
green   1
orange  1
red     1
white   1
*/

答案2

我最初是通过将去重后的结果选入新表来实现这一点的

truncate dedup_table;
insert into dedup_table
    (source_id, source, code, description)
    select source_id, source, code, description
        from hasdupes_table
        where id in
        (select min(id) from hasdupes_table
         group by regexp_replace(code, '(?-i){.*?}', ''));

这基本上与@berty 推荐的方法相同，但使用子查询而不是临时表。

然后我意识到这可以按照我最初的意图工作，通过使用 NOT IN 反转子查询来找出要从“hasdupes”表中删除哪些行。

我还没有真正测试过，但我相信它会起作用-

delete from hasdupes_table where id not in
            (select min(id) from hasdupes_table
             group by regexp_replace(code, '(?-i){.*?}', ''));

相关内容