mysql - 通过匹配链接表中的值来填充联结表

Question

我有一个数据库，其中包含多篇文章和相应的图像(位于不同的文件夹中)

有 3 个包含产品图片的文件夹

In one folder only technical images, in the other marketing images, in the third are high quality images for printing purposes

为了更快地检索产品信息或图像，我们使用“slugs”，将商品编号从除数字之外的任何字符中删除

我在图像表上做了同样的事情，但因此有很多图像链接到一个产品(有时一个图像链接到不同的产品)我想将它们链接到一个连接表中

是否可以通过比较 tblArticles.articleSlug 和 tblArticlepictures. 中的值来填充连接表中的 ID文章图片Slug

我找到了这个资源，但它是基于一对多的关系

How do I insert into a table from another table by matching on values?

<小时/>

CREATE TABLE `tblArticles` (
  `articleID` int(11) NOT NULL AUTO_INCREMENT,
  `articleSlug` varchar(16) DEFAULT NULL,
  `articleComment` varchar(1500) DEFAULT '',
  PRIMARY KEY (`articleID`),
 ) ENGINE=InnoDB DEFAULT CHARSET=utf8;

CREATE TABLE `tblArticlepictures` (
  `articlepictureID` int(11) NOT NULL AUTO_INCREMENT,
  `articlepictureYear` int(4) DEFAULT NULL,
  `articlepicturePath` varchar(255) NOT NULL,
  `articlepictureSlug` varchar(45) DEFAULT NULL,
  PRIMARY KEY (`articlepictureID`),
  UNIQUE KEY `articlepicturePath_UNIQUE` (`articlepicturePath`),
  ) ENGINE=InnoDB DEFAULT CHARSET=utf8;

CREATE TABLE `tblRefArticleArticlepicture` (
  `refArticleArticlepictureID` int(11) NOT NULL AUTO_INCREMENT,
  `articleIDRef` int(11) NOT NULL,
  `articlepictureIDRef` int(11) NOT NULL,
  PRIMARY KEY (`refArticleArticlepictureID`),
  KEY `fk_tblRefArticleArticlepicture_tblArticles1_idx` (`articleIDRef`),
  KEY `fk_tblRefArticleArticlepicture_tblArticlepictures1_idx` (`articlepictureIDRef`),
  CONSTRAINT `fk_tblRefArticleArticlepicture_tblArticlepictures1` 
  FOREIGN KEY (`articlepictureIDRef`) REFERENCES `tblArticlepictures` (`articlepictureID`) 
  ON DELETE NO ACTION ON UPDATE NO ACTION,
  CONSTRAINT `fk_tblRefArticleArticlepicture_tblArticles1` 
  FOREIGN KEY (`articleIDRef`) REFERENCES `tblArticles` (`articleID`) 
  ON DELETE NO ACTION ON UPDATE NO ACTION
) ENGINE=InnoDB DEFAULT CHARSET=utf8;

<小时/>

+-------------------+-------------------+-------------------+-----------------------+
| Picture           | Folder            | Article           | Slug (in both tables) |
+-------------------+-------------------+-------------------+-----------------------+
| APS-4216_wr.jpg   | W                 | APS-4216T         | 4216                  |
+-------------------+-------------------+-------------------+-----------------------+
| APS-9669 .jpg     | W                 | APS-9669          | 9669                  |
+-------------------+-------------------+-------------------+-----------------------+
| APS-2719NC.jpg    | W                 | APS-2719          | 2719                  |
+-------------------+-------------------+-------------------+-----------------------+
| EAP-3277T.jpg     | W                 | EAP-3277          | 3277                  |
+-------------------+-------------------+-------------------+-----------------------+
| EAP-8717T_1.jpg   | W                 | EAP-8717Z         | 8717                  |
+-------------------+-------------------+-------------------+-----------------------+
| EAP-4530T.jpg     | W                 | EAP-4530A         | 4530                  |
+-------------------+-------------------+-------------------+-----------------------+
| ----------------- | ----------------- | ----------------- | -----------------     |
+-------------------+-------------------+-------------------+-----------------------+
| APS-4216 t.jpg    | P                 | APS-4216T         | 4216                  |
+-------------------+-------------------+-------------------+-----------------------+
| APS-9669_wr.jpg   | P                 | APS-9669          | 9669                  |
+-------------------+-------------------+-------------------+-----------------------+
| APS-2719 .jpg     | P                 | APS-2719          | 2719                  |
+-------------------+-------------------+-------------------+-----------------------+
| EAP-3277NC.jpg    | P                 | EAP-3277          | 3277                  |
+-------------------+-------------------+-------------------+-----------------------+
| EAP-8717T.jpg     | P                 | EAP-8717Z         | 8717                  |
+-------------------+-------------------+-------------------+-----------------------+
| EAP-4530T_1.jpg   | P                 | EAP-4530A         | 4530                  |
+-------------------+-------------------+-------------------+-----------------------+

Answer 1

insert into tblRefArticleArticlepicture (articleIDRef, articlepictureIDRef)
select articleID as articleIDRef, articlepictureID as articlepictureIDRef
        from tblArticles a
        join tblArticlepictures p
        ON a.articleSlug = p.articlepictureSlug
        where a.articleSlug =p.articlepictureSlug