mysql - 从两个表插入一个表的 SQL 语句(使用另一个没有连接条件的 id)

Question

我有两个表:

CREATE TABLE `data_items` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `data` longtext NOT NULL,
  `created` datetime NOT NULL,
  `updated` datetime NOT NULL,
  `data_item_type` varchar(255) NOT NULL,
  `data_source_id` int(11) NOT NULL,
  PRIMARY KEY (`id`),
  KEY `IDX_AFA125D21A935C57` (`data_source_id`),
  CONSTRAINT `FK_AFA125D21A935C57` FOREIGN KEY (`data_source_id`) REFERENCES `data_sources` (`id`)
) ENGINE=InnoDB AUTO_INCREMENT=7192 DEFAULT CHARSET=utf8;

和

CREATE TABLE `map_locations` (
  `id` int(11) NOT NULL,
  `latitude` decimal(9,6) DEFAULT NULL,
  `longitude` decimal(9,6) DEFAULT NULL,
  `name` varchar(255) DEFAULT NULL,
  `address` varchar(255) DEFAULT NULL,
  `status` varchar(255) NOT NULL,
  `message_codes` longtext NOT NULL,
  PRIMARY KEY (`id`),
  CONSTRAINT `FK_B28E0DE7BF396750` FOREIGN KEY (`id`) REFERENCES `data_items` (`id`) ON DELETE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8;

我正在尝试完成一个两步过程。第一个查询工作正常:

INSERT INTO data_items (`data`, created, updated, data_item_type, data_source_id) SELECT data_items.data, now(), now(), data_items.data_item_type, 2
    FROM data_items
    WHERE data_items.data_source_id = 1

--> 1和2是示例值

现在，我想从“data_items”(刚刚创建的 auto-incs)中获取每个 ID，并在“map_locations”表中插入每个新的(对应的行)时使用这些 ID。

所以，

data_items
id | data_source_id
1 | 1
2 | 1
3 | 2
4 | 2

map_locations
id | content
1 | aaa
2 | bbb
.... 
3 | aaa
4 | bbb

我需要复制行 (1, 2)，使用 data_items (3,4) 中的 ID 并插入这些行。

希望这是有道理的..它相当困惑。TIA。

Answer 1

1. 您需要新创建的 data_items引用他们原来的同行；最简单的方法是修改架构，使表包含一个自引用外键:

   ALTER TABLE data_items
     ADD COLUMN underlying_id INT(11) NULL,
     ADD FOREIGN KEY (underlying_id) REFERENCES data_items (id)
   

2. 插入data_items然后变成:

   INSERT INTO data_items
     (underlying_id, data, created, updated, data_item_type, data_source_id)
   SELECT id, data, NOW(), NOW(), data_item_type, 2
   FROM   data_items
   WHERE  data_source_id = 1
   

3. 现在可以执行自连接来获取新创建的 id小号:

   INSERT INTO map_locations
     (id, content)
   SELECT new.id, map_locations.content
   FROM   map_locations
     JOIN data_items old USING (id)
     JOIN data_items new ON new.underlying_id = old.id
   WHERE  old.data_source_id = 1
   

   确保使用(默认)REPEATABLE READ 在与之前插入相同的事务中执行此查询事务隔离级别，以确保对 data_items 的任何并发更改表不会导致不一致的状态。

4. 同样，在同一个事务中，清除 underlying_id引用:

   UPDATE data_items SET underlying_id = NULL