php – 合并3个具有相似数据但不同ID的MySQL数据库？

// 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

需要考虑的一些问题：

>我不认为您可以安全地更新表,因为密钥将如上所述发生冲突.如果您愿意,可以通过调整新键值以避免与任何现有键值重叠来避免这种情况.例如,在这种情况下,您可以向所有新公共密钥添加一个整数,例如100000.这可能允许对先前的键值进行“就地”更新 – 可能对早期测试或备用转换路径有用.
>您需要一个确定的表列表才能应用转换.如果ID始终使用相同的名称,则可以通过扫描数据库表格式(可能通过describe tablename)自动执行.
>如果你确实在数据库中有一个逗号分隔的ID值列表,如图所示,你将不得不做额外的工作来识别它们,解析它们并适当地更新所有这些.
>您会注意到我为每家公司(即奇异果,梨,葡萄)添加了一个独特的库存物品,因为它们可能并非全部都带有完全相同的产品组.

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