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
分享到:
相关推荐
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_...
备份:`mysqldump -u username -p database_name > backup.sql`恢复:`mysql -u username -p database_name 十六、权限管理 MySQL提供精细的权限控制,允许分配不同级别的访问权限。例如,为用户授予权限:`GRANT ...
- **创建存储过程**:`DELIMITER $$ CREATE PROCEDURE proc_name(IN param1 type, OUT param2 type) BEGIN SQL statements; END $$ DELIMITER ;` - **调用存储过程**:`CALL proc_name(value1, @value2);` #### 八...
1. **客户端连接**: 使用MySQL命令行客户端(`mysql.exe`)或图形化工具(如MySQL Workbench、phpMyAdmin)连接到MySQL服务器。输入用户名和密码(默认是root用户)进行登录。 2. **创建数据库**: 登录后,你可以...
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 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, ...
- **BETWEEN AND**:范围匹配,例如 `BETWEEN min_value AND max_value`,包含边界值。 - **IN**:判断某字段的值是否在列表中,等同于多个 `OR` 条件。 - **IS NULL/IS NOT NULL**:判断某字段是否为空。 - ****:...
1. 先阅读基础理论,理解MySQL的核心概念。 2. 阅读或观看学习资料,逐步学习SQL语法和操作。 3. 实践代码示例,亲手操作MySQL,遇到问题查阅相关文档或搜索答案。 4. 完成练习题目,检验学习效果。 5. 深入研究高级...
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 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, ...
CHAPTER 1 Introduction to MySQL . . . . . . . . . . . . . . . . . . . . . . . . . . 3 1.1 Introduction . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 3 1.2 Database, ...
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 ...
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...
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...
- **创建存储过程/函数**:`CREATE PROCEDURE/FUNCTION proc_name (param1 type, ...) BEGIN ... END;` - **调用存储过程/函数**:`CALL proc_name(value1, value2);` - **删除存储过程/函数**:`DROP PROCEDURE/...
Using collections containing component references and form inheritance can result in incorrect references being stored in the dfm file.======================================================= CORE ...
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...
BACKUP DATABASE your_database; END; ``` 这段代码会在每天的同一时间自动备份指定的数据库。 其次,SQL存储过程是预编译的一组SQL语句,封装在一个可重用的单元中。它们允许开发人员将复杂的逻辑分组,提高代码...
- 创建数据库:`CREATE DATABASE database_name;` - 创建表:`CREATE TABLE table_name (column_name datatype constraint);` - 示例:创建一个名为`employees`的表,包含员工ID(主键)、姓名、年龄、薪水、出生...
procedure TForm1.Button1Click(Sender: TObject); var SQL: TSQLConnection; Query: TSQLQuery; begin SQL := TSQLConnection.Create(nil); try SQL.Connected := True; SQL.Params.Values['Server'] := '...