MYSQL将标签表合并为一个

Question

我有表格来组织标签..

3篇文章和文章标签的表格：

article_tags
---
tag_id *
tag_name

articles
---
article_id *
article_name 

individual_article_tags
----
article_id *
tag_id *

和3个单独的表，用于图像和图像标签

image_tags
---
tag_id *
tag_name

images
---
image_id *
image_name 

individual_image_tags
----
image_id *
tag_id *

我希望文章和图像表只引用1个标签表，而不是2个标签表，如下所示：

tags
---
tag_id *
tag_name

articles
---
article_id *
article_name 

individual_article_tags
----
article_id *
tag_id *

images
---
image_id *
image_name 

individual_image_tags
----
image_id *
tag_id *

问题是标签有不同的is，我不知道如何合并它们。

Answer 1

首先，通过对tags_unified执行INSERT INTO ...SELECT，创建一个具有自己的auto_increment id (与其他现有表不同)的新UNION表。结果将是两个表中所有标签的不同集合。

CREATE TABLE tags_unified (
  tags_id INT PRIMARY KEY AUTO_INCREMENT,
  tag_name VARCHAR(64)
);

/* Load up all the tags with new ids */
INSERT INTO tags_unified (tag_name)
  SELECT tag_name FROM article_tags
  UNION
  SELECT tag_name FROM image_tags

然后使用JOIN查询UPDATE所有的individual_image_tags和inidividual_article_tags，以获得新的ids。

UPDATE
  individual_image_tags
  JOIN image_tags ON individual_image_tags.tag_id = image_tags.tag_id
  /* LEFT JOIN the old tags to the new tags by tag_name */
  LEFT JOIN tags_unified ON image_tags.tag_name = tags_unified.tag_name
SET individual_image_tags.tag_id = tags_unified.tag_id
/* And update those which have a match in the new tags table */
WHERE tags_unified.tag_id IS NOT NULL

UPDATE
  individual_article_tags
  JOIN article_tags ON individual_article_tags.tag_id = article_tags.tag_id
  LEFT JOIN tags_unified ON article_tags.tag_name = tags_unified.tag_name
SET individual_article_tags.tag_id = tags_unified.tag_id
WHERE tags_unified.tag_id IS NOT NULL

在运行UPDATE语句之前，请将其重新格式化为SELECT语句以验证结果。

SELECT
  individual_image_tags.*, 
  tags_unified.*
FROM
  individual_image_tags
  JOIN image_tags ON individual_image_tags.tag_id = image_tags.tag_id
  LEFT JOIN tags_unified ON image_tags.tag_name = tags_unified.tag_name
WHERE tags_unified.tag_id IS NOT NULL

SELECT
 individual_article_tags.*, 
 tags_unified.*
FROM
  individual_article_tags
  JOIN article_tags ON individual_article_tags.tag_id = article_tags.tag_id
  LEFT JOIN tags_unified ON article_tags.tag_name = tags_unified.tag_name
WHERE tags_unified.tag_id IS NOT NULL

编辑：

LEFT JOIN和WHERE子句实际上不应该是必需的，因为对于tags_unified表中的每个现有标记，都应该与之匹配。

评论后更新：

创建临时表并根据这些表重新填充原始链接表可能比删除并重新添加唯一约束或组合键更容易。使用CREATE TEMPORARY TABLE AS SELECT...。然后删除原始表中的所有行，并使用INSERT INTO ... SELECT从临时表中重新填充它。

CREATE TEMPORARY TABLE temp_individual_article_tags AS
    SELECT
     individual_article_tags.article_id 
     tags_unified.tag_id
    FROM
      individual_article_tags
      JOIN article_tags ON individual_article_tags.tag_id = article_tags.tag_id
      LEFT JOIN tags_unified ON article_tags.tag_name = tags_unified.tag_name
    WHERE tags_unified.tag_id IS NOT NULL