mysql - 更新链接列时复制部分/类别

Question

我有两个部分/类别结构的链接表。

the section table structure
id sec_title
1  section 1
2  section 2

the category structure
id sectionid cat_title
1  1  cat1
2  1  cat2
3  2  cat 3
4  2  cat 4

是否有一种简单的方法可以将包含所有类别的部分复制到同一部分和类别表中？

如果我进行直接复制，问题是类别表中复制的行的“sectionid”不是新部分行的 ID。相反，它是旧行的sectionid。

我需要“sectionid”是新插入的节行的 ID。

复制版本

section table structure:

id sec_title
1  section 1
2  section 2
3  copy of section 1
4  copy of section 2

the category structure
id sectionid cat_title
1  1  cat1
2  1  cat2
3  2  cat 3
4  2  cat 4
5  3  cat1
6  3  cat2
7  4  cat 3
8  4  cat 4

Answer 1

已更新您可以像这样一次克隆多个部分

INSERT INTO section (sec_title) 
SELECT CONCAT('Copy of ', sec_title)
  FROM section
 WHERE id IN(1, 2)
 ORDER BY id;

INSERT INTO category (sectionid, cat_title)
SELECT LAST_INSERT_ID() + rnum, cat_title
  FROM 
(
  SELECT sectionid, cat_title, @n := IF(@g = sectionid, @n + 1, 0) rnum, @g := sectionid
    FROM category CROSS JOIN (SELECT @n := -1, @g := NULL) i
   WHERE sectionid IN (1, 2)
   ORDER BY sectionid, id
)q;

这里是SQLFiddle 演示

<小时/>

原始问题的答案:

Is there an easy way to copy a section with all of its categories into the same section and categories table?

假设两个表中的 id 列都是 auto_increment 列，并且您想要克隆第 2 部分，您可以这样做

INSERT INTO section (sec_title) 
SELECT CONCAT('Copy of ', sec_title)
  FROM section
 WHERE id = 2;
INSERT INTO category (sectionid, cat_title)
SELECT LAST_INSERT_ID(), cat_title
  FROM category
 WHERE sectionid = 2;

这里是SQLFiddle 演示

<小时/>

您可以将其包装在存储过程中

DELIMITER $$
CREATE PROCEDURE clone_section(IN _sectionid INT)
BEGIN
  INSERT INTO section (sec_title) 
  SELECT CONCAT('Copy of ', sec_title)
    FROM section
   WHERE id = _sectionid;
  INSERT INTO category (sectionid, cat_title)
  SELECT LAST_INSERT_ID(), cat_title
    FROM category
   WHERE sectionid = _sectionid;
END$$
DELIMITER ;

然后使用它

CALL clone_section(2);

这里是SQLFiddle 演示