mysql - 迁移行以导入现有数据库，处理更改 ID 等

Question

这样做的目的是将一些行从一个环境复制到另一个环境而不覆盖现有行。

示例数据库:

INSERT INTO `school` (school_id,name) VALUES (15,'Middle');
INSERT INTO `class` (class_id,school_id,name) VALUES (12,15,'Sample');

想法是 school_id 和 class_id 是自动递增的，class 有一个返回 school 的外键链接>。但我想转储只是这些行并将它们插入到另一个数据库中，该数据库的 school_id 已经是 15。

它可能看起来像:

INSERT INTO `school` (name) VALUES ('Middle');
INSERT INTO `class` (school_id,name) VALUES (LAST_INSERT_ID(),'Sample');

但这只是针对这个简单的示例。想象一下，如果我有 50 个类(class)，每个类(class)有 25 个学生，每个学生/类(class)组合有几百个成绩。如果不将 LAST_INSERT_ID() 存储在一系列变量中，您会发现它可能无法正常工作。

执行此类操作的合适工具是什么？ mysqldump 能做这么聪明的事情吗？

Answer 1

你可以这样做:

• 找到 MAX school_id在目标 school表 -

  SELECT MAX(school_id) INTO @max_school_id FROM school;

• 全部更改school_id源表中的值(school，class)- 添加 MAX school_id从上一点开始-

  UPDATE school SET school_id = school_id + @max_school_id + 1;

添加 ON UPDATE CASCADE 可能非常有用对外键的操作，这将有助于改变school_id自动在子表中，例如-

ALTER TABLE class
  DROP FOREIGN KEY FK_name;
ALTER TABLE class
  ADD CONSTRAINT FK_name FOREIGN KEY (school_id)
    REFERENCES school(school_id) ON UPDATE CASCADE;

• 转储并导入。

---

解释和例子:

创建源表:

CREATE TABLE school(
  school_id INT PRIMARY KEY AUTO_INCREMENT,
  name VARCHAR(20)
);

INSERT INTO school (school_id, name) VALUES
  (1, 'Middle1'),
  (2, 'Middle2'),
  (3, 'Middle3'),
  (15, 'Middle');

CREATE TABLE class(
  class_id INT(11) NOT NULL,
  school_id INT(11) DEFAULT NULL,
  name VARCHAR(20) DEFAULT NULL,
  PRIMARY KEY (class_id),
  CONSTRAINT FK_class_school_school_id FOREIGN KEY (school_id)
  REFERENCES school (school_id) ON DELETE RESTRICT ON UPDATE CASCADE
)
ENGINE = INNODB;

INSERT INTO class (class_id, school_id, name) VALUES (11, 1, 'Sample1');
INSERT INTO class (class_id, school_id, name) VALUES (12, 15, 'Sample');

创建目标表:

CREATE TABLE school(
  school_id INT PRIMARY KEY AUTO_INCREMENT,
  name VARCHAR(20)
);

INSERT INTO school (school_id, name) VALUES
  (1, 'Top'),
  (2, 'Middle'),
  (3, 'Bottom'),
  (15, 'Top');

CREATE TABLE class(
  class_id INT(11) NOT NULL,
  school_id INT(11) DEFAULT NULL,
  name VARCHAR(20) DEFAULT NULL,
  PRIMARY KEY (class_id),
  CONSTRAINT FK_class_school_school_id FOREIGN KEY (school_id)
  REFERENCES school (school_id) ON DELETE RESTRICT ON UPDATE CASCADE
)
ENGINE = INNODB;

INSERT INTO class (class_id, school_id, name) VALUES (10, 2, 'Sample2');
INSERT INTO class (class_id, school_id, name) VALUES (12, 15, 'Sample');

更新源表，增加 id 值:我们应该更新所有唯一值，在我们的例子中我们必须更新 class_id在class表和 school_id在school表。

查找最大值 class_id对于目标 class表格

SELECT MAX(class_id) + 1000 FROM class; -- This will return => 1012

增加所有 SOURCE class_id值 class_id + 1012

UPDATE class SET class_id = class_id + 1012;

查找最大值 school_id对于目标 school表格

SELECT max(school_id) + 1000 FROM school; -- This will return =>1015

增加所有 SOURCE school_id值 school_id + 1015

UPDATE school SET school_id = school_id + 1015;

就是这样。我们可以转储源表:

INSERT INTO school VALUES
  (1016, 'Middle1'),
  (1017, 'Middle2'),
  (1018, 'Middle3'),
  (1030, 'Middle');

INSERT INTO class VALUES
  (1023, 1016, 'Sample1'),
  (1024, 1030, 'Sample');

现在我们可以轻松地针对目标数据库运行此脚本。