需求背景

项目需要开发一个线上会议室的预约功能，对接Zoom提供500个线上会议室，以满足用户线上会议需求。同时要求系统分配的会议室越离散越好，防止用户会议室使用超出预定时间时，影响下一场会议的进行。其中预约的会议的开始时间和结束时间只能在同一天，无法跨天。

总体方案

1. 按照最大间隔时间预约会议，并且每场会议至少间隔一小时
2. 定时任务，每10分钟执行一次，判断半小时内是否有会议即将开始，是结束当前会议室会议并更新会议室密码

数据库设计

meeting_online_roon：（存储会议室信息）

CREATE TABLE `meeting_online_room` (
  `id` bigint(20) unsigned NOT NULL AUTO_INCREMENT COMMENT '主键ID',
  `zoom_id` varchar(100) NOT NULL COMMENT 'zoom会议室ID',
  `host_key` varchar(64) NOT NULL COMMENT '主持人key',
  `email` varchar(100) DEFAULT NULL COMMENT '邮箱',
  PRIMARY KEY (`id`),
  UNIQUE KEY `udx_room_id` (`zoom_id`)
) ENGINE=InnoDB AUTO_INCREMENT=506 DEFAULT CHARSET=utf8mb4 COMMENT='线上会议室基础信息';

meeting_online_room_booking_detail：（存储会议室预定信息）

CREATE TABLE `meeting_online_room_booking_detail` (
  `id` bigint(20) unsigned NOT NULL AUTO_INCREMENT COMMENT '主键ID',
  `room_id` bigint(20) NOT NULL COMMENT '线上会议室ID',
  `zoom_id` varchar(100) NOT NULL COMMENT 'zoom会议室ID',
  `meeting_id` varchar(100) NOT NULL COMMENT '会议ID',
  `start_time` datetime NOT NULL COMMENT '开始时间',
  `end_time` datetime NOT NULL COMMENT '结束时间',
  `duration` int(6) NOT NULL COMMENT '会议时长',
  `password` varchar(64) NOT NULL DEFAULT '1' COMMENT '会议室密码',
  `create_time` datetime NOT NULL DEFAULT CURRENT_TIMESTAMP COMMENT '创建时间',
  `update_time` datetime NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP COMMENT '修改时间',
  `status` tinyint(2) NOT NULL DEFAULT '1' COMMENT '会议预定状态',
  PRIMARY KEY (`id`),
  KEY `idx_room_id` (`room_id`),
  KEY `idx_zoom_id` (`zoom_id`),
  KEY `idx_meeting_id` (`meeting_id`),
  KEY `idx_start_time` (`start_time`),
  KEY `idx_end_time` (`end_time`)
) ENGINE=InnoDB AUTO_INCREMENT=15334 DEFAULT CHARSET=utf8mb4 COMMENT='线上会议室预约信息';

分配算法

1. 找出未分配的会议室进行分配
2. 所有会议室都被分配的情况下：
   1. 找出满足当前会议时间的所有会议室
   2. 计算当前各会议室与预定的会议时间间隔，选出时间间隔最大的会议室，进行分配。

策略一： 找出所有未分配的会议室：

SELECT id, zoom_id,host_key
FROM meeting_online_room mr
WHERE NOT EXISTS
  (select room_id FROM meeting_online_room_booking_detail md where status != - 1 
    AND DATE(start_time) = DATE(#{startTime}) and mr.id = md.room_id)
limit 0, 1;

image-20200623194911655.png

策略二：分配满足条件且时间间隔最大的会议室

1. 过滤所有不满足时间条件的会议室

会议时间冲突场景：

image-20200623195128379.png

因此sql就是：

SELECT room_id FROM
  meeting_online_room_booking_detail
WHERE (start_time  >=  #{startTime} AND start_time  < #{endTime})
   OR (end_time  >  #{startTime} AND end_time <=  #{endTime})
   OR ((#{startTime} between start_time and end_time) and (#{endTime} between start_time and end_time)
  )AND STATUS != - 1
       
 或者：
   SELECT room_id FROM  meeting_online_room_booking_detail
  WHERE (start_time < ! [ CDATA [ >= ] ] > #{startTime} AND start_time < {endTime}) and status != -1
   UNION
  SELECT room_id FROM meeting_online_room_booking_detail
  WHERE (end_time  >  #{startTime} AND end_time <= #{endTime}) and status != -1
   UNION
  SELECT room_i FROM meeting_online_room_booking_detail
  WHERE ((#{startTime} between start_time and end_time) and (#{endTime} between start_time and end_time)) 
      AND STATUS != - 1

2. 计算满足当前预约会议时间的会议室时间差

 SELECT id, room_id, 
 MIN(LEAST(ABS(TIME_TO_SEC(start_time) - TIME_TO_SEC(#{endTime})), ABS(TIME_TO_SEC(end_time) - TIME_TO_SEC(#{startTime})))) as maxTime
 FROM meeting_online_room_booking_detail WHERE DATE(start_time) = DATE(#{startTime}) AND status != -1
       < IF test = "list != null and list.size > 0" >
  AND room_id NOT IN (#{room_id1}, #{room_id2} ...)
  GROUP BY `room_id`
  ORDER BY maxTime DESC LIMIT 0,1;

其中：

LEAST(ABS(TIME_TO_SEC(start_time) - TIME_TO_SEC(#{endTime})), ABS(TIME_TO_SEC(end_time) - TIME_TO_SEC(#{startTime})))

是计算某个会议室与当前会议时间最大时间间隔。

例如会议室A预定了2场会议室： 8:00-9:00 14:00-15:00，现在要预定的会议时间为：10:00 - 12:00 。 最大时间间隔为：1小时，而不是2小时

所以总体的sql就是：（以下采用Mybatis写的)

SELECT
        id, room_id, min(LEAST(ABS(TIME_TO_SEC(start_time) - TIME_TO_SEC(#{endTime})),
        ABS(TIME_TO_SEC(end_time) - TIME_TO_SEC(#{startTime})))) as maxTime
        FROM meeting_online_room_booking_detail
        WHERE DATE(start_time)=DATE(#{startTime}) AND status != -1 AND room_id NOT IN
            (SELECT room_id FROM meeting_online_room_booking_detail
            WHERE (start_time <![CDATA[ >= ]]> #{startTime} AND start_time <![CDATA[ < ]]> #{endTime})
            OR (end_time <![CDATA[ > ]]> #{startTime} AND end_time <![CDATA[ <= ]]> #{endTime})
            OR ((#{startTime} between start_time and end_time) and (#{endTime} between start_time and end_time)
            ) AND status!=-1
        )
        group by `room_id` order by maxTime desc limit 0, 1;

这种方法是通过sql直接分配好会议室，不需要在内容中再进行处理，速度较快，比较简单。