今天一网友求助,每天早上8点自动将A表中的10条数据插入B表,然后在A表中删除那10条数据。
现测试功能: 有table_a和table_b表,每30秒table_a有2条数据插入到table_b表,然后删除table_a表的那2条数据;
创建event时,我的mysql是5.0的,总是报错。'event_scheduler'也是未知变量
在网上看看知道,MySQL是从5.1开始支持event功能。我换了个mysql5.5版本的实现成功了。
create table table_a
(
id INT PRIMARY KEY AUTO_INCREMENT,
name VARCHAR(25) NOT NULL
)
create table table_b as select * from table_a where 1<>1;
2.插入数据到table_a(不断执行下面这条代码)
insert into table_a(name) values(now());
3.创建存储过程(设定执行符为双斜杠“//”,因为内部分号会执行。最后重设回分号)
DELIMITER //
CREATE PROCEDURE proc()
begin
insert into table_b select * from table_a limit 2;
delete from table_a where id in(select id from table_b);
end
//
DELIMITER ;
4.开启定时器
show variables like 'event_scheduler'; --查看是否开启定时器
set global event_scheduler on; --开启定时器
set global event_scheduler=1; --开启定时器
5.创建每30秒钟调用过程proc()的事件
DELIMITER //
CREATE EVENT event_test
ON SCHEDULE EVERY 30 SECOND
ON COMPLETION PRESERVE
DO CALL proc();
//
DELIMITER ;
select * from table_a; --30秒左右查看一下数据,看是否变化
select * from table_b; --30秒左右查看一下数据,看是否变化
6.附加
ALTER EVENT testEvent ON COMPLETION PRESERVE ENABLE; -- 开启事件ENABLE/DISABLE
select name,body from mysql.proc where db='test' and type='PROCEDURE';
show procedure status;
show create procedure proc;
drop event event_test;
drop procedure proc;
每天早上8点自动将A表中的10条数据插入B表,然后在A表中删除那10条数据。代码
CREATE PROCEDURE proc()
begin
insert into table_b select * from table_a limit 10;
delete from table_a where id in(select id from table_b);
end
CREATE EVENT myevent
ON SCHEDULE EVERY 1 DAY
STARTS date_add(date(curdate() + 1),interval 8 hour)
ON COMPLETION PRESERVE
DO CALL proc();
从A 表中导数据的主要核心就是把A表中Mobile这列数据到B表中,A表(all_source)就是总表,B表 (today _source)就是当天表,每天导数据到B表中,然后把这些数据分配给我们公司的每个话务员,作为他们打电话的资源,每个人每天大概要打350个电话,假如说今天公司一共有500个话务员,那就要导350*500条数据,那明天公司有可能就变成502个人或者490人(每天都有人入职或离职),这时候就要执行350*502条数据或350*490条数据.而user表就是话务员表,表中人数就决定了每天导数据的条数(350*人数).如果每天有人没打完电话,每天都给大家重新分配同样的电话量。
CREATE PROCEDURE proc()
BEGIN
declare personCount int;
declare noCallCount int;
declare oneDayCount int;
declare limitCount int;
declare oneid int;
declare cur CURSOR FOR SELECT user_id FROM users;
set oneDayCount=350;
select count(*) into personCount from users;
select count(*) into noCallCount from today_source;
set limitCount=personCount*oneDayCount-noCallCount;
insert into today_source select * from all_source limit limitCount;
delete from all_source where exists(select 'X' from today_source where all_source.id=today_source.id);
update today_source set user_id=0;
OPEN cur;
FETCH cur INTO oneid;
WHILE ( oneid is not null) DO
UPDATE today_source SET user_id=oneid WHERE user_id=0 limit oneDayCount;
FETCH cur INTO oneid;
END WHILE;
CLOSE cur;
END
//
DELIMITER ;