mysql通过时间定时为数据库创建动态表名

MySQL事件是基于预定义的时间表运行的任务，因此有时它被称为预定事件。MySQL事件也被称为“时间触发”，因为它是由时间触发的，而不是像触发器一样更新表来触发的。MySQL事件类似于UNIX中的cron作业或Windows中的任务调度程序。

您可以在许多情况下使用MySQL事件，例如优化数据库表，清理日志，归档数据或在非高峰时间生成复杂的报告。

检测事件是否开启

1	show variables like 'event_scheduler';

###开启事件（最好在my.init设置，因为重启后还会变回默认值OFF）

1	set global event_scheduler = on;

###关闭事件

1	SET GLOBAL event_scheduler = OFF;

MySQL事件调度器配置

MySQL使用一个名为事件调度线程的特殊线程来执行所有调度的事件。可以通过执行以下命令来查看事件调度程序线程的状态：

1	SHOW PROCESSLIST;

执行上面查询语句，得到以下结果 -

mysql> SHOW PROCESSLIST;
+----+------+-----------------+----------+---------+------+----------+------------------+
| Id | User | Host            | db       | Command | Time | State    | Info             |
+----+------+-----------------+----------+---------+------+----------+------------------+
|  2 | root | localhost:50405 | NULL     | Sleep   | 1966 |          | NULL             |
|  3 | root | localhost:50406 | yiibaidb | Sleep   | 1964 |          | NULL             |
|  4 | root | localhost:50407 | yiibaidb | Query   |    0 | starting | SHOW PROCESSLIST |
+----+------+-----------------+----------+---------+------+----------+------------------+
3 rows in set

默认情况下，事件调度程序线程未启用。要启用和启动事件调度程序线程，需要执行以下命令：

1	SET GLOBAL event_scheduler = ON;

现在看到事件调度器线程的状态，再次执行SHOW PROCESSLIST命令，结果如下所示 -

mysql> SHOW PROCESSLIST;
+----+-----------------+-----------------+----------+---------+------+------------------------+------------------+
| Id | User            | Host            | db       | Command | Time | State                  | Info             |
+----+-----------------+-----------------+----------+---------+------+------------------------+------------------+
|  2 | root            | localhost:50405 | NULL     | Sleep   | 1986 |                        | NULL             |
|  3 | root            | localhost:50406 | yiibaidb | Sleep   | 1984 |                        | NULL             |
|  4 | root            | localhost:50407 | yiibaidb | Query   |    0 | starting               | SHOW PROCESSLIST |
|  5 | event_scheduler | localhost       | NULL     | Daemon  |    6 | Waiting on empty queue | NULL             |
+----+-----------------+-----------------+----------+---------+------+------------------------+------------------+
4 rows in set

创建新的MySQL事件

创建事件与创建其他数据库对象(如存储过程或触发器)类似。事件是一个包含SQL语句的命名对象。

存储过程仅在直接调用时执行; 触发器则与一个表相关联的事件(例如插入，更新或删除)事件发生时，可以在一次或更多的规则间隔执行事件时执行触发。

要创建和计划新事件，请使用CREATE EVENT语句，如下所示：

CREATE EVENT [IF NOT EXIST]  event_name
ON SCHEDULE schedule
DO
event_body


SQL

下面让我们更详细地解释语法中的一些参数 -

首先，在CREATE EVENT子句之后指定事件名称。事件名称在数据库模式中必须是唯一的。
其次，在ON SCHEDULE子句后面加上一个表。如果事件是一次性事件，则使用语法：AT timestamp [+ INTERVAL]，如果事件是循环事件，则使用EVERY子句：EVERY interval STARTS timestamp [+INTERVAL] ENDS timestamp [+INTERVAL]
第三，将DO语句放在DO关键字之后。请注意，可以在事件主体内调用存储过程。如果您有复合SQL语句，可以将它们放在BEGIN END块中。

#创建事件（从11月24号开始每天执行一次）

create EVENT eve_createTable
ON SCHEDULE EVERY 1 DAY
STARTS '2016-11-24 00:00:00' ON COMPLETION PRESERVE ENABLE
DO
CALL pro_createTable();

注：

1 2	1、ON` `COMPLETION PRESERVE ENABLE 是创建此事件即开始自动执行 2、SCHEDULE EVERY 1 DAY STARTS '2016-11-24 00:00:00' 从指定时间开始每天执行一次时间可用 now()

#创建存储过程（动态表名）

