我是靠谱客的博主 健忘胡萝卜,这篇文章主要介绍mysql中int的区间,mysql constarint区间交集,现在分享给大家,希望可以做个参考。

I have mysql table

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;

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');

-- Attempt 1: conflicting dates

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;

-- 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中int的区间,mysql constarint区间交集的全部内容,更多相关mysql中int的区间,mysql内容请搜索靠谱客的其他文章。

本图文内容来源于网友提供,作为学习参考使用,或来自网络收集整理,版权属于原作者所有。
点赞(86)

评论列表共有 0 条评论

立即
投稿
返回
顶部