判断一个时间段是否在另一个时间段内

假设我们有一张表：test

CREATE TABLE `test` (
    `id` INT(11) UNSIGNED NOT NULL AUTO_INCREMENT,
    `start_time` DATETIME NULL DEFAULT NULL COMMENT '开始时间',
    `end_time` DATETIME NULL DEFAULT NULL COMMENT '结束时间',
    PRIMARY KEY (`id`) USING BTREE
)
COLLATE='utf8_unicode_ci'
ENGINE=INNODB
;

然后插入一条数据：

INSERT INTO `test` (`start_time`, `end_time`) VALUES ('2020-08-04 10:00:00', '2020-08-04 12:00:00');

然后我们查询看看：

select * from test;
+----+---------------------+---------------------+
| id | start_time          | end_time            |
+----+---------------------+---------------------+
|  1 | 2020-08-04 10:00:00 | 2020-08-04 12:00:00 |
+----+---------------------+---------------------+
1 row in set (0.00 sec)

接下来我们就可以来写sql语句了：

SET @BeignTime = '2020-08-04 05:00:00';
SET @EndTime   = '2020-08-04 10:00:00';
SELECT *
FROM test
WHERE (
@BeignTime BETWEEN start_time AND end_time OR
@EndTime   BETWEEN start_time AND end_time OR
start_time BETWEEN @BeignTime AND @EndTime OR
end_time   BETWEEN @BeignTime AND @EndTime
);
+----+---------------------+---------------------+
| id | start_time          | end_time            |
+----+---------------------+---------------------+
|  1 | 2020-08-04 10:00:00 | 2020-08-04 12:00:00 |
+----+---------------------+---------------------+
1 row in set (0.00 sec)
SET @BeignTime = '2020-08-04 05:00:00';
SET @EndTime   = '2020-08-04 09:00:00';
SELECT *
FROM test
WHERE (
@BeignTime BETWEEN start_time AND end_time OR
@EndTime   BETWEEN start_time AND end_time OR
start_time BETWEEN @BeignTime AND @EndTime OR
end_time   BETWEEN @BeignTime AND @EndTime
);
Empty set (0.00 sec)

可以像下面这样：

SET @BeignTime = '2020-08-04 05:00:00';
SET @EndTime   = '2020-08-04 09:00:00';
SELECT *
FROM test
WHERE (
    (@BeignTime >= start_time AND @BeignTime <= end_time) OR 
    (@EndTime   >= start_time AND @EndTime   <= end_time) OR 
    (start_time >= @BeignTime AND start_time <= @EndTime) OR 
    (end_time   >= @BeignTime AND end_time   <= @EndTime)
);
Empty set (0.00 sec)
SET @BeignTime = '2020-08-04 05:00:00';
SET @EndTime   = '2020-08-04 10:00:00';
SELECT *
FROM test
WHERE (
    (@BeignTime >= start_time AND @BeignTime <= end_time) OR 
    (@EndTime   >= start_time AND @EndTime   <= end_time) OR 
    (start_time >= @BeignTime AND start_time <= @EndTime) OR 
    (end_time   >= @BeignTime AND end_time   <= @EndTime)
);
+----+---------------------+---------------------+
| id | start_time          | end_time            |
+----+---------------------+---------------------+
|  1 | 2020-08-04 10:00:00 | 2020-08-04 12:00:00 |
+----+---------------------+---------------------+
1 row in set (0.00 sec)