将平面文件加载到规范化的 MySQL 数据库中

Question

将平面文件中的数据加载到 MySQL 数据库中，然后通过外键创建表之间的关系的最快方法是什么？

例如...我有一个格式为以下的平面文件：

[INDIVIDUAL]   [POP]  [MARKER]  [GENOTYPE]

"INDIVIDUAL1", "CEU", "rs55555","AA"  
"INDIVIDUAL1", "CEU", "rs535454","GA"  
"INDIVIDUAL1", "CEU", "rs555566","AT"  
"INDIVIDUAL1", "CEU", "rs12345","TT"  
...  
"INDIVIDUAL2", "JPT", "rs55555","AT"  

我需要将其加载到四个表中：

IND (id,fk_pop,name)  
POP (id,population)  
MARKER (id,rsid)  
GENOTYPE (id,fk_ind,fk_rsid,call)  

具体来说，如何以可扩展的方式填充外键？ 这些数字大约有 1000 多个个体，每个个体都有超过 100 万个基因型。

Answer 1

您可以从没有外键的基表开始。 然后，您可以在向其他表中插入数据时查找 ID。

另一个想法是您可以用 GUID 替换平面文件中的 ID（INDIVIDUAL1、CEU，...等）。 然后直接使用它们作为 ID 和外键（我注意到这是标记的性能，这可能不会提供最佳的“性能”）。

Answer 2

我会采取多步骤方法来做到这一点。

1. 将数据加载到临时表中，该临时表与您
2. 编写查询以执行其他插入操作的文件格式相匹配，启动通用表，然后执行联接以获取 FK 值。

Answer 3

有一个更简单的方法。

首先，确保对那些应该有一个（名称、人口、rsid）的列有唯一约束。

然后使用如下所示的内容：

 LOAD DATA INFILE 'data.txt' IGNORE INTO TABLE POP FIELDS TERMINATED BY ','
    ENCLOSED BY '"' LINES TERMINATED BY '\n' IGNORE 1 LINES 
    (@name, population, @rsid, @call);
 LOAD DATA INFILE 'data.txt' IGNORE INTO TABLE MARKER FIELDS TERMINATED BY ',' 
    ENCLOSED BY '"' LINES TERMINATED BY '\n' IGNORE 1 LINES 
    (@name, @population, rsid, @call);
 LOAD DATA INFILE 'data.txt' IGNORE INTO TABLE IND FIELDS TERMINATED BY ',' 
    ENCLOSED BY '"' LINES TERMINATED BY '\n' IGNORE 1 LINES 
    (name, @population, @rsid, @call) 
    SET fk_pop = (SELECT id FROM POP WHERE population = @population);
 LOAD DATA INFILE 'data.txt' IGNORE INTO TABLE GENOTYPE FIELDS TERMINATED BY ',' 
    ENCLOSED BY '"' LINES TERMINATED BY '\n' IGNORE 1 LINES 
    (@name, @population, @rsid, call)
    SET fk_ind = (SELECT id FROM IND where name = @name),
    fk_rsid = (SELECT id FROM MARKER where rsid = @rsid);

请注意，@ 用于指示变量，而不是列名。 在前 2 个 LOAD DATA 中，这些仅用于忽略数据。 在第二个2中，它们用于查找外键。

请注意，可能不会很快：）。