如何在mysql中创建调度程序，但是调度程序在我的电脑中由日期触发

lymnna71 于 2021-06-20 发布在 Mysql

关注(0)|答案(3)|浏览(250)

我有四张table

CREATE DATABASE IF NOT EXISTS `b_hotel`;

USE `b_hotel`;

/*Table structure for table `booking` */

DROP TABLE IF EXISTS `booking`;

CREATE TABLE `booking` (
  `booking_id` int(11) NOT NULL,
  `customer_id` int(11) DEFAULT NULL,
  `tgl_book` datetime DEFAULT NULL,
  `checkin` date DEFAULT NULL,
  `checkout` date DEFAULT NULL,
  `no_kamar` int(11) DEFAULT NULL,
  PRIMARY KEY (`booking_id`),
  KEY `customer_id` (`customer_id`),
  KEY `no_kamar` (`no_kamar`),
  CONSTRAINT `booking_ibfk_1` FOREIGN KEY (`customer_id`) REFERENCES `customer` (`customer_id`),
  CONSTRAINT `booking_ibfk_2` FOREIGN KEY (`no_kamar`) REFERENCES `kamar` (`no_kamar`)
) ENGINE=InnoDB DEFAULT CHARSET=latin1;

/*Data for the table `booking` */

/*Table structure for table `customer` */

DROP TABLE IF EXISTS `customer`;

CREATE TABLE `customer` (
  `customer_id` int(11) NOT NULL,
  `nama_customer` varchar(50) DEFAULT NULL,
  `no_telp` varchar(13) DEFAULT NULL,
  `email` varchar(50) DEFAULT NULL,
  PRIMARY KEY (`customer_id`)
) ENGINE=InnoDB DEFAULT CHARSET=latin1;

/*Data for the table `customer` */

insert  into `customer`(`customer_id`,`nama_customer`,`no_telp`,`email`) values 
(1,'ABC','123','email@email.com');

/*Table structure for table `kamar` */

DROP TABLE IF EXISTS `kamar`;

CREATE TABLE `kamar` (
  `no_kamar` int(11) NOT NULL,
  `no_tipe` smallint(6) DEFAULT NULL,
  `status` enum('Kosong','Terpakai') DEFAULT NULL,
  `img` varchar(50) DEFAULT NULL,
  PRIMARY KEY (`no_kamar`),
  KEY `no_tipe` (`no_tipe`),
  CONSTRAINT `kamar_ibfk_1` FOREIGN KEY (`no_tipe`) REFERENCES `tipe_kamar` (`no_tipe`)
) ENGINE=InnoDB DEFAULT CHARSET=latin1;

/*Data for the table `kamar` */

insert  into `kamar`(`no_kamar`,`no_tipe`,`status`,`img`) values 
(101,1,'Kosong','assets/img/room/standard_1.jpg'),
(102,2,'Kosong','assets/img/room/standard_2.jpg'),
(301,3,'Kosong','assets/img/room/deluxe_1.jpg'),
(302,4,'Kosong','assets/img/room/deluxe_2.jpg'),
(601,5,'Kosong','assets/img/room/executive_1.jpg'),
(602,6,'Kosong','assets/img/room/executive_2.jpg');

/*Table structure for table `tipe_kamar` */

DROP TABLE IF EXISTS `tipe_kamar`;

CREATE TABLE `tipe_kamar` (
  `no_tipe` smallint(6) NOT NULL AUTO_INCREMENT,
  `nama_tipe` varchar(30) DEFAULT NULL,
  `harga` int(7) DEFAULT NULL,
  `dewasa` smallint(1) DEFAULT NULL,
  PRIMARY KEY (`no_tipe`)
) ENGINE=InnoDB AUTO_INCREMENT=7 DEFAULT CHARSET=latin1;

/*Data for the table `tipe_kamar` */

insert  into `tipe_kamar`(`no_tipe`,`nama_tipe`,`harga`,`dewasa`) values 
(1,'Standard',400000,2),
(2,'Standard Big',800000,4),
(3,'Deluxe',800000,2),
(4,'Deluxe Big',1600000,4),
(5,'Suite',1600000,2),
(6,'Suite Big',3200000,4);

我要零钱 kamar . status 到“terpakai”如果 date in my computer 等于 booking . checkin .

mysql

来源：https://stackoverflow.com/questions/51576274/how-to-create-scheduler-in-mysql-but-the-scheduler-triggered-by-date-in-my-comp

3条答案

按热度按时间

zysjyyx41#

仍然没有工作，我会尝试创建每10秒（所以，我不必等待太久）。
代码：

DELIMITER $$
CREATE  /*[DEFINER = { user | CURRENT_USER }]*/ EVENT `b_hotel`.`auto_checkin`

ON SCHEDULE

EVERY 5 SECOND
DO
    BEGIN
         IF booking.checkin = DATE(NOW()) THEN
        UPDATE kamar JOIN booking ON kamar.no_kamar = booking.no_kamar
        SET kamar.status = 'Terpakai'
        WHERE kamar.no_kamar = booking.no_kamar;
         END IF;
    END$$
DELIMITER ;

如果我错了，请纠正我

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

8iwquhpp2#

我试着这样做，但是工作。。请纠正我。

DELIMITER $$
CREATE  /*[DEFINER = { user | CURRENT_USER }]*/ EVENT `b_hotel`.`auto_checkin`

ON SCHEDULE

EVERY 5 SECOND
DO
    BEGIN       
        UPDATE kamar JOIN booking ON kamar.no_kamar = booking.no_kamar
        SET kamar.status = "Terpakai"
        WHERE kamar.no_kamar = booking.no_kamar AND DATE(NOW()) = booking.checkin;
    END$$
DELIMITER ;

如果我错了，请纠正我

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

vwhgwdsa3#

您可以使用mysql事件调度器。
启用计划程序

SET GLOBAL event_scheduler = ON;

创建定期事件

CREATE EVENT e_hourly 
ON SCHEDULE EVERY 1 HOUR COMMENT 'Clears out sessions table each hour.' 
DO 
DELETE FROM site_activity.sessions;

mysql事件调度器

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

我来回答

如何在mysql中创建调度程序，但是调度程序在我的电脑中由日期触发

3条答案

相关问题

热门标签

最新问答