CREATE PROCEDURE pro_createTable()
BEGIN
DECLARE str VARCHAR(20000);
set str= CONCAT('CREATE TABLE member_network_',DATE_FORMAT(now(),'%Y%m%d'),'(
`id`  bigint(20) NOT NULL AUTO_INCREMENT ,
`member_id`  bigint(20) NULL DEFAULT NULL ,
`host_ip`  varchar(50) CHARACTER SET utf8 COLLATE utf8_general_ci NULL DEFAULT NULL ,
`serv_crc`  varchar(2000) CHARACTER SET utf8 COLLATE utf8_bin NULL DEFAULT NULL ,
`app_crc`  varchar(200) CHARACTER SET utf8 COLLATE utf8_general_ci NULL DEFAULT NULL ,
`sync_time`  timestamp NULL DEFAULT NULL ,
`online_time`  datetime NULL DEFAULT NULL ,
`type`  varchar(500) CHARACTER SET utf8 COLLATE utf8_general_ci NULL DEFAULT NULL ,
`up_stream_flux`  varchar(50) CHARACTER SET utf8 COLLATE utf8_general_ci NULL DEFAULT NULL ,
`down_stream_flux`  varchar(50) CHARACTER SET utf8 COLLATE utf8_general_ci NULL DEFAULT NULL ,
`total_stream_flux`  varchar(50) CHARACTER SET utf8 COLLATE utf8_general_ci NULL DEFAULT NULL ,
`line_no`  varchar(50) CHARACTER SET utf8 COLLATE utf8_general_ci NULL DEFAULT NULL ,
`url`  text CHARACTER SET utf8 COLLATE utf8_general_ci NULL ,
`action`  varchar(50) CHARACTER SET utf8 COLLATE utf8_general_ci NULL DEFAULT NULL ,
`sev_port`  varchar(50) CHARACTER SET utf8 COLLATE utf8_general_ci NULL DEFAULT NULL ,
`sor_port`  varchar(50) CHARACTER SET utf8 COLLATE utf8_general_ci NULL DEFAULT NULL ,
`protocol`  varchar(50) CHARACTER SET utf8 COLLATE utf8_general_ci NULL DEFAULT NULL ,
`regionCode`  varchar(500) CHARACTER SET utf8 COLLATE utf8_general_ci NULL DEFAULT NULL,
`memo`  varchar(500) CHARACTER SET utf8 COLLATE utf8_general_ci NULL DEFAULT NULL ,
`policy`  varchar(100) CHARACTER SET utf8 COLLATE utf8_general_ci NULL DEFAULT NULL ,
`dns`  varchar(100) CHARACTER SET utf8 COLLATE utf8_general_ci NULL DEFAULT NULL ,
`name`  varchar(255) CHARACTER SET utf8 COLLATE utf8_general_ci NULL DEFAULT NULL ,
`idcard`  varchar(255) CHARACTER SET utf8 COLLATE utf8_general_ci NULL DEFAULT NULL,
`client_name`  varchar(255) CHARACTER SET utf8 COLLATE utf8_general_ci NULL DEFAULT NULL,
PRIMARY KEY (`id`),
INDEX `dept_type` (`regionCode`(255), `serv_crc`(255), `online_time`, `name`, `type`(255)) USING BTREE 
)
ENGINE=InnoDB
DEFAULT CHARACTER SET=utf8 COLLATE=utf8_general_ci
AUTO_INCREMENT=1
ROW_FORMAT=COMPACT
');
SET @sqlstr=str;
PREPARE stmt from @sqlstr;
EXECUTE stmt;
deallocate prepare stmt;
END;

执行时报错，因为CONCAT拼接超过最大值；
#sql语句查看该参数，修改并重启mysql

1 2	show VARIABLES like '%max_allowed_packet%'; set global max_allowed_packet = 25600

mysql的前天、今天、后天

#2016-12-09
DATE_FORMAT(date_sub(curdate(),interval 1 day),'%Y%m%d')
#2016-12-10
DATE_FORMAT(date_sub(curdate(),interval 0 day),'%Y%m%d')
#2016-12-11
DATE_FORMAT(date_sub(curdate(),interval -1 day),'%Y%m%d')

删除MySQL事件

要删除现有事件，请使用DROP EVENT语句，如下所示：

DROP EVENT [IF EXISTS] event_name;


SQL

例如，要删除test_event_03的事件，请使用以下语句：

DROP EVENT IF EXISTS test_event_03;


SQL

在本教程中，您已经了解了MySQL事件，如何从数据库模式创建和删除事件。