java - INSERT IGNORE 在 mysql 模式下使用 h2 引发主键冲突
问题描述
我正在将 Scopus 数据抓取到 h2 文件数据库中。数据中有超过 46,000,000 条记录,每条记录都被视为不同的,这意味着重复了数百 GB 的数据(因此是关系数据库)。为了减少所有这些数据的插入时间,我最初创建了一组没有约束的临时表,然后使用 SELECT DISTINCT 和 GROUP BY 将数据复制到真实表中以强制唯一性。
一个例外是文档表和参考文档表。由于数据的格式,我可以保证每条记录代表一个唯一的文档,所以我可以只插入到文档表中,然后只连接引用的文档表中的行,这些行的 ID 还没有在文档表中。
以下是相关代码:
CREATE TABLE document (docid varchar NOT NULL, title varchar, abstract varchar, docType varchar NULL, ref boolean);
CREATE TABLE refdoc (refid varchar NOT NULL, title varchar);
INSERT INTO document (docid, title, abstract, docType, ref)
VALUES ('2-s2.0-0000098715', 'title', 'abstract', 'ab', 'false');
INSERT INTO refdoc (refid, title)
VALUES ('2-s2.0-0000098715', 'title'),
VALUES ('2-s2.0-33947184743', 'title');
ALTER TABLE document
ADD PRIMARY KEY (docid);
ALTER TABLE document
ADD FOREIGN KEY (docType) REFERENCES doctype(abbrev);
INSERT IGNORE INTO document (docid, title, ref)
SELECT refid, title, 'true' FROM refdoc;
- 创建文档表
- 创建参考文档表
- 在文档表中插入一条记录
- 在 refdoc 表中插入两条记录,包括重复的
- 使用主键更改文档表
- 使用外键更改文档表
- 插入 refdoc 中与文档不冲突的行
INSERT IGNORE 查询抛出:org.h2.jdbc.JdbcSQLException:唯一索引或主键违规:“CONSTRAINT_INDEX_6 ON PUBLIC.DOCUMENT(DOCID)
我也尝试使用 WHERE NOT EXISTS:
INSERT INTO document (docid, title, ref)
SELECT refid, title, 'true'
FROM refdoc
WHERE NOT EXISTS (
SELECT refid FROM refdoc
INNER JOIN document
ON document.docid = refdoc.refid );
但似乎尝试连接没有索引的表实际上是不可能的——我尝试过的任何连接都没有奏效。
作为最后的手段,我可以使用 FileHashMap 并转储 refdoc 表的内容,然后构造一个巨大的 PreparedStatement,如:
INSERT INTO document (docid, title, ref)
SELECT ?, ?, 'true'
WHERE NOT EXISTS (
SELECT docid FROM document
WHERE docid = ? );
但我显然宁愿不这样做,因为这将花费很长时间。
解决方案
终于找到了一个不涉及构建100,000,000条记录的批处理语句的解决方案。问题是我需要强制我插入到文档中的 refdocs 尚未在文档表中,并且我只插入了 refdoc 表中的唯一行。在此之前我的所有解决方案要么未能避免冲突,未能强制执行唯一性,要么涉及没有索引的表上的连接。
这是解决方案SQL:
CREATE TABLE document (docid varchar NOT NULL, title varchar, abstract varchar, docType varchar NULL);
CREATE TABLE refdoc (refid varchar NOT NULL, title varchar);
INSERT INTO document (docid, title, abstract, docType)
VALUES ('2-s2.0-0000098715', 'title', 'abstract', 'ab');
INSERT INTO refdoc (refid, title)
VALUES ('2-s2.0-0000098715', 'title'),
VALUES ('2-s2.0-33947184743', 'title');
INSERT IGNORE INTO document (docid, title)
SELECT refid, MAX(title)
FROM refdoc
WHERE refid NOT IN (
SELECT docid FROM document )
GROUP BY refid;
ALTER TABLE document
ADD PRIMARY KEY (docid);
ALTER TABLE document
ADD FOREIGN KEY (docType) REFERENCES doctype(abbrev);
现在的逻辑是:
- 创建文档表
- 创建参考文档表
- 在文档表中插入一条记录
- 在 refdoc 表中插入两条记录,包括重复的
- 插入 refdoc 中与文档不冲突且唯一的行
- 使用主键更改文档表
- 使用外键更改文档表
这有一个额外的好处,即在插入完成之前不索引文档表。
我仍然不完全清楚为什么我在没有主键的表上违反了主键约束,但这听起来像是作为错误报告提交给 h2 github 的东西。
推荐阅读
- reactjs - 样式化反应流程图的最佳实践是什么?
- git - git 远程添加源 docker 容器
- python - IntegrityError:UNIQUE 约束失败:theatre_audience.ticketId
- output - 如何正确读取 VHDL 中的“部分未定义”信号
- php - 在 codeigniter 中使用 base_url 自动调用图像名称
- accessibility - 使用 pa11y 的命令行界面时不断收到超时错误和“taskkill”
- swift - 将 RealmSwift 数据迁移到 Core Data,移除 RealmSwift 依赖?
- javascript - 是否可以使用 js 生成 csrf 令牌并使用 php 进行验证?
- json - MariaDB 查询列到 JSON
- hadoop - 使用 hadoop 时无法打开 localhost:8088