mysql - 如果日期与现有日期不重叠，则将日期范围插入日期列 [英] 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 |
-------------------------------------------------------
它用于定义日期范围可用性，例如.start_date 和 end_date 之间的所有日期对于给定的 acc_id 都不可用.
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.
根据用户输入，我正在关闭不同的范围，但如果用户尝试关闭(提交)一个范围，该范围的开始或结束日期在现有范围内的某处(对于提交 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 个步骤完成，2 个查询，中间有一些 PHP 逻辑.
that pretty much explains how to do it in 2 steps, 2 queries with some PHP logic in between.
但我想知道是否可以在一个插入语句中完成.我最终会检查受影响的行是否成功或失败(子问题:是否有更方便的方法来检查它是否因日期重叠之外的其他原因而失败?)
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.
推荐答案
遗憾的是，仅使用 MySQL 这是不可能的.或者至少，实际上.首选方法是使用 SQL CHECK 约束，这些约束在 SQL 语言标准中.但是，MySQL 不支持它们.
Sadly, using just MySQL this is impossible. Or at least, practically. The preferred way would be using SQL CHECK constraints, these are in the SQL language standard. However, MySQL does not support them.
参见:https://dev.mysql.com/doc/refman/5.7/en/create-table.html
CHECK 子句被解析但被所有存储引擎忽略.
The CHECK clause is parsed but ignored by all storage engines.
似乎 PostgreSQL 确实支持对表的 CHECK 约束，但我不确定您切换数据库引擎是否可行，或者是否值得为使用该功能而烦恼.
It seems PostgreSQL does support CHECK constraints on tables, but I'm not sure how viable it is for you to switch database engine or if that's even worth the trouble just to use that feature.
在 MySQL 中，可以使用触发器来解决这个问题，它会在插入/更新发生之前检查重叠的行，并使用 SIGNAL 语句抛出错误.(参见:https://dev.mysql.com/doc/refman/5.7/en/signal.html) 但是，要使用此解决方案，您必须使用最新的 MySQL 版本.
In MySQL a trigger could be used to solve this problem, which would check for overlapping rows before the insert/update occurs and throw an error using the SIGNAL statement. (See: https://dev.mysql.com/doc/refman/5.7/en/signal.html) However, to use this solution you'd have to use an up-to-date MySQL version.
除了纯 SQL 解决方案外，这通常是在应用程序逻辑中完成的，因此无论哪个程序访问 MySQL 数据库，通常都会通过请求 SELECT COUNT 中的新条目违反的每一行来检查这些类型的约束(id) ... 语句.如果返回的计数大于 0，它根本不会插入/更新.
Apart from pure SQL solutions, this typically is done in application logic, so whichever program is accessing the MySQL database typically checks for these kind of constraints by requesting every row that is violated by the new entry in a SELECT COUNT(id) ... statement. If the returned count is larger than 0 it simply doesn't to the insert/update.
这篇关于mysql - 如果日期与现有日期不重叠，则将日期范围插入日期列的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！
