获取当前登陆的用户和数据库:SELECT CURRENT_USER(), SCHEMA();
从Figure1可以知道bfsql@%是没有Event_priv权限的,在该用户下创建事件的时候会出现下面的错误:
Error Code: 1044Access denied for user 'bfsql'@'%' to database 'blog'
如果出现上面的错误,执行下面的SQL就可以给bfsql@%赋予创建Event的权限:
1
2
UPDATE
mysql.user
SET
Event_priv = 'Y'
WHERE
HOST='%'
AND
USER='bfsql';
FLUSH
PRIVILEGES;
最后,你可以通过SHOW GRANTS FOR 'bfsql'@'%';查看所有权限;
4.创建事件:
(1)创建事件的语法如下:
1
2
3
4
5
6
CREATE
EVENT [IF NOT
EXISTS] event_name
ON
SCHEDULE schedule
[ON
COMPLETION [NOT]
PRESERVE]
[ENABLE
| DISABLE]
[COMMENT
'comment']
DO
sql_statement
(2)创建事件的示例如下:
1
2
3
4
5
6
7
8
DELIMITER
$$
CREATE
EVENT IF NOT
EXISTS e_blog
ON
SCHEDULE EVERY 30 SECOND
ON
COMPLETION PRESERVE
DO
BEGIN
CALL
MoveBlogData();
END$$
DELIMITER
;
DO sql_statement字段表示该event需要执行的SQL语句或存储过程。这里的SQL语句可以是复合语句,使用BEGIN和END标识符将复合SQL语句按照执行顺序放在之间。
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
--从现在开始每隔九天定时执行
CREATE
EVENT EVENT1
ON
SCHEDULE EVERY 9 DAY
STARTS NOW()
ON
COMPLETION PRESERVE ENABLE
DO
BEGIN
CALL
TOTAL();
END
--每个月的一号凌晨1
点执行
CREATE
EVENT EVENT2
ON
SCHEDULE EVERY 1 MONTH
STARTS DATE_ADD(DATE_ADD(DATE_SUB(CURDATE(),INTERVAL DAY(CURDATE())-1
DAY),
INTERVAL 1 MONTH),INTERVAL
1 HOUR)
ON
COMPLETION PRESERVE ENABLE
DO
BEGIN
CALL
STAT();
END
---每个季度一号的凌晨2点执行
CREATE
EVENT TOTAL_SEASON_EVENT
ON
SCHEDULE EVERY 1 QUARTER STARTS DATE_ADD(DATE_ADD(DATE(
CONCAT(YEAR(CURDATE()),'-',ELT(QUARTER(CURDATE()),1,4,7,10),'-',1)),INTERVAL
1 QUARTER),INTERVAL 2 HOUR)
ON
COMPLETION PRESERVE ENABLE
DO
BEGIN
CALL
SEASON_STAT();
END
--每年1月1号凌晨四点执行
CREATE
EVENT TOTAL_YEAR_EVENT
ON
SCHEDULE EVERY 1 YEAR
STARTS DATE_ADD(DATE(CONCAT(YEAR(CURDATE())
+ 1,'-',1,'-',1)),INTERVAL
4 HOUR)
ON
COMPLETION PRESERVE ENABLE
DO
BEGIN
CALL
YEAR_STAT();
END
5.事件开启与关闭:
开启某事件:
1
ALTER
EVENT e_test ON
COMPLETION PRESERVE ENABLE;
关闭某事件:
1
ALTER
EVENT e_test ON
COMPLETION PRESERVE DISABLE;