mysql 定时复制表_MYSQL5.1修改表名与复制表结构的定时器与存储过程

---------------以下代码在MYSQL-Front下测试通过(MySQL Browser不行)-------------

无法保证事件操作的一致性--MYSQL下好像不支持alter,crate table等操作的回滚

-------------------------------------------------------------------------------

修改和新建表的存储过程:

CREATE PROCEDURE Alter_create_tablename(in tablename varchar(10))

BEGIN

declare predatetime varchar(10);

declare newtablename varchar(20);

set predatetime=(select date_format(date_sub(now(),interval 1 day),'%Y%m%d'));

set newtablename=concat(tablename,predatetime);

set @altertablesql=concat('ALTER TABLE ',tablename,' RENAME TO ',newtablename);          #不能进行事务处理，无法回滚

set @createtablesql=concat('create table ',tablename,' select * from ',newtablename,' where 1=2 ');

PREPARE altertablesql from @altertablesql;

Execute altertablesql;

commit;

PREPARE createtablesql from @createtablesql;

Execute createtablesql;

commit;

END

----------------------------------------------------

定时器: (必须运行在MYSQL5.1版本之上，又名事件调度器)

Select @@event_scheduler;

SET GLOBAL event_scheduler = ON; 开启调度器

CREATE EVENT IF NOT EXISTS Alter_create_Event

ON  SCHEDULE EVERY 1 DAY

Starts DATE_ADD(CONCAT(YEAR(CURDATE()),'-',Month(CURDATE()),'-',Day(CURDATE()),' 23:00:12'),INTERVAL 1 HOUR)

ON COMPLETION PRESERVE ENABLE

Do

begin

call Alter_create_tablename("student");

end