在 MySQL 中，如何复制表中的现有记录和依赖于父表 ID 的支持表? [英] In MySQL, how would I duplicate an existing record in a table and a supporting table that relies on the parent table ID?
问题描述
假设我有一个 core 表和 2 个支持表，我们称之为 activity 和 steps.
Let's say I have a table of core and 2 supporting tables we'll call activity and steps.
核心可能包含以下内容:
coreID(int) | coreLabel(varchar)
--------------------------------
1 | Wash Car
12 | Wash Dog
15 | Vacuum the carpet
活动可能包含以下内容:
aID(int auto) | coreID(int fk) | menuID(int fk)
-----------------------------------------------
1 | 1 | 268
2 | 1 | 269
3 | 12 | 269
4 | 12 | 239
5 | 12 | 230
6 | 15 | 237
7 | 15 | 269
8 | 15 | 244
9 | 15 | 242
步骤可能包含以下内容:
stepID(int auto) | coreID(int fk) | aID(int fk)
-----------------------------------------------
1 | 1 | 1
2 | 1 | 2
3 | 12 | 5
4 | 12 | 6
5 | 12 | 9
6 | 15 | 10
7 | 15 | 11
8 | 15 | 26
该网站将显示核心项目.用户应该能够点击一个按钮来复制一个项目(例如重复洗狗，coreID=2)，这样 Core 现在就会有值
The website will show the core items. The user should be able to click a button to duplicate an item (e.g. duplicate wash dog, coreID=2) such that Core would now have the values
coreID(int) | coreLabel(varchar)
--------------------------------
1 | Wash Car
12 | Wash Dog
15 | Vacuum the carpet
16 | Wash Dog (copy)
...Activity 中的数据应该如下所示:
...and the data in Activity should look like this:
aID(int auto) | coreID(int fk) | menuID(int fk)
-----------------------------------------------
1 | 1 | 268
2 | 1 | 269
3 | 12 | 269
4 | 12 | 239
5 | 12 | 230
6 | 15 | 237
7 | 15 | 269
8 | 15 | 244
9 | 15 | 242
10 | 16 | 268
11 | 16 | 269
...然后步骤应该如下所示:
stepID(int auto) | coreID(int fk) | aID(int fk)
-----------------------------------------------
1 | 1 | 1
2 | 1 | 2
3 | 12 | 5
4 | 12 | 6
5 | 12 | 9
6 | 15 | 10
7 | 15 | 11
8 | 15 | 26
9 | 16 | 10
10 | 16 | 11
复制核心和活动中的记录很简单，我在那里没有问题.我不知道该怎么做是复制步骤中的支持记录，因为当记录插入活动时，aID 是插入步骤所必需的，这特别是我的困境...... MySQL 可以使用触发器为我处理这个问题，例如我在此处找到的:https://dba.stackexchange.com/questions/37470/cascading-inserts-in-mysql 如果是这样，如何我会传入非默认值吗?
Duplicating the records in Core and Activity is straight forward, I have no issues there. What I don't know how to do is duplicate the supporting records in Steps because when a record is inserted into Activity, the inserted value for aID is required for the insertion into Steps and that specifically is where my dilemma is... Can MySQL handle this for me with a trigger such as what I found here: https://dba.stackexchange.com/questions/37470/cascading-inserts-in-mysql and if so, how would I pass in non default values?
更新桌子上的触发器不起作用.我需要能够将数据添加到 Activity 表，而不需要 db 在 step 表中添加一行.通常我认为该解决方案会奏效，但在这种情况下不行.
Update
A trigger on the table isn't going to work. I need to be able to add data to the Activity table without the db also adding a row into the step table. Normally I think that solution would have worked but not under this scenario.
更新 #2我创建了一个数据库小提琴.这是从我的真实源数据派生的样本数据组成的，但已经过简化，因此希望样本数据足够.
Update #2 I created a database fiddle. This is made up sample data derived from my true source data but has been simplified so hopefully the sample data is sufficient.
CREATE TABLE `core` (
`coreID` INT(11) NOT NULL AUTO_INCREMENT,
`coreLabel` VARCHAR(150) NOT NULL DEFAULT 'Untitled',
PRIMARY KEY (`coreID`)
)
COLLATE='utf8_general_ci'
ENGINE=InnoDB
AUTO_INCREMENT=1
;
CREATE TABLE `activity` (
`aID` INT(11) NOT NULL AUTO_INCREMENT,
`coreID` INT(11) NULL DEFAULT NULL,
`menuID` INT(11) NULL DEFAULT NULL,
PRIMARY KEY (`aID`)
)
COLLATE='utf8_general_ci'
ENGINE=InnoDB
AUTO_INCREMENT=1
;
CREATE TABLE `steps` (
`stepID` INT(11) NOT NULL AUTO_INCREMENT,
`coreID` INT(11) NULL DEFAULT NULL,
`aID` INT(11) NOT NULL,
PRIMARY KEY (`stepID`)
)
COLLATE='utf8_general_ci'
ENGINE=InnoDB
AUTO_INCREMENT=1
;
insert into core (corelabel) values ('Wash Car');
insert into core (corelabel) values ('Wash Dog');
insert into core (corelabel) values ('Vacuum Rug');
insert into activity (coreID, menuID) values (1,268);
insert into activity (coreID, menuID) values (1,269);
insert into activity (coreID, menuID) values (12,268);
insert into activity (coreID, menuID) values (12,239);
insert into activity (coreID, menuID) values (12,230);
insert into activity (coreID, menuID) values (15,237);
insert into activity (coreID, menuID) values (15,269);
insert into activity (coreID, menuID) values (15,244);
insert into activity (coreID, menuID) values (15,242);
insert into activity (coreID, menuID) values (4,268);
insert into steps (coreID, aID) values (1,1);
insert into steps (coreID, aID) values (1,2);
insert into steps (coreID, aID) values (12,5);
insert into steps (coreID, aID) values (12,6);
insert into steps (coreID, aID) values (12,9);
insert into steps (coreID, aID) values (15,10);
insert into steps (coreID, aID) values (15,11);
insert into steps (coreID, aID) values (15,26);
select * from core;
select * from activity;
select * from steps;
Select
c.coreID,
c.coreLabel,
a.aID,
a.menuID,
s.stepID
From
core c Left Join
activity a On a.coreID = c.coreID Left Join
steps s On s.aID = a.aID;
-- 如果我们假装点击了一个按钮来复制Wash Car"，我们预计会发生以下插入；
-- 问题实际上是以编程方式获取这些值；
-- If we pretend a button was clicked to duplicate "Wash Car", we'd expect the following inserts to occur;
-- the issue is actually getting these values programmatically;
insert into core (corelabel) values ('Wash Car (copy)');
insert into activity (coreID, menuID) values (4,269);
insert into steps (coreID, aID) values (4,10);
insert into steps (coreID, aID) values (4,11);
Select
c.coreID,
c.coreLabel,
a.aID,
a.menuID,
s.stepID
From
core c Left Join
activity a On a.coreID = c.coreID Left Join
steps s On s.aID = a.aID;
最后一个选择查询(见上文)返回 4 条记录.这应该是正确的，因为洗车"有两个步骤.该场景是用户想要使用洗车"作为另一个程序的模板.单击网站上Wash Car"旁边的复制按钮后，我们应该会看到返回了 6 条记录，并且应该会看到Wash Car (copy)"条目
The last select query (see above) returns 4 records. This should be correct since "Wash Car" has two steps. The scenario is that the user wants to use the "Wash Car" as a template for another procedure. After clicking the duplicate button next to "Wash Car" on the web site, we should see 6 records returned and we should see an entry for "Wash Car (copy)"
推荐答案
在 Core 和 Activity 中复制记录很简单
Duplicating the records in Core and Activity is straight forward
而且可能看起来像这样:
And would probably look something like this:
set @sourceCoreID = 12;
insert into core (coreLabel)
select coreLabel
from core
where coreID = @sourceCoreID;
set @newCoreID = last_insert_id();
insert into activity (coreID, menuID)
select @newCoreID, menuID
from activity
where coreID = @sourceCoreID;
您现在需要做的就是将一些数据从新的 activity 行复制到 steps(至少在您的示例结果中看起来是这样).而且不需要太多魔法:
All you need now, is to copy some data from the new activity rows to steps (At least it looks like that in your sample result). And there is not much magic needed:
insert into steps(coreID, aID)
select coreID, aID
from activity
where coreID = @newCoreID;
这篇关于在 MySQL 中，如何复制表中的现有记录和依赖于父表 ID 的支持表?的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！
