基于MySQL+PHP解决活动日期时间冲突问题的技术问询

嘿，我来帮你搞定活动时间冲突的问题！结合MySQL和PHP，我们可以从应用层和数据库层两层入手，既给用户即时的反馈，又保证数据的绝对一致性。

第一步：规范数据库表结构 #

首先先把你的活动表结构优化得更规范一点，方便后续操作：

CREATE TABLE events (
    id INT AUTO_INCREMENT PRIMARY KEY,
    name VARCHAR(255) NOT NULL,
    event_date DATE NOT NULL, -- 活动日期
    start_time TIME NOT NULL, -- 活动开始时间
    duration_min INT NOT NULL CHECK (duration_min > 0) -- 活动时长（分钟）
);

然后把你提供的示例数据插进去：

INSERT INTO events (name, event_date, start_time, duration_min)
VALUES 
('Carneval', '2018-04-30', '12:00:00', 150),
('Fairytale evening', '2018-04-30', '20:00:00', 200);

第二步：PHP层的冲突校验（用户友好型） #

在用户提交新活动之前，我们先在PHP里检查是否和已有活动时间冲突，这样能立刻给用户提示，不用等到数据库报错。这里用PDO来操作数据库，安全又方便：

// 检查活动是否冲突的函数
function isEventConflict($pdo, $eventDate, $startTime, $durationMin) {
    // 计算新活动的结束时间
    $newEndTime = date('H:i:s', strtotime($startTime) + ($durationMin * 60));
    
    // 核心SQL：查询同一日期下，时间区间有重叠的活动
    $sql = "
        SELECT COUNT(*) 
        FROM events 
        WHERE event_date = :event_date
        AND start_time < :new_end_time
        AND ADDTIME(start_time, SEC_TO_TIME(:duration * 60)) > :new_start_time
    ";
    
    $stmt = $pdo->prepare($sql);
    $stmt->execute([
        ':event_date' => $eventDate,
        ':new_end_time' => $newEndTime,
        ':duration' => $durationMin,
        ':new_start_time' => $startTime
    ]);
    
    // 如果计数大于0，说明有冲突
    return $stmt->fetchColumn() > 0;
}

// 实际使用示例
try {
    // 连接数据库（替换成你的数据库信息）
    $pdo = new PDO('mysql:host=localhost;dbname=your_db;charset=utf8', 'username', 'password');
    $pdo->setAttribute(PDO::ATTR_ERRMODE, PDO::ERRMODE_EXCEPTION);
    
    // 假设这是用户提交的新活动数据
    $newActivity = [
        'name' => 'Spring Workshop',
        'date' => '2018-04-30',
        'start' => '14:00:00',
        'duration' => 120
    ];
    
    // 检查冲突
    if (isEventConflict($pdo, $newActivity['date'], $newActivity['start'], $newActivity['duration'])) {
        echo "抱歉，这个时间段已经有活动了，请换个时间！";
    } else {
        // 插入新活动
        $insertSql = "
            INSERT INTO events (name, event_date, start_time, duration_min)
            VALUES (:name, :event_date, :start_time, :duration_min)
        ";
        $insertStmt = $pdo->prepare($insertSql);
        $insertStmt->execute([
            ':name' => $newActivity['name'],
            ':event_date' => $newActivity['date'],
            ':start_time' => $newActivity['start'],
            ':duration_min' => $newActivity['duration']
        ]);
        echo "活动添加成功！";
    }
} catch (PDOException $e) {
    echo "数据库出错啦：" . $e->getMessage();
}

第三步：数据库层的强制约束（防止绕过PHP操作） #

如果有人直接通过SQL客户端修改数据库，PHP层的校验就失效了。所以我们需要在数据库层面加触发器，强制检查时间冲突：

-- 插入前检查冲突的触发器
DELIMITER //
CREATE TRIGGER check_event_conflict_before_insert
BEFORE INSERT ON events
FOR EACH ROW
BEGIN
    DECLARE conflict_count INT;
    SELECT COUNT(*) INTO conflict_count
    FROM events
    WHERE event_date = NEW.event_date
    AND start_time < ADDTIME(NEW.start_time, SEC_TO_TIME(NEW.duration_min * 60))
    AND ADDTIME(start_time, SEC_TO_TIME(duration_min * 60)) > NEW.start_time;
    
    IF conflict_count > 0 THEN
        -- 抛出自定义错误
        SIGNAL SQLSTATE '45000' SET MESSAGE_TEXT = '活动时间冲突，无法插入';
    END IF;
END //
DELIMITER ;

-- 更新前检查冲突的触发器（排除自身，因为更新自己的时间时要和其他活动对比）
DELIMITER //
CREATE TRIGGER check_event_conflict_before_update
BEFORE UPDATE ON events
FOR EACH ROW
BEGIN
    DECLARE conflict_count INT;
    SELECT COUNT(*) INTO conflict_count
    FROM events
    WHERE id != NEW.id -- 排除当前要更新的活动
    AND event_date = NEW.event_date
    AND start_time < ADDTIME(NEW.start_time, SEC_TO_TIME(NEW.duration_min * 60))
    AND ADDTIME(start_time, SEC_TO_TIME(duration_min * 60)) > NEW.start_time;
    
    IF conflict_count > 0 THEN
        SIGNAL SQLSTATE '45000' SET MESSAGE_TEXT = '活动时间冲突，无法更新';
    END IF;
END //
DELIMITER ;

时间冲突的核心逻辑说明 #

为什么用这样的SQL判断冲突？因为两个时间区间重叠的情况有三种：

1. 新活动完全包含已有活动
2. 新活动被已有活动完全包含
3. 新活动和已有活动部分重叠

而start_time < 新活动结束时间 AND 已有活动结束时间 > 新活动开始时间这个条件，可以覆盖所有这三种重叠情况，绝对不会漏判。

内容的提问来源于stack exchange，提问作者Atom