将大量记录插入有关系且没有重复的数据库

Question

我从第 3 方获得了一些记录（大约 30k），其结构与此类似（所有字符串）：

first_name, last_name, city

另外我在 MySQL 中有 2 个表：用户和城市（大约 25k 条记录）。users 表有一个到城市表的外键。

我需要users用来自 api 的记录和citites表的外键来填充表。如果城市不存在，我需要创建它。所以我的代码是这样的：

<?php

$users = $api->getUsers();

$existingUsers = $this->userRepository->getIds();

$existingCities = $this->cityRepository->geIdsIndexedByName();

$db->beginTransaction();

foreach ($users as $i => $user) {

  // if no city with such name, then create new and insert to array

  if (!array_key_exists($user['city'], $existingCities) { 

    $cityId = $db->insert('cities', ['name' => $user['city']]);

    $existingCities[$user['city_id']] = $cityId;

  }

  $user['city_id'] = $existingCities[$user['city']];

  if (in_array($user['id'], $existingUsers) { // if record with such id exists, then we update it

      $db->update('users', $user);

  } else {

      $db->insert('users', $user);

  }

  if (($i % 100) === 0) { // use transactions to avoid mass inserts and updates

    $db->commit();

    $db->beginTransaction();

  }

}

我不喜欢的是，我必须将所有城市和所有用户加载到内存中以检查记录是否已经存在。我这里不使用 ORM，没有对象，只有很小的数组，但它仍然会占用资源，我想减少内存消耗。是否有任何实践可以优化该过程？

我有一个想法，将 NoSQL 存储用作现有用户和引用的缓存，但这是不允许的。

Answer 1

如果我理解正确，为了将新列表与旧列表匹配，您将必须匹配城市名称。虽然这并不理想，但它可能是唯一可用的解决方案。如果您的城市名称是唯一的，那没问题。但如果它们不是唯一的（Springfield AK、Springfield CA、Springfield CO、Springfield GA、Springfield ID、Springfield IL、Springfield IN 等），您将需要更多信息来匹配。此外，拼写错误（springfield vs springfiled）将破坏您规范化数据库的原因......第一步，将新信息添加到新表中。我将其称为新信息...newinfo----------first_namelast_namecity因此假设城市名称是唯一的，下一步是将所有新的唯一城市名称添加到cities. 如果你有一个唯一的索引cities.name，你可以简单地insert ignore into cities('name') select city from newinfo&nbsp;&nbsp;否则，您必须加入表以查找新值：insert into cities ('name')&nbsp;select newinfo.city&nbsp;from newinfo&nbsp;left join cities on newinfo.city=cities.name&nbsp;where cities.name is null现在您的cities表中有所有可能的城市，您需要插入所有新名称。考虑如何获取需要插入的信息：select newinfo.first_name, new info.last_name, cities.id&nbsp;from newinfo&nbsp;inner join cities on new info.city=cities.name然后，当您对获得正确的数据感到满意时，插入它：insert into users&nbsp;values('first_name','last_name','city_id')&nbsp;select newinfo.first_name, new info.last_name, cities.id&nbsp;from newinfo&nbsp;inner join cities on new info.city=cities.name&nbsp;现在您已经完成了表 newinfo 并且可以将其删除。