イベントスケジューラーのSQL

サンプルコード

毎日60秒間隔で自動インサートされるテーブルのサンプルSQL。

SET GLOBAL event_scheduler = ON;
SHOW variables like 'event%';

create database event_auto_insert;
use event_auto_insert;

CREATE TABLE event_auto_insert.logs (
   id INT AUTO_INCREMENT,
    datetime DATETIME NOT NULL,
    value1 INT NOT NULL,
    value2 INT NOT NULL,
    category varchar(1) NOT NULL,
    flg INT NOT NULL,
    PRIMARY KEY (id)
);

create event logs
    on schedule every 10 SECOND
    starts now()
    do INSERT INTO event_auto_insert.logs(datetime, value1, value2, category, flg) VALUES (
     NOW(),
     CASE WHEN MONTH(NOW()) IN (1,2,3) THEN FLOOR(RAND() * (100 * -1))
     WHEN MONTH(NOW()) IN (4,5,6) THEN FLOOR(RAND() * (10 * -2))
     WHEN MONTH(NOW()) IN (7,8,9) THEN FLOOR(RAND() * (100 * 1))
     ELSE FLOOR(RAND() * (10 * 2)) END,
     FLOOR(RAND() * 100),
     SUBSTRING(MD5(RAND()), 1, 1),
     floor(rand() * 2)
     );

mysql> select * from event_auto_insert.logs;
+----+---------------------+--------+--------+----------+-----+
| id | datetime            | value1 | value2 | category | flg |
+----+---------------------+--------+--------+----------+-----+
|  1 | 2020-01-12 02:34:10 |    -17 |     61 | f        |   0 |
|  2 | 2020-01-12 02:34:20 |    -51 |     27 | 9        |   1 |
|  3 | 2020-01-12 02:34:30 |    -52 |     57 | a        |   1 |
+----+---------------------+--------+--------+----------+-----+