mysql - INSERT日期范围到日期列IF日期不与现有列重叠 [英] mysql - INSERT date range into date columns IF dates don't overlap with existing ones
问题描述
我有以下表格结构：
表名称：avail
id (autoincremetn) | acc_id | start_date | end_date
-------------------------------------------------------
1 | 175 | 2015-05-26 | 2015-05-31 |
-------------------------------------------------------
2 | 175 | 2015-07-01 | 2015-07-07 |
-------------------------------------------------------
它用于定义日期范围的可用性，例如。对于给定的acc_id，start_date和end_date之间的所有日期都不可用。
It's used for defining date range availability eg. all dates in between start_date and end_date are unavailable for the given acc_id.
根据用户输入，我关闭不同的范围，但是如果用户想要尝试关闭（提交）已经在DB中已存在的范围（对于提交的acc_id）开始或结束的范围。
在这个例子中，一个start_date：2015-05-30 end_date：2015-06-04将是一个很好的失败候选人。
Based on user input I'm closing different ranges but I would like to throw an error IF an user tries to close (submit) a range that has it's start OR end_date somewhere in the range of an already existing one (for the submitted acc_id) in the DB. In this example a start_date: 2015-05-30 end_date: 2015-06-04 would be a good fail candidate.
我发现这个QA ：
MySQL重叠日期，无冲突
几乎解释了如何在两个步骤中执行，2个查询之间有一些PHP逻辑。
that pretty much explains how to do it in 2 steps, 2 queries with some PHP logic in between.
但我想知道是否可以在一个insert语句中完成。
我最终将检查影响成功或失败的行（小问题：除了日期重叠之外，还有一个更方便的方法来检查是否失败？）
But I was wondering if it can be done in one insert statement. I would eventually check for rows affected for success or fail (sub question: is there a more convenient way to check if it failed for some other reason besides date overlap?)
编辑：
为了回应Petr的评论，我会进一步指定验证：
In response to Petr's comment I'll specify further the validation:
应该避免任何类型的重叠，即使是包含
的整个范围或发现自己在现有的范围内。此外，如果
开始或结束日期等于现有的开始或结束日期，则必须将
视为重叠。有时某些acc_id已经比表中的一个响应更多
，所以验证应该对
所有具有给定acc_id的条目进行。
any kind of overlapping should be avoided, even the one embracing the whole range or finding itself inside the existing range. Also, if start or end dates equal the existing start or end dates it must be considered an overlap. Sometimes certain acc_id will already have more than one rang in the table so the validation should be done against all entries with a given acc_id.
推荐答案
考虑以下...
DROP TABLE IF EXISTS avail;
CREATE TABLE avail
(id INT NOT NULL AUTO_INCREMENT PRIMARY KEY
,acc_id INT NOT NULL
,start_date DATE NOT NULL
,end_date DATE NOT NULL
);
INSERT INTO avail VALUES
(1,175,'2015-05-26','2015-05-31'),
(2,175,'2015-07-01','2015-07-07');
SELECT * FROM avail;
+----+--------+------------+------------+
| id | acc_id | start_date | end_date |
+----+--------+------------+------------+
| 1 | 175 | 2015-05-26 | 2015-05-31 |
| 2 | 175 | 2015-07-01 | 2015-07-07 |
+----+--------+------------+------------+
INSERT INTO avail
(acc_id,start_date,end_date)
SELECT 176,'2015-06-01','2015-06-05'
FROM (SELECT 1) x -- was FROM avail
LEFT
JOIN avail y
ON y.start_date < '2015-06-05' AND y.end_date > '2015-06-01'
WHERE y.id IS NULL LIMIT 1;
Query OK, 1 row affected (0.01 sec)
Records: 1 Duplicates: 0 Warnings: 0
SELECT * FROM avail;
+----+--------+------------+------------+
| id | acc_id | start_date | end_date |
+----+--------+------------+------------+
| 1 | 175 | 2015-05-26 | 2015-05-31 |
| 2 | 175 | 2015-07-01 | 2015-07-07 |
| 3 | 176 | 2015-06-01 | 2015-06-05 |
+----+--------+------------+------------+
INSERT INTO avail
(acc_id,start_date,end_date)
SELECT 176,'2015-06-01','2015-06-05'
FROM avail
LEFT
JOIN avail y
ON y.start_date < '2015-06-05' AND y.end_date > '2015-06-01'
WHERE y.id IS NULL LIMIT 1;
Query OK, 0 rows affected (0.00 sec)
Records: 0 Duplicates: 0 Warnings: 0
SELECT * FROM avail;
+----+--------+------------+------------+
| id | acc_id | start_date | end_date |
+----+--------+------------+------------+
| 1 | 175 | 2015-05-26 | 2015-05-31 |
| 2 | 175 | 2015-07-01 | 2015-07-07 |
| 3 | 176 | 2015-06-01 | 2015-06-05 |
+----+--------+------------+------------+
这篇关于mysql - INSERT日期范围到日期列IF日期不与现有列重叠的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！
