mysql constarint区间交集 [英] mysql constarint interval intersection

问题描述

我有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屋！