问合并具有外键的两个数据库时的SQL问题

EN

编辑：

您的原始设计使用AUTO INCREMENT列作为主键。这样做的问题是，当您看到数据同时进入最终将被合并的独立数据库时，您最终会创建重复的键。

一种替代方法是在每个不同的数据库中使用一个序列。不幸的是，序列在mysql中不是本机可用的(许多其他数据库，如Oracle、db2都有序列--它们允许这种类型的分布式数据库插入没有冲突)。AUTO INCREMENT列不允许您想要的那种复制。

因此，您只有两个选择。

1)在设置location_id的主键中添加额外的部分(根据第一个答案)。

2)或者使用自己的序列手动生成插入的id，而不是使用AUTO INCREMENT列。

最好是将序列实现为一个存储的proc/function，它在获得序列中的下一个值时提交自己--这可能会导致值不被使用。这很好--如果您等到整个insert被提交时才提交序列号，这比争用要好。

主要的事情是，当你做第一次插入时，你使用存储的proc中的sequence #。当您有效地将数据复制到第二个数据库中时，您将在原始数据库的行中使用生成的序列#。并且该序列将保持在具有不同起始点的每个单独的db中，以防止冲突。

例如，在每个数据库中，您可能需要两部分：

1)保存每个命名序列的下一个可用序列号的表。(每个获取从序列中提取的主键的表都有一个条目)。2)使用下一个序列号访问和更新该表的函数。

一个示例实现是：

顺序表：

CREATE TABLE sequences (
  name varchar(30) NOT NULL,
  value int(10) unsigned,
  PRIMARY KEY (name)
) ENGINE=InnoDB

序列函数：

delimiter //
create function get_next_value(p_name varchar(30)) returns int
  deterministic
  sql security invoker
begin
  declare current_val integer;
    UPDATE sequences
    SET value = (@current_val:=value) + 1
    WHERE name = p_name;

  return @current_val;
end //
delimiter ;

主要的问题是，存储的函数需要是单个语句，以便它完成并立即提交(否则，您将对其进行锁定，这将导致您的事务随着订单的到来而堆叠在一起。如果您没有非常高的吞吐量，这不是一个大问题。

这个函数不是我写的--我是从这里复制的：http://www.bigdbahead.com/?p=185，更详细的信息请参考这里。(如果用户在这里找到我，我很乐意让他在这里写一个答案，并在这里给他适当的信任)。

现在，对于每个数据库，您使用不同的数字初始化值，以避免冲突。因此，对于位置A中的orders表，您可以使用以下命令进行初始化：

insert into sequences ('orders', 1);

在位置B中，您可以使用以下命令进行初始化：

insert into sequences ('orders', 1000000);

然后在这两个数据库中，在插入到orders中时，您将执行以下操作：

insert into orders (order_id, . . .)
select mysql.get_next_value('user_id'), . . . <hardcoded-values>

--

我还没有对这个解决方案进行过道路测试--把它当作我在回答中关于序列的建议的大纲。您应该跟进上面的博客条目链接，其中提供了更多细节，特别是关于如何在事务控制下工作的详细信息，请参阅评论(我从评论中获取了函数的形式，而不是原始函数)，当然，在负载下测试它。

如果工作区A和B都有不同的ID 23条目，那么您唯一的选择就是在记录更新到中央数据库时为每个条目创建一个辅键。然后，当您更新工作区数据库时，可以将该键重新复制回工作区数据库，这将允许使用真正唯一的标识符访问它们的项。

但是，我强烈建议您不要使用此方法！

正确的做法是创建一个在工作区1和2使用的web或桌面应用程序，它将连接到中央数据库，并将其用于所有数据访问。好的数据库设计通常意味着拥有尽可能少的重复数据副本。由于订单、库存等的多个副本散布在三个不同的数据库中，您正在为未来的数据损坏和/或丢失做准备--随着数据库规模的增长，这个问题只会变得越来越难以修复。在数据库变得更大之前，现在修复结构问题！

我想到了这个答案，但我仍然感到困惑，如果它比上面的其他解决方案更好。工作区B中的所有查询都立即在本地数据库上执行，并发送到中央数据库，然后发送到工作区A，但是工作区A上的查询不会立即在本地数据库上执行，它们被发送到中央数据库，然后中央将它们发送到工作区B，当工作区B执行这些查询时，它会通知中央，然后通知工作区A，然后工作区A可以执行存储的查询。因此，允许工作区B正常执行其查询，但是仅当工作区A知道工作区B何时执行了这些查询时，才允许工作区A执行其查询，因为在工作区B执行A的查询之前，它发送自己的新查询，然后执行A的查询，因此在通知A B执行其查询之后，它检查B是否发送了它自己的任何新查询并执行它们，然后它执行它自己的查询。这样，所有自动递增的id在两个工作区中都是相同的。示例：

工作区A：

         q1= insert into stock (name) values ('A')    not executed

         q2= insert into stock (name) values ('B')    not executed

A的数据库：(空)

工作区A将q1和q2发送到中心，并等待中心确认B已执行这些查询，以便它可以自己执行这些查询

工作区B：

         q3= insert into stock (name) values ('C')    executed id=1

         q4= insert into stock (name) values ('D')    executed id=2

数据库B：(1，'C')，(2，'D')

工作区B在将q3和q4发送到中心后，会收到q1和q2的通知。

工作区B执行q1 & q2

          q1= insert into stock (name) values ('A')   executed id=3

          q2= insert into stock (name) values ('B')   executed id=4

数据库B：(1，'C')，(2，'D')，(3，'A')，(4，'B')

工作区A被通知B已经执行了它的q1 & q2，但是它被告知必须先执行q3 & q4，然后才能执行q1 & q2

工作区A：

          q3= insert into stock (name) values ('C')    executed id=1

          q4= insert into stock (name) values ('D')    executed id=2

          q1= insert into stock (name) values ('A')    executed id=3

          q2= insert into stock (name) values ('B')    executed id=4

B的数据库：(1，'C')，(2，'D')，(3，'A')，(4，'B')