php - 如何用PHP和外键导入 "a lot"的数据到MySQL？

Question

我有这些表:

create table person (
    person_id int unsigned auto_increment, 
    person_key varchar(40) not null, 
    primary key (person_id), 
    constraint uc_person_key unique (person_key)
) 
-- person_key is a varchar(40) that identifies an individual, unique 
-- person in the initial data that is imported from a CSV file to this table

create table marathon (
    marathon_id int unsigned auto_increment,  
    marathon_name varchar(60) not null, 
    primary key (marathon_id) 
)

create table person_marathon (
    person_marathon _id int unsigned auto_increment,  

    person_id int unsigned, 
    marathon_id int unsigned,

    primary key (person_marathon_id),
    foreign key person_id references person (person_id), 
    foreign key marathon_id references person (marathon_id),

    constraint uc_marathon_person unique (person_id, marathon_id)  
)

Person 表由包含大约 130,000 行的 CSV 文件填充。此 CSV 包含每个人的唯一 varchar(40) 和其他一些人数据。 CSV 中没有 ID。

对于每场马拉松比赛，我都会得到一个 CSV，其中包含 1000 到 30000 人的列表。 CSV 基本上只包含 person_key 的列表。显示哪些人参加了该特定马拉松的值(value)观。

将数据导入 person_marathon 的最佳方式是什么？表维护FK关系？

这些是我目前能想到的想法:

• 拉 person_id + person_key从 MySQL 中取出信息并合并 person_marathon PHP 中的数据获取 person_id在插入 person_marathon 之前在那里表格

• 使用临时表进行插入...但这是为了工作，我被要求永远不要在这个特定的数据库中使用临时表

• 不要使用 person_id完全使用 person_key领域，但我将不得不加入 varchar(40)这通常不是一件好事

• 或者，对于插入，让它看起来像这样(我必须插入 <hr> 否则它不会将整个插入格式化为代码):

  insert  into person_marathon 
  
  select  p.person_id, m.marathon_id
  
  from    ( select 'person_a' as p_name, 'marathon_a' as m_name union 
            select 'person_b' as p_name, 'marathon_a' as m_name ) 
            as imported_marathon_person_list 
  
          join person p 
             on p.person_name = imported_marathon_person_list.p_name
  
          join marathon m 
             on m.marathon_name = imported_marathon_person_list.m_name
  

  该插入的问题是要用 PHP 构建它，imported_marathon_person_list将是巨大的，因为它很容易达到 30,000 select union项目。不过，我不确定该怎么做。

Answer 1

我处理过类似的数据转换问题，但规模较小。如果我正确理解你的问题(我不确定)，听起来让你的情况具有挑战性的细节是这样的:你试图在同一步骤中做两件事:

• 从 CSV 中导入大量行到 mysql，并且
• 进行转换，使 person-marathon 关联通过 person_id 和 marathon_id 工作，而不是(笨拙且不受欢迎的)varchar personkey 列。

简而言之，我会尽一切可能避免在同一步骤中同时执行这两项操作。将其分为这两个步骤 - 首先以可接受的形式导入所有数据，然后再对其进行优化。 Mysql 是执行此类转换的良好环境，因为当您将数据导入 persons 和 marathons 表时，ID 已为您设置。

第一步:导入数据

• 我发现在 mysql 环境中执行数据转换比在其外部更容易。因此，将数据导入 mysql，以一种保留个人-马拉松关联的形式，即使它不是最佳的，并担心事后更改关联方法。
• 您提到了临时表，但我认为您不需要。在 persons_marathons 表上设置临时列“personkey”。当你导入所有的关联时，你现在将 person_id 留空，只导入 personkey。重要的是，确保 personkey 是关联表和人员表上的索引列。然后你可以稍后再过一遍，为每个personkey填写正确的person_id，不用担心mysql效率低下。
• 我不清楚马拉松比赛表数据的性质。你有成千上万的马拉松比赛要参加吗？如果是这样，我不会羡慕您在每场马拉松比赛中处理 1 个电子表格的工作。但如果它更少，那么您也许可以手动设置马拉松表。让 mysql 为您生成马拉松 ID。然后，当您为每个马拉松导入 person_marathon CSV 时，请务必在与该马拉松相关的每个关联中指定该马拉松 ID。

完成数据导入后，您将拥有三个表:* persons - 你有丑陋的 personkey，以及一个新生成的 person_id，加上任何其他字段* marathons - 此时你应该有一个 marathon_id，对吧？要么是新生成的，要么是您从某个旧系统中继承的数字。* persons_marathons - 该表应该填写 marathon_id 并指向 marathons 表中的正确行，对吗？您还有 personkey(丑陋但存在)和 person_id(仍然为空)。

第二步:使用personkey为关联表中的每一行填写person_id

然后您要么直接使用 Mysql，要么编写一个简单的 PHP 脚本，为 persons_marathons 表中的每一行填写 person_id。如果我无法让 mysql 直接执行此操作，我通常会编写一个 php 脚本来一次处理一行。这样做的步骤很简单:

1. 查找 person_id 为 null 但 personkey 不为 null 的任意 1 行
2. 查找那个 personkey 的 person_id
3. 在该行的关联表中写入该 person_id

您可以告诉 PHP 重复这 100 次然后结束脚本，或者 1000 次，如果您不断遇到超时问题或类似的问题。

此转换涉及大量查找，但每次查找只需要针对一行。这很吸引人，因为您在任何时候都不需要要求 mysql(或 PHP)“将整个数据集放在头脑中”。

此时，您的关联表中的每一行都应该填入 person_id。现在可以安全地删除 personkey 列，瞧，你有了高效的外键。