时间戳

首页 > 解决方案 > 如何在不将这些列作为主键或唯一列的情况下对 2 个值的组合执行类似插入忽略的操作？

mysql - 如何在不将这些列作为主键或唯一列的情况下对 2 个值的组合执行类似插入忽略的操作？

问题描述

我有一张像

create table dados(
    id INT AUTO_INCREMENT,
    id2 VARCHAR(18) NOT NULL,
    id3 VARCHAR(18) NOT NULL,
    ...
    PRIMARY KEY (id));

我需要允许具有相同组合（id2，id3）的行，但我想将数据本地 infile 加载到此表中，从而跳过这些组合的匹配项。

我试过将文件加载到临时表中

create temporary table temp_tbl like dados;

从那里我尝试了这些（这里的语法不一定正确）：

insert into dados select * from temp_tbl where not exists 
( select 1 from dados where dados.id2=temp_tbl.id2 and dados.id3=temp_tbl.id3);

和

insert into dados select * from temp_table left join dados
on dados.id2=temp_table.id2 and dados.id3=temp_table.id3
where dados.id2 is null and dados.id3 is null;

但是这两个查询都需要永远，因为表有几百万个条目。

作为这些的替代方案，我考虑创建另一个列，该列的值要么为 id3，要么在重复组合 (id2,id3) 的情况下，将其设置为 id3_'此处的其他增量数字'，所以我会有

create table dados(
  id INT AUTO_INCREMENT,
  id2 VARCHAR(18) NOT NULL,
  id3 VARCHAR(18) NOT NULL,
  ...
  other_identification varchar(25) NOT NULL,
  PRIMARY KEY (id,other_identification));

但我想避免这种解决方案，因为它会在已经非常大的表中创建一个可能不必要的列。我想做的另一件事是让主键为 (id,id2,id3) 并且在插入时可能有一个 IGNORE 或一个 ON DUPLICATE KEY，但只使用 id2 和 id3 作为键，但是我找不到类似的东西这。

任何帮助将不胜感激。

标签： mysql

解决方案

无论您尝试哪种方法，如果没有索引，它都会很慢(id2, id3)。

我会做以下事情：

在原表中定义索引：

alter table dados add index (id2, id3);

创建具有唯一索引的临时表(id2, id3)。它实际上可以是主键：

create temporary table temp_tbl(
  id2 VARCHAR(18) NOT NULL,
  id3 VARCHAR(18) NOT NULL,
  ...
  PRIMARY KEY (id2, id3)

使用LOAD DATA INFILEwith IGNORE, 忽略 CSV 文件中的重复项：

LOAD DATA INFILE '/path/to/file.csv'
IGNORE INTO TABLE temp_tbl
...

使用 DELETE .. JOIN 查询删除现有的 (id2, id3) 组合：

delete t
from temp_tbl t
join dados d on d.id2 = t.id2 and d.id3 = t.id3;

将临时表中的数据插入到原始表中：

insert into dados (id2, id3, ...)
  select id2, id3, ...
  from temp_tbl;

请参阅db-fiddle.com 上的演示。

推荐阅读