30 lines
1.1 KiB
SQL
Executable File
30 lines
1.1 KiB
SQL
Executable File
-- 挂号单状态自动更新 - MySQL 事件（可选，需开启 event_scheduler）
|
||
-- 表名请根据实际前缀修改（如 zyt_doctor_appointment、la_doctor_appointment）
|
||
|
||
-- 创建事件：每10分钟执行一次
|
||
-- SET GLOBAL event_scheduler = ON; -- 需先开启事件调度器
|
||
|
||
DELIMITER $$
|
||
|
||
DROP EVENT IF EXISTS evt_update_appointment_status$$
|
||
|
||
CREATE EVENT evt_update_appointment_status
|
||
ON SCHEDULE EVERY 10 MINUTE
|
||
DO
|
||
BEGIN
|
||
-- 1. 超过8小时 -> 已取消(status=2)
|
||
UPDATE zyt_doctor_appointment
|
||
SET status = 2, update_time = UNIX_TIMESTAMP()
|
||
WHERE status = 1
|
||
AND CONCAT(appointment_date, ' ', IFNULL(appointment_time, '00:00:00')) <= DATE_SUB(NOW(), INTERVAL 8 HOUR);
|
||
|
||
-- 2. 已过时间但未超8小时 -> 已过号(status=4)
|
||
UPDATE zyt_doctor_appointment
|
||
SET status = 4, update_time = UNIX_TIMESTAMP()
|
||
WHERE status = 1
|
||
AND CONCAT(appointment_date, ' ', IFNULL(appointment_time, '00:00:00')) < NOW()
|
||
AND CONCAT(appointment_date, ' ', IFNULL(appointment_time, '00:00:00')) > DATE_SUB(NOW(), INTERVAL 8 HOUR);
|
||
END$$
|
||
|
||
DELIMITER ;
|