mysql constarint区间交集 [英] mysql constarint interval intersection
本文介绍了mysql constarint区间交集的处理方法,对大家解决问题具有一定的参考价值,需要的朋友们下面随着小编来一起学习吧!
问题描述
我有mysql表
CREATE TABLE `range` (
`id` int(11) NOT NULL,
`object_id` int NOT NULL,
`datetime_from` datetime NOT NULL,
`datetime_to` datetime NOT NULL,
PRIMARY KEY (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8;
请帮助提供有关mysql级别约束的实现:同一object_id没有时间间隔交集.
Please help to provide on mysql level constraint implementation: no time interval intersection for same object_id.
推荐答案
触发器很好,但是通过证明不需要触发器,请考虑以下内容...
A trigger is fine, but by way of demonstrating that no trigger is required, consider the following...
DROP TABLE IF EXISTS my_table;
CREATE TABLE my_table (
id SERIAL,
dt_from DATE NOT NULL,
dt_to DATE NOT NULL,
PRIMARY KEY (id)
);
INSERT INTO my_table (dt_from,dt_to)
VALUES
('2018-05-31','2018-06-03');
-尝试1:日期冲突
SET @dt_from = '2018-05-28';
SET @dt_to = '2018-06-01';
INSERT INTO my_table (dt_from,dt_to)
SELECT @dt_from
, @dt_to
FROM (SELECT 1) x
LEFT
JOIN my_table y
ON y.dt_from < @dt_to
AND y.dt_to > @dt_from
WHERE y.id IS NULL;
-尝试2:不冲突的日期
-- Attempt 2: Non-conflicting dates
SET @dt_from = '2018-06-04';
SET @dt_to = '2018-06-06';
INSERT INTO my_table (dt_from,dt_to)
SELECT @dt_from
, @dt_to
FROM (SELECT 1) x
LEFT
JOIN my_table y
ON y.dt_from < @dt_to
AND y.dt_to > @dt_from
WHERE y.id IS NULL;
SELECT * FROM my_table;
+----+------------+------------+
| id | dt_from | dt_to |
+----+------------+------------+
| 1 | 2018-05-31 | 2018-06-03 |
| 2 | 2018-06-04 | 2018-06-06 |
+----+------------+------------+
请参阅.冲突的日期将被忽略.
See. Conflicting dates are ignored.
这篇关于mysql constarint区间交集的文章就介绍到这了,希望我们推荐的答案对大家有所帮助,也希望大家多多支持IT屋!
查看全文