mysql+php，避免日期限制

ctehm74n 于 2021-06-21 发布在 Mysql

关注(0)|答案(1)|浏览(341)

我有一个数据库，里面有“卡内瓦。。。。

+-------------------+------------+---------------+-----------------------+
+   Name            +    Date    + action starts + action duration (min) + 
+-------------------+------------+---------------+-----------------------+
+ Carneval          + 2018-04-30 +   12:00:00    +          150          +
+-------------------+------------+---------------+-----------------------+
+ Fairytale evening + 2018-04-30 +   20:00:00    +          200          +
+-------------------+------------+---------------+-----------------------+

我该如何避免在数据库中输入一个动作，比如2018年4月30日11:58:00或13:00:00的“吃你能吃的东西”，因为这些时间显然与本例中的其他动作（如卡内瓦尔）一致。。。
我正在使用php在表中插入数据。。。

mysql php

来源：https://stackoverflow.com/questions/50099329/mysql-php-avoid-date-conficts

1条答案

按热度按时间

pnwntuvh1#

DROP TABLE IF EXISTS my_table;

CREATE TABLE my_table
(event_id SERIAL PRIMARY KEY
,name VARCHAR(12) NOT NULL 
,start_dt DATETIME NOT NULL 
,end_dt DATETIME NULL
);

INSERT INTO my_table VALUES
(1, 'Carnival', '2018-04-30 12:00:00','2018-04-30 12:00:00'+INTERVAL 150 MINUTE),
(2, 'Fairytale','2018-04-30 20:00:00','2018-04-30 20:00:00'+INTERVAL 200 MINUTE);

SELECT * FROM my_table;
 +----------+-----------+---------------------+---------------------+
 | event_id | name      | start_dt            | end_dt              |
 +----------+-----------+---------------------+---------------------+
 |        1 | Carnival  | 2018-04-30 12:00:00 | 2018-04-30 14:30:00 |
 |        2 | Fairytale | 2018-04-30 20:00:00 | 2018-04-30 23:20:00 |
 +----------+-----------+---------------------+---------------------+

INSERT INTO my_table (name,start_dt,end_dt) 
SELECT 'Whatever','2018-04-20 11:58:00','2018-04-30 13:00' 
  FROM (SELECT 1) x 
  LEFT 
  JOIN my_table y 
    ON y.end_dt > '2018-04-30 11:58:00' 
   AND y.start_dt < '2018-04-30 13:00:00' 
 WHERE y.event_id IS NULL;

SELECT * FROM my_table;
 +----------+-----------+---------------------+---------------------+
 | event_id | name      | start_dt            | end_dt              |
 +----------+-----------+---------------------+---------------------+
 |        1 | Carnival  | 2018-04-30 12:00:00 | 2018-04-30 14:30:00 |
 |        2 | Fairytale | 2018-04-30 20:00:00 | 2018-04-30 23:20:00 |
 +----------+-----------+---------------------+---------------------+

赞(0）回复(0）举报 2021-06-21

我来回答

mysql+php，避免日期限制

1条答案

相关问题

热门标签

最新问答