深入理解MySQL中間表

一、什麼是MySQL中間表

MySQL中間表是指在兩個或多個表之間建立的一個臨時表，用於協調多張表之間的交互。中間表不存儲數據，僅用於連接其他表以進行查詢、篩選、排序、聚合等操作。

中間表的創建可以使用CREATE TABLE AS SELECT語句或INSERT INTO SELECT語句，當且僅當SELECT查詢語句與INSERT INTO語句或CREATE TABLE語句中出現相同數量的列時，這種做法才適用。

CREATE TABLE `order_detail` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `user_id` int(11) DEFAULT NULL,
  `product_id` int(11) DEFAULT NULL,
  `quantity` int(11) DEFAULT NULL,
  PRIMARY KEY (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

CREATE TABLE `product` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `name` varchar(255) DEFAULT NULL,
  `price` decimal(10,2) DEFAULT NULL,
  PRIMARY KEY (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

INSERT INTO `order_detail` (`user_id`, `product_id`, `quantity`) VALUES
(1, 2, 4),
(1, 1, 3),
(2, 3, 5),
(3, 1, 1),
(3, 4, 2);

CREATE TABLE `order_detail_aggre` (
  `user_id` int(11) DEFAULT NULL,
  `total_quantity` int(11) DEFAULT NULL,
  `total_price` decimal(10,2) DEFAULT NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

INSERT INTO `order_detail_aggre` (`user_id`, `total_quantity`, `total_price`) 
SELECT `user_id`, SUM(`quantity`), SUM(`quantity` * `price`) 
FROM `order_detail` 
JOIN `product` ON `order_detail`.`product_id` = `product`.`id`
GROUP BY `user_id`;

二、MySQL中間表的優勢

使用中間表有以下優勢：

1. 提升查詢效率

通過中間表的建立，可以將一些複雜的跨表查詢變成針對中間表的單表查詢，提升查詢效率。

2. 統計聚合數據

通過對多張表的關聯查詢，可以將多條數據匯聚到一個結果集中，用於實現複雜統計的功能。

3. 管理維護方便

中間表是臨時性的，使用完畢可以隨時刪除，對系統的管理和維護都非常方便。

三、使用MySQL中間表的場景

下面列舉幾個適合使用MySQL中間表的場景：

1. 複雜統計查詢

在多張關聯表中進行統計運算，獲取計算結果並輸出。

SELECT `user_id`, SUM(`quantity`) AS `total_quantity`, SUM(`quantity` * `price`) AS `total_price`
FROM `order_detail` 
JOIN `product` ON `order_detail`.`product_id` = `product`.`id`
GROUP BY `user_id`;

2. 數據報表生成

將多個表的數據聚合後，生成需要的數據報表，供後續使用。

CREATE TABLE `report` (
  `date` date DEFAULT NULL,
  `category` varchar(255) DEFAULT NULL,
  `total_quantity` int(11) DEFAULT NULL,
  `total_price` decimal(10,2) DEFAULT NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

INSERT INTO `report` (`date`, `category`, `total_quantity`, `total_price`)
SELECT DATE(`order_date`), `category`, SUM(`quantity`), SUM(`quantity` * `price`)
FROM `order_detail`
JOIN `product` ON `order_detail`.`product_id` = `product`.`id`
JOIN `product_category` ON `product`.`category_id` = `product_category`.`id`
GROUP BY DATE(`order_date`), `category`
ORDER BY DATE(`order_date`), `category`;

3. 多表間數據交互

使用中間表完成不同表之間的數據交互，完成一些特定的需求。

CREATE TABLE `promo` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `product_id` int(11) DEFAULT NULL,
  `promo_price` decimal(10,2) DEFAULT NULL,
  PRIMARY KEY (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

INSERT INTO `promo` (`product_id`, `promo_price`) VALUES (1, 88.88);

UPDATE `product`
JOIN `promo` ON `product`.`id` = `promo`.`product_id`
SET `product`.`price` = `promo`.`promo_price`;

總結

本文介紹了MySQL中間表的概念、優勢和使用場景，並且給出了對應的SQL語句進行示例說明。中間表是一個非常靈活、方便、高效的操作，對於建立複雜查詢或數據報表、進行數據計算等需求是非常合適的。