ตั้งกำหนดเวลาคิวรี่ข้อมูลใน MySQL ด้วย Event Scheduler ตอนที่ 2

CREATE TABLE `tbl_testcron` (
  `id_cron` int(11) NOT NULL,
  `test_val` varchar(100) NOT NULL,
  `date_val` datetime NOT NULL
) ENGINE=MyISAM DEFAULT CHARSET=utf8;
 
ALTER TABLE `tbl_testcron`
  ADD PRIMARY KEY (`id_cron`);
 
ALTER TABLE `tbl_testcron`
  MODIFY `id_cron` int(11) NOT NULL AUTO_INCREMENT;
COMMIT;

SET GLOBAL event_scheduler = "ON"

SET GLOBAL event_scheduler = "OFF"

INSERT INTO tbl_testcron (test_val,date_val) VALUES ('test',NOW())

    INSERT INTO tbl_testcron (test_val,date_val) VALUES ('test',NOW())

BEGIN
	INSERT INTO tbl_testcron (test_val,date_val) VALUES ('test',NOW());
	UPDATE tbl_testcron SET test_val=CONCAT(test_val,' ',LAST_INSERT_ID()) 
	WHERE id_cron=LAST_INSERT_ID();
END

CREATE
    [DEFINER = { user | CURRENT_USER }]
    EVENT
    [IF NOT EXISTS]
    event_name
    ON SCHEDULE schedule
    [ON COMPLETION [NOT] PRESERVE]
    [ENABLE | DISABLE | DISABLE ON SLAVE]
    [COMMENT 'string']
    DO event_body;

schedule:
    AT timestamp [+ INTERVAL interval] ...
  | EVERY interval
    [STARTS timestamp [+ INTERVAL interval] ...]
    [ENDS timestamp [+ INTERVAL interval] ...]

interval:
    quantity {YEAR | QUARTER | MONTH | DAY | HOUR | MINUTE |
              WEEK | SECOND | YEAR_MONTH | DAY_HOUR | DAY_MINUTE |
              DAY_SECOND | HOUR_MINUTE | HOUR_SECOND | MINUTE_SECOND}

CREATE DEFINER=CURRENT_USER 
EVENT `EV1` 
ON SCHEDULE 
	AT '2019-03-19 23:47:59' 
ON COMPLETION 
	PRESERVE 
DISABLE 
DO
	INSERT INTO tbl_testcron (test_val, date_val) 
	VALUES ('test',NOW())

delimiter |	

CREATE DEFINER=CURRENT_USER 
EVENT `EV1` 
ON SCHEDULE 
	AT '2019-03-19 23:47:59' 
ON COMPLETION 
	PRESERVE 
DISABLE 
DO
	BEGIN
		INSERT INTO tbl_testcron (test_val,date_val) VALUES ('test',NOW());
		UPDATE tbl_testcron SET test_val=CONCAT(test_val,' ',LAST_INSERT_ID()) WHERE id_cron=LAST_INSERT_ID();
	END 
	
|	
delimiter ;

delimiter | 

# แทรกคำสั่งในการสร้าง event 

| 
delimiter ;

delimiter $$$ 

# แทรกคำสั่งในการสร้าง event 

$$$ 
delimiter ;

CREATE DEFINER=CURRENT_USER 
EVENT `EV1` 
ON SCHEDULE 
	EVERY 10 SECOND 
	STARTS '2019-03-19 12:00:00' 
	ENDS '2019-03-19 23:00:00' 
ON COMPLETION 
	PRESERVE  
ENABLE     
COMMENT 'เพิ่มรายการทุกๆ 10 วินาที' 
DO
	INSERT INTO tbl_testcron (test_val, date_val)
	VALUES ('test',NOW())

delimiter |	

CREATE DEFINER = CURRENT_USER 
EVENT `EV1` 
ON SCHEDULE 
	EVERY '1:2' MINUTE_SECOND 
	STARTS '2019-03-19 12:00:00' 
ON COMPLETION 
	NOT PRESERVE 
ENABLE     
DO 
	BEGIN
		INSERT INTO tbl_testcron (test_val,date_val) VALUES ('test',NOW());
		UPDATE tbl_testcron SET test_val=CONCAT(test_val,' ',LAST_INSERT_ID()) WHERE id_cron=LAST_INSERT_ID();
	END 
	
|	
delimiter ;

ON SCHEDULE 
	AT NOW() + INTERVAL 3 DAY

ON SCHEDULE 
	AT '2019-03-23 12:00:00'

CURDATE()+INTERVAL 1 DAY
// จะได้เป็น 2019-03-21 00:00:00
CURDATE()+INTERVAL 1 DAY + INTERVAL 8 HOUR
// จะได้เป็น 2019-03-21 08:00:00
CURDATE()+INTERVAL 1 DAY + INTERVAL 8 HOUR + INTERVAL 30 MINUTE
// จะได้เป็น 2019-03-21 08:30:00

ON SCHEDULE 
	EVERY 10 SECOND 
	STARTS CURDATE()+INTERVAL 1 DAY + INTERVAL 8 HOUR
	ENDS CURDATE()+INTERVAL 1 DAY + INTERVAL 12 HOUR

ALTER
	[DEFINER = { user | CURRENT_USER }]
	EVENT event_name
	[ON SCHEDULE schedule]
	[ON COMPLETION [NOT] PRESERVE]
	[RENAME TO new_event_name]
	[ENABLE | DISABLE | DISABLE ON SLAVE]
	[COMMENT 'string']
	[DO event_body]

ALTER EVENT EV1
    RENAME TO EV2

ALTER EVENT EV2
    RENAME TO EV1
    DISABLE

ALTER EVENT EV1
    ON SCHEDULE
      AT CURDATE()+INTERVAL 1 DAY + INTERVAL 8 HOUR
    DO
      TRUNCATE TABLE tbl_testcron