MySQL如何生成房间与所有设施的全量关联结果（含未关联记录）

现有MySQL中的room（房间）和amenity（设施）两张表，一个房间可对应多个设施，但amenity表未做规范化设计——相同设施名称会按关联的room_id重复存储。需要实现：生成每个房间与amenity表中所有唯一设施的对应记录，即使两者原本无关联（比如2个房间+4个唯一设施，需生成8条记录）。由于不能修改数据库结构（避免影响现有运行的多服务），常规左外连接无法得到预期结果，以下是详细表结构、测试数据及解决方案。

表结构与测试数据 #

-- 房间表定义
CREATE TABLE `room` (
  `id` int NOT NULL AUTO_INCREMENT,
  `name` varchar(255) CHARACTER SET utf8mb3 COLLATE utf8mb3_unicode_ci DEFAULT NULL,
  PRIMARY KEY (`id`)
) ENGINE=InnoDB AUTO_INCREMENT=1 DEFAULT CHARSET=utf8mb3 COLLATE=utf8mb3_unicode_ci;

-- 设施表定义
CREATE TABLE `amenity` (
  `id` int NOT NULL AUTO_INCREMENT,
  `room_id` int NOT NULL,
  `amenity` varchar(255) CHARACTER SET utf8mb3 COLLATE utf8mb3_unicode_ci DEFAULT NULL,
  `checked` tinyint(1) DEFAULT '0',
  PRIMARY KEY (`id`),
  KEY `room_key_idx` (`room_id`),
  CONSTRAINT `room_key` FOREIGN KEY (`room_id`) REFERENCES `room` (`id`) ON DELETE CASCADE
) ENGINE=InnoDB AUTO_INCREMENT=1 DEFAULT CHARSET=utf8mb3 COLLATE=utf8mb3_unicode_ci;

-- 插入房间测试数据
INSERT INTO room (name) VALUES('room1');
INSERT INTO room (name) VALUES('room2');

-- 插入设施测试数据
INSERT INTO amenity (room_id, amenity, checked) VALUES(1, 'Amenity1', 0);
INSERT INTO amenity (room_id, amenity, checked) VALUES(2, 'Amenity1', 0);

INSERT INTO amenity (room_id, amenity, checked) VALUES(1, 'Amenity2', 0);
INSERT INTO amenity (room_id, amenity, checked) VALUES(2, 'Amenity2', 0);

INSERT INTO amenity (room_id, amenity, checked) VALUES(1, 'Amenity3', 0);

INSERT INTO amenity (room_id, amenity, checked) VALUES(2, 'Amenity4', 0);

现有查询的问题 #

• 普通内连接仅返回已有关联的记录（共6条）：

select * from room r join amenity a on r.id =a.room_id where a.room_id in(1,2);

结果：

1   room1   1   1   Amenity1    0
1   room1   2   1   Amenity2    0
1   room1   3   1   Amenity3    0
2   room2   4   2   Amenity1    0
2   room2   5   2   Amenity2    0
2   room2   6   2   Amenity4    0

• 常规左外连接同样无法生成无关联的组合（仅返回6条）：

select * from room r left outer join amenity a on a.room_id =r.id where r.id in(1,2);

期望输出 #

需要生成房间与所有唯一设施的全量组合，包含无关联的记录（共8条）：

roomid idamenity amenity        
1      1         Amenity1
2      2         Amenity1
1      3         Amenity2
2      4         Amenity2
1      5         Amenity3
2      null      Amenity3
1      null      Amenity4
2      6         Amenity4

解决方案 #

通过笛卡尔积生成全量组合，再左关联原amenity表获取已有关联的记录，具体SQL如下：

SELECT
    r.id AS roomid,
    a.id AS idamenity,
    unique_amenities.amenity
FROM
    room r
-- 生成所有房间与所有唯一设施的笛卡尔积
CROSS JOIN (SELECT DISTINCT amenity FROM amenity) unique_amenities
-- 左关联原amenity表，匹配对应房间+设施的已有记录
LEFT JOIN amenity a ON r.id = a.room_id AND unique_amenities.amenity = a.amenity
WHERE
    r.id IN (1, 2)
ORDER BY
    unique_amenities.amenity,
    r.id;

原理说明 #

1. 先用SELECT DISTINCT amenity FROM amenity提取所有唯一的设施名称，得到一个包含4条记录的临时表；
2. 用CROSS JOIN将房间表与这个唯一设施临时表做笛卡尔积，生成2×4=8条全量组合记录；
3. 再通过LEFT JOIN关联原amenity表，匹配对应房间和设施的已有记录，没有匹配的记录则返回null；
4. 最后排序保证结果与期望输出一致。

内容的提问来源于stack exchange，提问作者jam