深入理解MySQL中间表

发布时间：2023-05-23

一、什么是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语句进行示例说明。中间表是一个非常灵活、方便、高效的操作，对于建立复杂查询或数据报表、进行数据计算等需求是非常合适的。

推荐文章