MySQL批量加载以自增字段为外键的多表

Question

考虑以下人为的示例，其中 FOREST 包含 TREE，并且 TREE 具有 BRANCH>(es)。此外，FLOCK包含BIRD，并且BIRD可能位于也可能不在BRANCH上>.

CREATE TABLE 'FOREST' (
forest_id INT(11) NOT NULL AUTO_INCREMENT,
'name'    VARCHAR(45) NOT NULL,
...
)

CREATE TABLE 'TREE' (
'tree_id'    INT(11) NOT NULL AUTO_INCREMENT,
'forest_id'  INT(11) NOT NULL ,
'tree_loc_x' INT NOT NULL,
'tree_loc_y' INT NOT NULL,
...
CONSTRAINT 'fk_tree_forest'
    FOREIGN KEY ('forest_id' )
    REFERENCES `FOREST` ('forest_id' )
)

CREATE TABLE 'BRANCH' (
'branch_id' INT(11) NOT NULL AUTO_INCREMENT,
'tree_id'   INT(11) NOT NULL,
'br_loc_x'  INT NOT NULL,
'br_loc_y'  INT NOT NULL,
'br_loc_z'  INT NOT NULL,
...
CONSTRAINT 'fk_branch_tree'
   FOREIGN KEY ('tree_id' )
   REFERENCES `TREE` ('tree_id' )
)

CREATE  TABLE 'FLOCK' (
  'flock_id' INT NOT NULL AUTO_INCREMENT ,
  'name'     VARCHAR(45) NOT NULL
...
)

CREATE  TABLE 'BIRD' (
  'bird_id'   INT(11) NOT NULL AUTO_INCREMENT ,
  'flock_id'  INT(11) NOT NULL ,
  'branch_id' INT(11) NULL ,
  'bird_tag'  VARCHAR(45) NOT NULL ,
...
  CONSTRAINT 'fk_bird_flock'
    FOREIGN KEY ('flock_id' )
    REFERENCES 'FLOCK' ('flock_id' )
  CONSTRAINT 'fk_bird_branch'
    FOREIGN KEY ('branch_id' )
    REFERENCES 'BRANCH' ('branch_id' )
 )

我想从 C++ 应用程序中使用批量类型加载(多插入语句或 LOAD DATA INFILE)来加载每个表。

检索数据库分配的 auto_increment 值以用作每个后续表加载的外键的最佳方法是什么。

请注意，表不一定以级联方式加载(即 BIRDS 将在 FLOCKS 之后加载，而不是直接在 BRANCHES 之后加载，因此在加载 BIRDS 相对于 BRANCHES 时“LAST_INSERT_ID”不会有用)。

每个表都有候选自然键，但我试图避免将它们用作主键或外键。

Answer 1

当您使用 LOAD DATA INFILE(或其他多行 INSERT，如 INSERT...SELECT)时，对 LAST_INSERT_ID() 的后续调用仅返回生成的第一个 id 值。但是 InnoDB 将 id 分配为连续 block ，因此如果您插入了 1000 行，并且 LAST_INSERT_ID() 报告 1234，那么您就知道数据加载使用了 id 1234 到 2233。

(假设 auto_incrment_increment = 1；如果不是，更准确的说法是您的数据加载将使用接下来的 1000 个连续 id。)

但是，如果您随后在树之后批量加载分支，则会遇到不知道每个分支属于哪棵树的问题。有些树可能只有一个分支，有些则有六个分支，等等。仅仅因为您有 1000 棵树和 1000 个分支，您不一定会假设均匀分布。如果 Twig 上有鸟等，情况就会变得更加复杂。

最终，将数据批量加载到多个表中并保留所有相应的生成 ID 是不切实际的。您确实必须自己编写代码来逐行循环所有输入文件，并逐行捕获 LAST_INSERT_ID() 以便在相关行中使用。我还没有找到更好的解决方案。

对于更复杂的情况，即鸟类对它们各自的分支和它们的羊群都有外键，您可以尝试跟踪每个分支的插入 ID，并将其映射到自然键，但也许有太多分支认为将该映射保留在内存中变得不切实际。在一定规模下，您会发现在插入分支或群体时可以选择插入鸟类，但不能同时插入两者(因为分支和群体之间存在多对多关系，您可以不要假设它们排序得很好)。因此，插入鸟可能需要为它们的分支或它们的羊群保留 NULL，然后您必须稍后使用 UPDATE 来填充缺失的数据。