mysql - 我有主从表，我想复制它们

Question

CREATE TABLE IF NOT EXISTS `master` (
  `id` int(10) unsigned NOT NULL AUTO_INCREMENT,
  `comments` varchar(50) DEFAULT NULL,
  PRIMARY KEY (`id`)
) AUTO_INCREMENT=1 ;

CREATE TABLE IF NOT EXISTS `detail` (
  `id` int(10) unsigned NOT NULL AUTO_INCREMENT,
  `master_id` int(10) unsigned NOT NULL,
  `value` int(10) DEFAULT 0,
  PRIMARY KEY (`id`)
) AUTO_INCREMENT=1 ;

大师

id, comments
 1, 'text 1' 

细节

id, master_id, value
 1, 1,          50 
 2, 1,          53

我想通过一两个查询复制它们，如下所示:

id, comments 
 1, 'text 1' 
 2, 'text 1' *

id, master_id, value
 1, 1,          50 
 2, 1,          53 
 3, 2,          50 *
 4, 2,          53 *

我已经设法仅使用临时表复制主数据，但问题出在细节上。它是用 master_id 1 而不是 2 复制的

id, comments 
 1, 'text 1' 
 2, 'text 2'
 3, 'text 1' *

id, master_id, value
 1, 1,          50 
 2, 1,          53 
 3, 2,          51 
 4, 2,          66 
 5, 2,          44 
 6, 3,          50 *
 7, 3,          53 *

当我们已经插入第 2 行时，复制 master.id = 1 的行

第二个查询修复如下:

INSERT INTO detail ( master_id, value )
SELECT m2.id, value
FROM detail AS d
JOIN master AS m1 ON m1.id = d.master_id AND m1.id = @DUPLICATETHISID
JOIN master AS m2 ON m2.comments = m1.comments AND m2.id != m1.id 
AND m2.id not IN ( SELECT master_id FROM detail JOIN master as m ON master_id = m.id )

Answer 1

你不需要临时表来复制主表，你可以这样做:

INSERT INTO master (comments)
SELECT comments FROM master;

要复制详细信息，我认为应该这样做:

INSERT INTO detail (master_id, value)
SELECT m2.id, value
FROM detail AS d
JOIN master AS m1 ON m1.id = d.master_id
JOIN master AS m2 ON m2.comments = m1.comments AND m2.id != m1.id

DEMO