`
sillycat
  • 浏览: 2539512 次
  • 性别: Icon_minigender_1
  • 来自: 成都
社区版块
存档分类
最新评论

Mysql Database Event and Procedure(1)MySQL Event

 
阅读更多
Mysql Database Event and Procedure(1)MySQL Event

Check Event Switch
>show variables like '%scheduler%';
event_scheduler OFF

or

event_scheduler ON

Start the event
>SET GLOBAL event_scheduler = 1;

Need to set my.cnf as follow, otherwise, after reboot the mysql database, the settings will be gone.
event_scheduler=ON

Event Grammer - Create Event
Call Procedure Every 9 days
CREAT EVENT EVENT1

ON SCHEDULE EVERY 9 DAY STARTS NOW()
ON COMPLETION PRESERVE ENABLE
DO
     BEGIN
         CALL TOTAL();
     END

Grammer
   CREATE
      EVENT
      [IF NOT EXISTS]
      event_name
      ON SCHEDULE scheudle
      [ON COMPLETION [NOT] PRESERVE]
      [ENABLE | DISABLE | DISABLE ON SLAVE]
      [COMMENT ‘comment']
      DO event_body;

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

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

ON COMPLETION PRESERVE    execute many times forever

Event Grammar - Change the Event
ALTER
     EVENT event_name
     [ON SCHEDULE schedule]
     [ON COMPLETION [NOT] PRESERVE]
     [RENAME TO new_event_name]
     [ENABLE | DISABLE | DISABLE ON SLAVE]
     [COMMENT ‘comment']
     [DO event_body]

Event Grammer - Delete the Event
     DROP EVENT [IF EXISTS] event_name

Some Samples - Event and SQL in Body
-- check switch
show variables like '%scheduler%';

-- enable switch
SET GLOBAL event_scheduler = 1;

-- test table
CREATE TABLE `test` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `t1` datetime DEFAULT NULL,
  `id2` int(11) NOT NULL DEFAULT '0',
  PRIMARY KEY (`id`)
) ENGINE=InnoDB AUTO_INCREMENT=106 DEFAULT CHARSET=utf8

-- insert 1 record to that table every 3 seconds
CREATE EVENT IF NOT EXISTS test ON SCHEDULE EVERY 3 SECOND
ON COMPLETION PRESERVE
DO INSERT INTO test(id,t1) VALUES(NULL,NOW());

-- check event
SHOW CREATE EVENT test;

-- alter the event
ALTER EVENT test ON SCHEDULE EVERY 3 SECOND
ON COMPLETION PRESERVE
DO INSERT INTO test(id,t1) VALUES(NULL,NOW());

-- clean the things after 10 minutes
CREATE EVENT IF NOT EXISTS test2
ON SCHEDULE
AT CURRENT_TIMESTAMP + INTERVAL 10 MINUTE
DO TRUNCATE TABLE test;

DROP EVENT test;

-- check event
SHOW CREATE EVENT test2;

Some Samples - Event Call Procedure
define a store procedure and call the procedure after CALL
procedure name test_add();

CREATE EVENT test ON SCHEDULE EVERY 1 DAY
STARTS ‘2016-12-01 00:00:00'
ENDS ‘2016-12-01 00:00:00’ + INTERVAL 40 DAY
ON COMPLETION PRESERVE DO
CALL test_add();

Start and Disable Event
-- disable the event
ALTER EVENT test DISABLE;

-- enable the event
ALTER EVENT test ENABLE;

References:
https://blog.tankywoo.com/2015/04/01/mysql-stored-procedure.html
http://blog.chinaunix.net/uid-20639775-id-3323098.html
分享到:
评论

相关推荐

    mysql实现自动创建与删除分区

    SELECT PARTITION_NAME INTO last_partition FROM information_schema.partitions WHERE TABLE_SCHEMA = 'your_database' AND TABLE_NAME = 'your_table' ORDER BY PARTITION_NAME DESC LIMIT 1; IF NOT last_...

    MySQL语法大全

    备份:`mysqldump -u username -p database_name > backup.sql`恢复:`mysql -u username -p database_name 十六、权限管理 MySQL提供精细的权限控制,允许分配不同级别的访问权限。例如,为用户授予权限:`GRANT ...

    数据库讲义mysql05

    - **创建存储过程**:`DELIMITER $$ CREATE PROCEDURE proc_name(IN param1 type, OUT param2 type) BEGIN SQL statements; END $$ DELIMITER ;` - **调用存储过程**:`CALL proc_name(value1, @value2);` #### 八...

    mysql文档教程

    1. **客户端连接**: 使用MySQL命令行客户端(`mysql.exe`)或图形化工具(如MySQL Workbench、phpMyAdmin)连接到MySQL服务器。输入用户名和密码(默认是root用户)进行登录。 2. **创建数据库**: 登录后,你可以...

    Mysql实验2.pdf

    CREATE PROCEDURE film_1_3(IN p_id INT, IN p_name VARCHAR(20), IN p_price INT, IN p_number INT) BEGIN INSERT INTO salesrecords (id, name, price, number) VALUES (p_id, p_name, p_price, p_number); END$$...

    Navicat Premium Enterprise Edition v10.0.10 + Keygen

    Navicat Premium combines the functions of other Navicat members and supports most of the features in MySQL, SQL Server, SQLite, Oracle and PostgreSQL including Stored Procedure, Event, Trigger, ...

    mysql详细学习笔记

    - **BETWEEN AND**:范围匹配,例如 `BETWEEN min_value AND max_value`,包含边界值。 - **IN**:判断某字段的值是否在列表中,等同于多个 `OR` 条件。 - **IS NULL/IS NOT NULL**:判断某字段是否为空。 - ****:...

    mysql的学习代码以及一些学习资料.zip

    1. 先阅读基础理论,理解MySQL的核心概念。 2. 阅读或观看学习资料,逐步学习SQL语法和操作。 3. 实践代码示例,亲手操作MySQL,遇到问题查阅相关文档或搜索答案。 4. 完成练习题目,检验学习效果。 5. 深入研究高级...

    PremiumSoft Navicat Premium Enterprise v11.2.13 (x86 & x64)

    Navicat Premium combines the functions of other Navicat members and supports most of the features in MySQL, SQL Server, SQLite, Oracle and PostgreSQL including Stored Procedure, Event, Trigger, ...

    Navicat 11.2.12 Premium x64

    Navicat Premium combines the functions of other Navicat members and supports most of the features in MySQL, SQL Server, SQLite, Oracle and PostgreSQL including Stored Procedure, Event, Trigger, ...

    Sql for mysql

    CHAPTER 1 Introduction to MySQL . . . . . . . . . . . . . . . . . . . . . . . . . . 3 1.1 Introduction . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 3 1.2 Database, ...

    UniDAC 7.1.4

    Universal Data Access Components (UniDAC) is a library of components that provides direct access to most popular database servers from Delphi, Delphi for .NET and, C++Builder. UniDAC can work with ...

    unidac_7_1_4_pro DELPHI 10 Tokyo

    Azure Database for MySQL is supported JSON data type is supported InterBase data provider Support for Firebird on Android platform is added Support for Firebird 3 packages is added Aliases handling in...

    unidac_7_1_4_pro DELPHI 10 Berlin

    Azure Database for MySQL is supported JSON data type is supported InterBase data provider Support for Firebird on Android platform is added Support for Firebird 3 packages is added Aliases handling in...

    MySQL 常用命令速查表:日常开发、求职面试必备良方!

    - **创建存储过程/函数**:`CREATE PROCEDURE/FUNCTION proc_name (param1 type, ...) BEGIN ... END;` - **调用存储过程/函数**:`CALL proc_name(value1, value2);` - **删除存储过程/函数**:`DROP PROCEDURE/...

    Delphi7.1 Update

    Using collections containing component references and form inheritance can result in incorrect references being stored in the dfm file.======================================================= CORE ...

    Unidac Pro 7.1.4 XE8

    Azure Database for MySQL is supported JSON data type is supported InterBase data provider Support for Firebird on Android platform is added Support for Firebird 3 packages is added Aliases handling in...

    自动执行SQL语句&创建标准的Sql 存储过程

    BACKUP DATABASE your_database; END; ``` 这段代码会在每天的同一时间自动备份指定的数据库。 其次,SQL存储过程是预编译的一组SQL语句,封装在一个可重用的单元中。它们允许开发人员将复杂的逻辑分组,提高代码...

    数据库知识点整理,很详细完整,适合入门或者复习。

    - 创建数据库:`CREATE DATABASE database_name;` - 创建表:`CREATE TABLE table_name (column_name datatype constraint);` - 示例:创建一个名为`employees`的表,包含员工ID(主键)、姓名、年龄、薪水、出生...

    Delphi 7 高效数据库程序设计.pdf

    procedure TForm1.Button1Click(Sender: TObject); var SQL: TSQLConnection; Query: TSQLQuery; begin SQL := TSQLConnection.Create(nil); try SQL.Connected := True; SQL.Params.Values['Server'] := '...

Global site tag (gtag.js) - Google Analytics