php – 避免来自不同MySQL数据库的主键冲突

发布时间：2020-12-13 16:48:31 所属栏目：PHP教程来源：网络整理

导读：我有几台服务器运行自己的特定 MySQL数据库实例,遗憾的是无法在复制/集群中设置.每个服务器将数据插入到几个与用户相关的表中,这些表之间具有外键约束(例如user,user_vote).以下是该过程的进展：所有服务器都以相同的数据开头每个服务器独立于其他服务器生

我有几台服务器运行自己的特定 MySQL数据库实例,遗憾的是无法在复制/集群中设置.每个服务器将数据插入到几个与用户相关的表中,这些表之间具有外键约束(例如user,user_vote).以下是该过程的进展：

>所有服务器都以相同的数据开头
>每个服务器独立于其他服务器生成自己的数据集
>周期性地,来自所有服务器的数据被手动合并并应用回每个服务器(因此该过程从步骤1开始重复).

这是因为除了主键之外,用户表还包含一个唯一的电子邮件字段,该字段允许识别每个数据库中已存在的用户,并在更改主键和外键时合并新用户以避免冲突和维护正确的外键约束.它可以工作,但这是相当费力的,因为必须改变主键和外键以避免碰撞,因此我的问题是：

有没有办法让每个服务器使用不与其他服务器冲突的主键来促进合并？

我最初想要使用复合主键(例如server_id,id),但我使用的是Doctrine which doesn’t support primary keys composed of multiple foreign keys,因此我的外键约束会出现问题.

我想过使用VARCHAR作为id并使用字符串的一部分作为前缀(SERVER1-1,SERVER1-2,SERVER2-1,SERVER2-2 ……)但我认为它会使DB变慢我将不得不对id进行一些操作(例如,在插入时,我必须解析现有的id并提取最高,增加它,将它与服务器id连接起来……).

PS：另一种选择是使用从从设备读取并写入主设备来实现复制,但是由于诸如主设备上的复制延迟和单点故障等问题现在无法解决,因此该选项被丢弃.

解决方法

您可以确保每个服务器使用不同的自动增量增量和不同的起始偏移量：

Change the step auto_increment fields increment by

(假设您使用的是自动增量)

我只在两个服务器上使用过这个,所以我的设置有一个偶数id和一个奇数.

当它们合并在一起时,只要你确保所有表都遵循上述想法,就不会发生任何冲突.

为了实现4台服务器

你会说,设置以下偏移量：

>服务器1 = 1
>服务器2 = 2
>服务器3 = 3
>服务器4 = 4

您可以设置增量(我使用10来为额外的服务器留出空间)：

>服务器1 = 10
>服务器2 = 10
>服务器3 = 10
>服务器4 = 10

然后在合并之后,在复制回每个服务器之前,您只需要更新每个表的autoinc值以再次获得正确的偏移量.想象一下,每个服务器创建了100行,autoincs将是：

>服务器1 = 1001
>服务器2 = 1002
>服务器3 = 1003
>服务器4 = 1004

这是由于有四台服务器而变得棘手的地方.想象一下,某些表可能没有从特定服务器插入任何行.所以你可能最终得到一些表,它们的最后一个autoinc id不是来自服务器4,而是来自服务器2.这将使得解决任何特定表的下一个autoinc应该是非常棘手的.

因此,最好在每个表中都包含一个列,用于在插入任何行时记录服务器编号.

id | field1 | field2 | ... | server

这样,您可以通过在任何表上选择以下内容,轻松找出特定服务器的最后一个autoinc值：

SELECT MAX(id) FROM `table` WHERE `server`=4 LIMIT 0,1

使用此值,您可以在将合并数据集滚动到相关服务器之前,重置每个服务器上每个表所需的下一个autoinc值.

UPDATE information_schema.tables SET Auto_increment = (
  SELECT MAX(id) FROM `table` WHERE `server`=s LIMIT 0,1
)+n WHERE table_name='table' AND table_schema = DATABASE();

其中s是服务器编号,n设置为偏移量,因此在我的示例中它将是10.

（编辑：李大同）

【声明】本站内容均来自网络，其相关言论仅代表作者个人观点，不代表本站立场。若无意侵犯到您的权利，请及时与联系站长删除相关内容!