我们正在合并一些在不同阶段建立的独立公司数据库，它们都有大致相同的数据，但顺序/ID 不同。

这是 3 个数据库的伪叠加图，每个数据库有 2 个示例表: +------------+------------+--------------+ |数据库 1 | | | +------------+------------+--------------+ |水果编号 |姓名 | | | 1 |橙色 | | | 2 |苹果 | | | 3 |香蕉 | | | | | | |销售编号 | fruit_ids | | | 1924 年 | 2,3 |苹果、香蕉 | | 1925 年 | 1,3 |橘子，苹果 | | | | | |数据库 2 | | | |水果编号 |姓名 | | | 1 |苹果 | | | 2 |橙色 | | | 3 |香蕉 | | | | | | |销售编号 | fruit_ids | | | 1924 年 | 2,3 |橘子、香蕉 | | 1925 年 | 1,3 |苹果、香蕉 | | | | | |数据库 3 | | | |水果编号 |姓名 | | | 1 |香蕉 | | | 2 |苹果 | | | 3 |橙色 | | | | | | |销售编号 | fruit_ids | | | 1950 | 2,3 |苹果、橘子 | | 1951 | 1,3 |香蕉，橘子 | +------------+------------+--------------+

你会看到一些数据库sales_id实际上是重复的，甚至fruit_ids与每个表中的不同项目相关。

我们正在使用的真实数据库将在数据库的很多地方点缀 fruit_id，并且一些数据库包含 100 万行以上，因此手动 MySQL 查询有点不合时宜。

我们需要合并的实际数据库比上面的伪表复杂得多，但我想看看是否有任何逻辑/工具/软件可以帮助将MySQL数据库与这种类似的数据合并？

最佳答案

到目前为止，答案并不多。下面是一些初步的尝试，展示了在一些具有相同格式的数据库之间生成公共(public) key :

// Source databases or companies
// -----------------------------
$corp = array();
$corp[0] = 'XYZ Corp';
$corp[1] = 'ABC Corp';
$corp[2] = '123 Corp';

// Source fruit lists
// ------------------
$fruit = array();

$fruit[0]['orange'] = 1;
$fruit[0]['apple']  = 2;
$fruit[0]['banana'] = 3;
$fruit[0]['kiwi']   = 4;

$fruit[1]['apple']  = 1;
$fruit[1]['orange'] = 2;
$fruit[1]['banana'] = 3;
$fruit[1]['pear']   = 4;

$fruit[2]['banana'] = 1;
$fruit[2]['apple']  = 2;
$fruit[2]['orange'] = 3;
$fruit[2]['grape']  = 4;

// Master fruit list
// -----------------
echo "Generating common fruit key list...<br>\n";

// Generate common keys in sorted item order
// -----------------------------------------
$common = array();
foreach ($corp as $corpid => $name)
{
  echo "<br>\n";
  echo "... examining fruit keys for " . $corp[$corpid] . "<br>\n";
  foreach ($fruit[$corpid] as $name => $id)
  {
    $common[$name] = 0;
    echo "... ... fruit $name has key $id<br>\n";
  }
}

ksort($common);

$i = 0;
foreach ($common as $name => $dummy )
   $common[$name] = ++$i;

echo "<br>\n";   
print_r($common);
echo "<br><br>\n";

// Demonstrate index conversions
// -----------------------------
echo "Demonstrating conversions to common indexes per company...<br>\n";

foreach ($corp as $corpid => $name)
{
  echo "<br>\n";
  echo "... examining key conversions for " . $corp[$corpid] . "<br>\n";
  foreach ($fruit[$corpid] as $name => $id)
  {
    $new = $common[$name];
    $old = $id;
    echo "... ... fruit $name key changes from $old to $new<br>\n";
  }
}

您将获得类似于以下内容的结果:

Generating common fruit key list...

... examining fruit keys for XYZ Corp
... ... fruit orange has key 1
... ... fruit apple has key 2
... ... fruit banana has key 3
... ... fruit kiwi has key 4

... examining fruit keys for ABC Corp
... ... fruit apple has key 1
... ... fruit orange has key 2
... ... fruit banana has key 3
... ... fruit pear has key 4

... examining fruit keys for 123 Corp
... ... fruit banana has key 1
... ... fruit apple has key 2
... ... fruit orange has key 3
... ... fruit grape has key 4

Array ( [apple] => 1 [banana] => 2 [grape] => 3 [kiwi] => 4 [orange] => 5 [pear] => 6 ) 

Demonstrating conversions to common indexes per company...

... examining key conversions for XYZ Corp
... ... fruit orange key changes from 1 to 5
... ... fruit apple key changes from 2 to 1
... ... fruit banana key changes from 3 to 2
... ... fruit kiwi key changes from 4 to 4

... examining key conversions for ABC Corp
... ... fruit apple key changes from 1 to 1
... ... fruit orange key changes from 2 to 5
... ... fruit banana key changes from 3 to 2
... ... fruit pear key changes from 4 to 6

... examining key conversions for 123 Corp
... ... fruit banana key changes from 1 to 2
... ... fruit apple key changes from 2 to 1
... ... fruit orange key changes from 3 to 5
... ... fruit grape key changes from 4 to 3

需要考虑的一些问题:

• 我认为您不能安全地就地更新表，因为键会像上面提供的那样发生冲突。如果您愿意，可以通过调整新键值使其不与任何现有键值重叠来避免这种情况。例如，在这种情况下，您可以向所有新的公共(public) key 添加一个整数，例如 100000。这可能允许对先前的键值进行“就地”更新——可能对早期测试或备用转换路径有用。
• 您需要一份明确的表列表才能应用转换。如果 ID 始终使用相同的名称，则可以通过扫描数据库表格式(可能通过描述表名)来自动执行此操作。
• 如果您确实在数据库中有一个以逗号分隔的 ID 值列表，如所演示的那样，您将需要做额外的工作来识别它们，解析它们并适本地更新它们。
• 您会注意到我为每家公司添加了一个独特的库存项目(即猕猴桃、梨、葡萄)，因为它们可能不都携带完全相同的产品集。

这应该为思考这个问题提供一个起点。很难说将所有源数据库处理成新数据库然后验证合并结果可能需要多长时间。

关于php - 合并 3 个具有相似数据但不同 ID 的 MySQL 数据库？，我们在Stack Overflow上找到一个类似的问题： https://stackoverflow.com/questions/31111509/