Mysql
創建一個跨越 3 個表的 VIEW
抱歉，如果標題不清楚 - 請隨時改進。
這裡有 3 個表格（並非所有數據都與問題相關）：
CREATE TABLE `content` ( `id` int(10) unsigned NOT NULL AUTO_INCREMENT, `community_id` int(10) unsigned NOT NULL COMMENT 'The community for whom this URL has special rules', `url_id` int(10) unsigned DEFAULT NULL COMMENT 'The URL that has special rules', `question_id` int(10) unsigned DEFAULT NULL, `banned` tinyint(1) unsigned NOT NULL DEFAULT '0' COMMENT 'Flags if a URL is banned from the community', `featured` tinyint(3) unsigned NOT NULL DEFAULT '0' COMMENT 'If this article is to be featured on this site', `slider_image` tinytext, `weight` int(11) DEFAULT '0', `create_time` timestamp NULL DEFAULT CURRENT_TIMESTAMP, PRIMARY KEY (`id`), UNIQUE KEY `content_url_unique` (`community_id`,`url_id`), UNIQUE KEY `content_question_unique` (`community_id`,`question_id`), KEY `community_index` (`community_id`), KEY `content_url_idx` (`url_id`), KEY `content_community_idx` (`community_id`), KEY `content_question_idx` (`question_id`), CONSTRAINT `content_community` FOREIGN KEY (`community_id`) REFERENCES `community` (`id`) ON DELETE NO ACTION ON UPDATE NO ACTION, CONSTRAINT `content_question` FOREIGN KEY (`question_id`) REFERENCES `question` (`id`) ON DELETE CASCADE ON UPDATE NO ACTION, CONSTRAINT `content_url` FOREIGN KEY (`url_id`) REFERENCES `url` (`id`) ON DELETE CASCADE ON UPDATE NO ACTION ) ENGINE=InnoDB AUTO_INCREMENT=186 DEFAULT CHARSET=utf8; CREATE TABLE `url_tag` ( `url_id` int(10) unsigned NOT NULL COMMENT 'The URL to be tagged', `tag_id` int(10) unsigned NOT NULL COMMENT 'The tag the url refers to', PRIMARY KEY (`url_id`,`tag_id`), KEY `FK_urltag_url_idx` (`url_id`), KEY `FK_urltag_tag_idx` (`tag_id`), CONSTRAINT `FK_urltag_tag` FOREIGN KEY (`tag_id`) REFERENCES `tag` (`id`) ON DELETE CASCADE ON UPDATE NO ACTION, CONSTRAINT `FK_urltag_url` FOREIGN KEY (`url_id`) REFERENCES `url` (`id`) ON DELETE CASCADE ON UPDATE NO ACTION ) ENGINE=InnoDB DEFAULT CHARSET=utf8 COMMENT='Allows to tag URLs'; CREATE TABLE `question_tag` ( `question_id` int(10) unsigned NOT NULL, `tag_id` int(10) unsigned NOT NULL, PRIMARY KEY (`question_id`,`tag_id`), UNIQUE KEY `question_tag` (`question_id`,`tag_id`), KEY `fk_questiontag_question_idx` (`question_id`), KEY `fk_questiontag_tag_idx` (`tag_id`), CONSTRAINT `fk_questiontag_question` FOREIGN KEY (`question_id`) REFERENCES `question` (`id`) ON DELETE CASCADE ON UPDATE NO ACTION, CONSTRAINT `fk_questiontag_tag` FOREIGN KEY (`tag_id`) REFERENCES `tag` (`id`) ON DELETE CASCADE ON UPDATE NO ACTION ) ENGINE=InnoDB DEFAULT CHARSET=utf8 COMMENT='The tags associated with a given question';請注意，內容意味著具有 a
url_id或 aquestion_id- 而不是兩者。我需要一種將內容 ID 連接到標籤 ID 的方法。我正在考慮創建一個 VIEW，到目前為止，我想出了 2 種可能性。選項1：
(SELECT content.id AS content_id, url_tag.tag_id AS tag_id FROM url_tag, content WHERE url_tag.url_id = content.url_id) UNION (SELECT content.id AS content_id, question_tag.tag_id AS tag_id FROM question_tag, content WHERE question_tag.question_id = content.question_id)選項 2：
SELECT content.id AS content_id, COALESCE(url_tag.tag_id, question_tag.tag_id) AS tag_id FROM `content` LEFT JOIN url_tag ON content.url_id = url_tag.url_id LEFT JOIN question_tag ON content.question_id = question_tag.question_id WHERE COALESCE(url_tag.tag_id, question_tag.tag_id) IS NOT NULL我的問題是哪一種是更好的方法（或者是否有更好的方法）？
另請注意，目前某些內容根本沒有標記。
額外挑戰：將 content_id 直接連接到 category_id 的 VIEW
CREATE TABLE `category_tag` ( `category_id` int(10) unsigned NOT NULL, `tag_id` int(10) unsigned NOT NULL, PRIMARY KEY (`category_id`,`tag_id`), KEY `FK_categorytag_category_idx` (`category_id`), KEY `FK_categorytag_tag_idx` (`tag_id`), CONSTRAINT `FK_categorytag_category` FOREIGN KEY (`category_id`) REFERENCES `category` (`id`) ON DELETE CASCADE ON UPDATE NO ACTION, CONSTRAINT `FK_categorytag_tag` FOREIGN KEY (`tag_id`) REFERENCES `tag` (`id`) ON DELETE CASCADE ON UPDATE NO ACTION ) ENGINE=InnoDB DEFAULT CHARSET=utf8;
這裡沒有一個強有力的案例來用視圖來做這件事。
在選項 1 和 2 中，選項 2 是唯一可能可用於任何大小的數據的選項，因為MySQL 用於處理視圖的算法不同，以及每種算法的優勢與局限性。
除非另有明確聲明，否則 MySQL 會
MERGE盡可能使用該算法，這實際上使視圖成為更複雜查詢的別名。WHERE這提供了一些優化，尤其是在解釋外部查詢中 的子句時使用基礎表中的索引。SELECT * FROM view_option_2 WHERE content_id = 500;我看不出為什麼
MERGE不能與這個視圖定義一起使用，所以伺服器應該處理上面的查詢，就好像你已經發送了這個：SELECT content.id AS content_id, COALESCE(url_tag.tag_id, question_tag.tag_id) AS tag_id FROM `content` LEFT JOIN url_tag ON content.url_id = url_tag.url_id LEFT JOIN question_tag ON content.question_id = question_tag.question_id WHERE content.id = 500 AND COALESCE(url_tag.tag_id, question_tag.tag_id) IS NOT NULL這可以合理地預期使用主鍵和主鍵
content的最左列。這似乎是一個不錯的、整潔的、索引良好的查詢。utl_tag``question_tag另一方面，如果選項 1 用於視圖定義，伺服器將無法使用該
MERGE算法，因為UNION… 所以伺服器將使用另一個算法TEMPTABLE，並構造一個臨時表並填充它使用兩個查詢的聯合，然後通過從臨時表中消除不匹配的行，只返回由 where 子句指定的行。但是您不想在這裡忽略的一點是，臨時表可以包含所有可能的行，而不考慮WHERE外部查詢中的。定義視圖時不必指定
ALGORITHM，如果不指定，則預設為UNDEFINED允許伺服器選擇的。指定算法的一個優點是您始終知道將使用哪個算法。額外獎勵，如果您要求MERGE並且無法完成，您會收到警告。如果視圖中沒有表，合併也不起作用，所以這裡有一個簡單的例子：mysql> CREATE ALGORITHM=MERGE VIEW test_view AS SELECT UUID_SHORT() AS a_big_number; Query OK, 0 rows affected, 1 warning (0.05 sec)我們沒有收到錯誤，但確實收到了警告。
mysql> show warnings; +---------+------+-------------------------------------------------------------------------------+ | Level | Code | Message | +---------+------+-------------------------------------------------------------------------------+ | Warning | 1354 | View merge algorithm can't be used here for now (assumed undefined algorithm) | +---------+------+-------------------------------------------------------------------------------+ 1 row in set (0.00 sec)然而，這是一個完全合法且有效的視圖，每次返回不同的數字：
mysql> select * from test_view; +---------------------+ | a_big_number | +---------------------+ | 2184556849028661248 | +---------------------+ 1 row in set (0.00 sec)定義您的視圖，
ALGORITHM=MERGE如果伺服器不喜歡它，那麼這不是此應用程序中視圖的理想候選者。但是請注意，這並不意味著該
TEMPTABLE算法也不是沒有好處……但是如果您SELECT要從您的角度來看，WHERE某些條件適用於基礎表中的欄位，TEMPTABLE可以防止這些條件“通過”視圖定義。相反，它們會在派生臨時表後應用於結果，當然該臨時表沒有索引。你的“獎金”似乎也不是特別複雜。
採用選項 2 查詢並修改它：
SELECT content.id AS content_id, COALESCE(url_tag.tag_id, question_tag.tag_id) AS tag_id, category_tag.category_id FROM `content` LEFT JOIN url_tag ON content.url_id = url_tag.url_id LEFT JOIN question_tag ON content.question_id = question_tag.question_id LEFT JOIN category_tag on category_tag.tag_id = COALESCE(url_tag.tag_id, question_tag.tag_id) WHERE COALESCE(url_tag.tag_id, question_tag.tag_id) IS NOT NULL如果您不想刪除未標記的內容，請刪除該
WHERE子句。要選擇特定的 content.id，請將其放在
WHERE子句中。如果您因為類別和標籤之間的關係而獲得重複的類別，那麼您將需要
SELECT DISTINCT但不要將其放在視圖中，因為這MERGE兩者都不兼容。